Crystal Developers Journal

SAP BusinessObjects 101

2011-09-07T11:03:00.002+10:00

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:

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

2011-03-02T23:13:00.001+11:00

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

2011-02-25T13:43:00.001+11:00

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

2011-02-23T09:00:00.003+11:00

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

2011-02-20T16:24:00.012+11:00

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

BusinessObjects Board (BOB)

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!

TekTips Forums

Providing a number of forums for Crystal Reports developers, including forums on formulas, data access, etc.

SAP Developer Network – Crystal Reports

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.

Technical Support for Crystal Reports

Service Packs/Updates

Critical Updates

Hot Fixes

Service Packs

.NET Developer Resources

Sample Code

Runtime Packages

NET SDK documentation

Visual Studio Integration Manager

.NET Developer Forums

Licensing for Developers

Crystal Reports.NET 2005/2008 Forum

Providing answers to developer questions, code samples and more

CodeGuru Crystal Reports Forum

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

DB Forums

Forum for Crystal Reports development mainly around database development

Java Developer Resources

Sample Code

Crystal Reports for Eclipse

JAVA SDK documentation

Java Developer Forums

User Groups/Newsletters

BusinessObjects Users Groups

SAP BusinessObjects Newsletter

Tip: Inserting a User Name on Reports

2011-02-18T08:00:00.001+11:00

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

2011-02-17T09:00:00.002+11:00

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.

Tip: Using Summary Information

2011-02-16T13:29:00.002+11:00

When saving your report, don't forget to fill in the Summary Information with a title, description, etc. by selecting File > Summary Info. If you plan to use this report with Crystal Reports Server or BusinessObjects Edge/Enterprise, this information is displayed by default when you publish the report and can help users find reports more quickly.

Tutorial: Working with Specified Grouping

2011-02-15T14:16:00.002+11:00

Grouping within Crystal Reports is an easy way to organize like records. In this tutorial, learn how to use "specified grouping" to re-order your report data and make it easier to understand.

Note: This tutorial refers to the Xtreme Sample Database, which is a sample database used by Crystal Reports. If you don't have a copy of this database, you can download it from here. You will need to setup an ODBC driver to point to this database.

Grouping is often used to help analyze report data, as it can draw a clearer picture of what is in the data. For example, you could create grouped report that would show records by state, so all of the sales for a particular state would be shown together.

But what if you want to group on an item not found in the database? Or if you want to re-order the data? Crystal Reports provides a feature called "specified grouping" that will allow you to create your own custom groups to display the data as you like.

In this tutorial, we will look at how to create a specified group using some of the sample data that ships with Crystal Reports. We will create an "Employee Listing" report that shows the country where employees live and then we will create a specified group to group these countries into "North America" and "Europe" respectively.

1. Open Crystal Reports 2008 from the Start menu

2. Select File > New to create a new Standard Report

3. Under "Available Data Sources", double-click the option for "Create New Connection" and double-click "ODBC (RDO)" to open the standard ODBC dialog.

4. Select "Xtreme Sample Database" and click Finish to return to the Standard Report Wizard.

5. Expand the "Tables" section and double-click to select the "Employee" and "Employee_Addresses" tables and then click "Next" to continue.

6. The next step of the wizard is for specifying the links between the two tables. In this instance, both tables have a common key and the link has already been created for you, so you can click "Next" to continue.

7. In the next dialog, select the fields for your report-- to select a field, highlight it in the list on the left and then click the right-arrow button to move it to the list on the right. Select the following fields:

Employee.First Name
Employee.Last Name
Employee.Position
Employee_Addresses.City
Employee_Addresses.Region
Employee_Addresses.Country

And click "Finish" when you are done. You will end up with a simple listing report that matches the fields you have selected.

8. Next, we are going to insert our group-- select the Insert menu then "Group" to open the Insert Group dialog.

9. Using the first drop down list, select the Employee_Addresses.Country field and in the second drop-down box, select "In Specified Order"

10. This will change the dialog to show a "Specified Order" tab. In the text box marked "Named Group" enter the names of your groups, pressing enter after each. In this example, we are going to create two groups-- "North America" and "Europe"

11. Once you have entered these two groups, click to select the "Europe" group and click "Edit"

12. In the dialogue named "Defined Named Group" change the drop-down list to "Is one of" and select the countries of "UK" and "France" and click OK. Repeat this same action with the "North America" group, adding "Canada" and "USA"

Note:
You may have noticed there is also an "others" tab that specifies what will occur when there is a country that does not meet the criteria you have specified. Use the options on this tab to set whether these records will be shown in their own groups, a custom-named group or not shown at all.

13. When you have finished editing your specified groups, click OK to return to your report, which should now show the two groups of "North America" and "Europe" as well as an "Others" group if you took the default settings.

You can use this technique anywhere you have a group inserted into your report.

Free Crystal Reports for Visual Studio 2010

2011-02-12T13:51:00.002+11:00

SAP has released Crystal Reports for Visual Studio 2010 and has made it free for Visual Studio developers who are looking to integrate reports into their .NET applications.

This new version provides developers with the ability to leverage features from the Crystal Reports 2008 release, as well as a new WPF viewer (shown below) for integrating into applications that use the Windows Presentation Framework.

You can check out what's new for in this release from the PDF available here. And you can download your own copy from: http://www.businessobjects.com/jump/xi/crvs2010/default.asp

Tip: Setting the Default Formula Syntax

2010-12-13T13:26:00.002+11:00

Are you tired of setting the formula syntax you want to us every time you create a formula? By default, the formula editor will use the Crystal syntax option but you can change this to default to basic syntax by selecting File > Options and then clicking on the Formula Editor tab. You can then use the drop-down at the bottom of the page to select your default formula syntax.

Crystal Reports 2011: Give'em what they want!

2010-12-06T19:49:00.001+11:00

There are few things I look forward to more than a new version of Crystal Reports—and I am in luck, as Crystal Reports 2011 is just around the corner. They say that with BusinessObjects software, there are “integration” releases and “innovation” releases, and Crystal Reports 2011 is definitely a step forward in the right direction, providing tighter integration with SAP data sources, but there are still some features that hardened report developers and end-users are going to love

And first and foremost, is the new Excel 2007 export format. In a perfect world, users would be content to view their beautifully formatted reports online, but alas it is not a perfect world. The comment I most often hear is “Great report design, can I export that to Excel?” (To which I roll my eyes in the back of my head like I am having a seizure)

Unfortunately, users love to export their Crystal Reports to Excel, where they can chop, change and re-arrange the data as they see fit. Crystal Reports has always had an Excel export, but with Crystal Reports 2011, this has been updated to support Excel 2007 and produces .XLSX files which can have up to 1 million rows in them. In addition, this export format uses compression technology to make smaller file sizes.

So if your users are clamouring to get at their report data set, they are going to love this release! And make sure you remind them that while Crystal Reports 2011 does provide a great export format for Excel, they could be using LiveOffice and cut out the exporting step all together. If you are going to use Excel, might as well use it the smart way!

Bringing Flash to the iPhone

2010-09-13T22:12:00.000+10:00

http://www.pcmag.com/article2/0,2817,2368965,00.asp

In what has to be good news for Adobe Flash developers (and Xcelsius developers as well,) Apple has eased it's restrictions on the tools that developers can use, meaning the Adobe crowd can once again export their applications for use on the iPhone. What will this mean for Xcelsius? Stay tuned for more details.

Tip: Set your default formula language in Crystal Reports

2009-10-16T13:44:00.001+11:00