Pages

SAP BusinessObjects 101

SAP BusinessObjects Explorer is a new addition to SAP Crystal Server 2011 and was previously only available with the higher-end server products. Explorer is not installed by default when you install SAP Crystal Server 2011, but if you have installed it, you will be able to access Explorer from the Applications menu, as shown below in Figure 1:

Explorer Launch Option
Figure 1: Explorer launch option
 

Explorer is a tool that you can use to explore and analyze data sets . These data sets can be sourced from Excel spreadsheets, or alternately from Universes (.UNX) created using the new semantic layer available with SAP Crystal Server 2011.

Since this is just "BusinessObjects Explorer 101", this blog is not intended to be an exhaustive reference on BusinessObjects Explorer, but does provide the basic skills to get you up and running with the tool, with an Excel spreadsheet as the data source for your first Information Space.

For more of the unabridged user guide to SAP BusinessObjects Explorer, visit http://help.sap.com and navigate to the BusinessObjects documentation, where you will find the user guide, administrator's guide and installation guide for Explorer – over 300 pages worth!

When you first open Explorer, you will be presented with a home page showing a list of “Information Spaces” as shown below in Figure 2:



Figure 2: Explorer home page

To start your exploration of data, you have three options:

1. Enter a search term in the box at the top of the screen — this will search through all of the Information Spaces and find the data that references your search term. For example, if you were to search for “California”, Explorer would find all of the Information Spaces where this is a data element. You can then use the Explorer UI to filter the results, analyze data, etc.

2. Click to explore an existing Information Space — this will take you to the Explorer UI and allow you to filter and analyze the data within that Information Space.

3. Upload a Spreadsheet — this will allow you to upload and configure an Excel
spreadsheet to do ad-hoc data exploration and analysis.

Note: Using this method will not actually save the Excel spreadsheet as an Information Space, so only use this technique if you want to do ad-hoc analysis and not save the results.

In the following section, we are going to look at how to configure an Excel spreasdheet an Information Space for use with BusinessObjects Explorer.

Configuring an Excel Spreadsheet as an Information Space

 
To configure an Information Space based on Excel spreadsheet data, first we need to check that the Excel workbook is formatted correctly for use with Explorer. To start, the spreadsheet should have a first row that contains the column names and the structure of the spreadsheet should be a simple column-based layout, like the spreadsheet shown below in Figure 3:

Figure 3: Spreadsheet formatted for Explorer

The Excel workbook can have more than one spreadsheet tab, but when configuring the Information Space, we can only use data from one spreadsheet at a time. For large data sets, it is better if the spreadsheet data is summarized, but this is not always a requirement as Explorer has its own indexing functionality built in.

Note: This Excel spreadsheet and dataset is included with the downloadable files that are available from http://www.kuiperpublishing.com/assets/explorerdata.xls so you can follow along with the instructions below.

Once you have confirmed that your spreadsheet has the correct layout and attributes, you will need to upload your spreadsheet to BI launch pad. To upload your spreadsheet, follow these steps:

1. Login to BI launch pad.
2. Click on Documents > Folders > Public Folders and click to select a folder where you want to store the uploaded Excel file.
3. Right-click on the folder and select New > Local Document, which will open the dialog shown in the figure below:


Figure 4: Local document upload dialog

4. Browse to where your Excel spreadsheet is stored and then click the “Add” button to upload your spreadsheet to the folder you have selected.
Now that your spreadsheet has been updated, we need to create an Information Space and configure the options. To configure a new Excel-based Information Space, follow these steps:
1. Launch BusinessObjects Explorer from BI launch pad by selecting Applications > Explorer.
2. From within Explorer, click the option to “Manage Spaces”.
3. Navigate through the node marked Excel Spreadsheets > Public Folders to the folder where you uploaded your Excel spreadsheet.
4. Click on the Excel spreadsheet and then click the “New” button which should now be enabled. This will open the dialog shown in figure below:


Figure 5: Information Space dialog

5. Using the dialog, enter a name for your Information Space and then click the tab marked “Configure Excel File” to open the dialog shown below:



Figure 6: Excel file configuration options

6. From this dialog, select the Worksheet that contains your data and the option for
“Headers are provided by first row” is ticked on by default.

7. Using the drop-down menus, select the options for each column, indicating the type of field it will be. The options are:

  • Label
  • Label as Text
  • Value (SUM)
  • Value (MIN)
  • Value (MAX)
  • Value (AVG)
  • Hide

If you select “Label” or “Label as Text”, these columns will be considered to be Dimensions or "Facets". Columns set as “Value” will be considered to be Measures in your Information Space. And if you select “Hide” the field will not be available for use.

8. Click on the Scheduling tab and select your options for how often the data will be reindexed. The scheduling process will re-run the index as a one-off or on a regular basis. The options here will be determined by whether you are uploading this data as a “one-off” or whether you will be updating it periodically. You can also re-index at any time without scheduling from the “Manage Spaces” menu.

9. Once you have finished setting your options, click OK to return to the list of Information Spaces. To make your Information Space available immediately, click the “Index Now” button from the dialog shown below (it is one of the four small icons on the right-hand side.)


Figure 7: Index now button

To consume your new Information Space, you can return to the Home tab in Explorer and then click the “Refresh” icon on the Information Space listing. Your new Information Space should be listed and you can click to open the Explorer UI and start using it to explore your data, as shown below:



The Explorer interface can be used to filter data by dimensions (or "facets")  as well as to visualize data, export and e-mail the data to other users and more. For the complete SAP BusinessObjects Explorer User's Guide, visit http://help.sap.com

Migrating Your Crystal Reports Server System Database

When you install Crystal Reports Server, you have the option of either selecting a database to use for the System Database (or repository) or the setup program can install and configure MySQL for you.
Many installations will start of using MySQL and then look to move their system database to another database server, like SQL Server, Oracle, DB2, etc.

To move your system database from MySQL to another database (in this case, SQL Server), select a time when there will be no users who need to access the server and then follow these steps:

1. Backup your existing MySQL database. There is a great article on how to backup a database using the MySQL utility mysqldump, which is available from:
http://www.devshed.com/c/a/MySQL/Backing-up-and-restoring-your-MySQL-Database/

2. Check the Supported Platforms document to make sure your version/service pack of SQL Server is supported.

3. On the SQL Server, create a blank database

4. On the server where you installed Crystal Reports Server, create an ODBC system dsn that points to the blank database you have created.

5. Next, go to the server and select Start > Programs > Crystal Reports Server 2008 > Crystal Reports Server 2008 > Central Configuration Manager as shown below in Figure 1.

 

6. To change the system database, you will need to stop the SIA service, which in turn will stop all of the other BusinessObjects services. Highlight the SIA service, shown below in Figure 2 and then click the Stop button in the toolbar.

 

7. Next, right-click on the SIA service and select Properties and navigate to the Configuration tab.

8. Locate the CMS System Database Configuration area and click the Specify button, as shown below in Figure 3.

 

9. A dialog will appear presenting three options for specifying the system database. Since we are moving the system database, select the first option, which is “Update Data Source Setting” as shown in Figure 4

 

10. Once you have selected this option and clicked OK, you will be presented with a list of available data sources, as shown in Figure 5.

 

11. Select the option for SQL Server (ODBC) and then select the ODBC System DSN that you created earlier using the dialog shown below in Figure 6, then  click OK.

 

You may be asked to login—make sure you login with a SQL Server account that has the rights to create tables, etc. (If in doubt, get your DBA to login to do this step.)

Once the database transfer is complete, you can then restart the SIA service—the easiest way to check that the transfer was successful is to login to InfoView (http://servername:8080/InfoViewApp) or the Central Management Console (http://servername:8080/CmcApp) and navigate around, schedule a few reports, etc.

If the SIA service itself won't start, check out the Windows Event Viewer to check for any error or warning messages that may appear.

This technique can also be applied to SAP BusinessObjects Edge and SAP BusinessObjects Enterprise. It also can be used to move the system databases to other database formats. In the case of DB2, Oracle, etc. you don't need to create an ODBC connection to the database, as you can correct to these database platforms natively.

Tip: Conditionally setting Size and Position

One of the underused features of Crystal Reports is the ability to control report indenting conditionally format the "X" value in the size and position dialog. To work with this feature, right-click on a field, select Size and Position and then click the "X+2" button to add a conditional formatting formula.

Tip: Copying Formatting between Objects

Need a quick way to format multiple report objects in your Crystal Reports? Format a single object as required, then click on the object and then select Format > Format Painter. Click on another object of the same type and the formatting options from the first field will be applied.

Crystal Reports Online Resources

One of the questions I get asked a lot is "Where can I find help for Crystal Reports on the web"? To help with that question and more, I have put together the links below-- these links were current at the time of the publishing but may change as the web sites themselves change and update over time.

If you have any additional web sites you would like to add to this list, please use the comments below and we will try to keep the list as up to date as possible.

Community Sites


BOB is one of the oldest and most respected BusinessObjects communities on the web, featuring forums on all of the BusinessObjects products, including Crystal Reports as well as updates on local, national and international user group meetings. If you are looking for an answer, you will probably find it on BOB!

Providing a number of forums for Crystal Reports developers, including forums on formulas, data access, etc.
The SAP Development Network (SDN) is a community-based site sponsored by SAP that provides samples, downloads, blogs, forums and more.

General Report Design Resources


Product Documentation
Click on SAP BusinessObjects then "All Products". You can then use the drop-down list to select the product and version to see a list of documentation.

Sample Reports + Databases
A selection of sample reports that demonstrate various report features and sample databases/data sources that are required by the reports.

Crystal Reports Viewer 2008                                              
A few report viewer available for both Windows and Macs for viewing reports with saved data, providing the ability to drill-down, sort, export, print, etc.

FREE Download Data Direct ODBC Drivers
Additional drivers provided free for Crystal Reports developers to use when connecting various data sources.

Support for SAP Crystal Reports
SAP support area for Crystal Reports

Search SAP Notes
Search the SAP Notes (Knowledge Base) for information about Crystal Reports, answers to common problems, etc.

Supported Platforms Documentation
This documentation lists all of the supported and tested platforms for Crystal Reports. If you are having trouble with a particular data source, application, etc. you should check this documentation to see if your data source, version, etc. are listed.
 
Crystal Reports Server
Crystal Reports Server is a web-based, server platform for distributing reports, dashboards and other content to users and includes the ability to view reports in your web browser, schedule and distribute reports and more.
                                

  

Service Packs/Updates


                 

Service Packs                                                             

 

.NET Developer Resources


Sample Code




Licensing for Developers

Providing answers to developer questions, code samples and more
Providing support for .NET and Visual Basic users integrating Crystal Reports into their application
ASP.NET Crystal Reports forum
Providing assistance for ASP.NET developers integrating Crystal Reports into their own applications
Forum for Crystal Reports development mainly around database development
                                  
 
Java Developer Resources




                                                                           
                                                                                                                                   
 

User Groups/Newsletters


BusinessObjects Users Groups                                                                     
                                                                         

Tip: Inserting a User Name on Reports

Ever look at a printed report and wonder who ran it? One of the old tricks was to use a parameter for the user name when the report was run, but if you are using Crystal Reports Server or BusinessObjects Edge/Enterprise, there is a "Special Field" available in Crystal Reports called "Current CE User Name" that you can place on your report to tell you. To add this field to your report, select View > Field Explorer and expand the Special Fields section. You can then drag the field on to your report canvas-- when the report is viewed through Crystal Reports Server or BusinessObjects Edge/Enterprise, the name will appear.

Dashboard 101: Creating a Pie Chart

Welcome to "Dashboard 101", a series of tutorials designed to teach basic Crystal Dashboard design-- in this tutorial, we are going to be looking at how to build a simple Crystal Dashboard (Xcelsius) visualization using the Pie Chart component. When you are finished with the tutorial, your visualization should look like this:


You can then use this technique to add other charts to your visualizations, as it is the same basic workflow. To create this visualization, you will need to download the source Excel spreadsheet, which is available from a zip file found here.

To create this visualization, follow these steps:

1.    Select File > New with Spreadsheet
2.    Browse to the folder where you have stored your course files.. There is a sub-folder named “Activity Source.” From this folder, open the “SalesByState.xls” spreadsheet.
3.    From the Component Browser, drag the Pie Chart component onto your canvas
4.    Right-click on the Pie Chart and select Properties
5.    For the Titles, click on the range selector icon beside the Chart title and select cell “A1”
6.    For the subtitle, click on the range selector icon beside the Subtitle and select cell “A2”
7.    Under the Data properties, select the radio button for “Data in Columns”
8.    For the data Values, click on the range selector and select “C5:C12”
9.    For the data Labels, click on the range selector and select “B5:B12”
10.    Click the icon in the Toolbar marked “Fit Canvas to Components”
11.    Click the Preview button to check your visualization
12.    Save your visualization as “Sales_by_State.xlf”

And that is all there is to it-- the same technique can be applied to add other charts to your visualization. Remember, you will select the data for these charts using your range selector and you can change the options for the chart, including formatting, labels, etc. from the chart's properties page.