Crystal Reports provides two easy ways to create reports against OLAP data sources. As introduced in Chapter 1, "Creating and Designing Basic Reports," Crystal provides several report wizards to step you through the creation of some popular types of reportsone of those is OLAP. The OLAP Wizard involves five steps and walks you through the process of creating an OLAP grid and an optional supporting graphic based on an existing data source. The OLAP Wizard is accessible when you are creating a new report.
The second method of creating an OLAP-based report is through the OLAP Expert that is accessed from the Insert OLAP Grid on the Insert menu. This expert provides six tabs that step through the creation of an OLAP grid to be placed anywhere on a report.
The two methods of creation offer very similar degrees of functionality, and their respective dialog screens and tabs are almost identical. The OLAP Report Creation Wizard does provide a built-in Charting screen not found in the OLAP Expert, whereas the OLAP Expert provides Style Customization and Label tabs not found in the OLAP Wizard.
Although Crystal Reports has been designed to report off of numerous multidimensionalOLAP databases including Hyperion Essbase, Microsoft SQL Server Analysis Services, and SAP BW, for the purposes of demonstration in this chapter, examples will be based on the SQL Server sample HR cubeFoodMart. If a different OLAP Database is available, the general principles should be followed against that native OLAP cube.
Specifying an OLAP Data Source
The OLAP Data tab (or screen in the OLAP Wizard) requests the OLAP data source on which the report is to be based. This wizard and its associated dialog screens are to multidimensional data sources what the data explorer, introduced in Chapter 1, is to relational data. Figure 16.1 shows the OLAP Data screen from the OLAP Wizard.
Figure 16.1. The OLAP Data dialog from the OLAP Report Creation Wizard.
When this screen is first displayed, a cube will need to be selected with the Select Cube button. Clicking on this button opens the Crystal OLAP Connection Browser, which is displayed in Figure 16.2. From the tree control presented in this dialog, select the desired cube.
Figure 16.2. The Crystal OLAP Connection Browser enables the specification of an OLAP data source for the involved Crystal Report.
For detailed coverage of the Crystal OLAP Connection Browser and the functionality it provides, p. 378
To help you learn about the creation of an OLAP-based Crystal Report, here are the introductory steps to doing exactly that against SQL Server's sample FoodMart cube. Other steps will follow these initial steps after subsequent screens have been explained. Start the OLAP Report Creation process with the following steps:
Figure 16.3. The New Server dialog is used to connect to new OLAP data sources.
A Select CAR File button exists on the Data screen of the OLAP Report Creation Wizard, in addition to the Select Cube button. CAR files are Crystal Analytic Reports (CAR) and are created with the sister product to Crystal ReportsCrystal Analysis. This product is an OLAP-focused reporting and application tool and will be briefly introduced in Chapter 19, "Creating Crystal Analysis Reports," and Chapter 20, "Advanced Crystal Analysis Report Design." These CAR files can be treated as multidimensional data sources because they themselves contain connectivity information to an underlying OLAP data source.
Specifying OLAP Rows and Columns
The Rows/Columns dialog screen enables you to select both the dimensions and fields to be presented along the columns and rows of the OLAP grid. All the available dimensions in the selected cube/data source are listed in the Dimensions list box depicted in Figure 16.4.
Figure 16.4. The Rows/Columns dialog of the OLAP Report Creation Wizard.
To select a dimension for placement in the rows section or the columns section of the OLAP grid, highlight the desired dimension and click either the column or row arrow (>) button. It is possible to select multiple dimensions to be displayed and have these nested in the OLAP grid by successively selecting multiple dimensions for either the rows or the columns section. It is also possible to remove dimensions from the existing row or column list boxes; however, the column and row dimension list boxes cannot be left empty.
After the desired dimensions are selected, a subset of the fields (also known as members) for those dimensions can be selected using the Select Row Members or Select Column Members buttons. Examples of this might be selecting only a certain subset of provinces or states in a region dimension or, alternatively, selecting only a certain year's worth of data in a time dimension. By highlighting a dimension in either of the Rows or Columns list box and then selecting the appropriate Selection button, a subset of the members for the involved dimension can be selected from the Member Selector dialog as shown in Figure 16.5.
Figure 16.5. The Member Selector dialog is used to select default Column and Row Dimension members.
The last and newest feature of the Rows/Column screen is the Create/Edit Parameter functionality provided for each of the Row and Column dimensions. This capability provides the business user or report consumer with the capability to interact with the report and control its content by entering parameters that directly affect the dimension members displayed in the OLAP grid(s) on the report.
Because Chapter 5, "Implementing Parameters for Dynamic Reporting," covered parameters in detail, you are likely familiar with this topic already. Of significance for this wizard screen is that the parameter creation process is directly accessible here, and this facilitates the rapid development of formatted and interactive OLAP reports. If necessary, review Chapter 5 for a refresher on creating and editing parameters.
The Member Selector dialog provides some powerful shortcuts for the selection of certain logical groups of members. These selection shortcuts are accessed through either the Select drop-down box or by right-clicking on any part of the Member Selection list box. Sample selection shortcuts include the capability to select all base level members or all members at a highlighted level.
Continuing with the creation of the sample report started in the last section, the following steps walk through the Rows/Columns screen part of this report creation example and allow for the refinement of the data to be viewed in the OLAP grid. Follow these steps to add rows and columns to your OLAP-based report:
At this point, you will review the concept of OLAP dimension filters and pages in your OLAP report.
Specifying OLAP Dimension Slices (Filters) and Pages
The Slice/Page dialog of the OLAP Report Creation Wizard, shown in Figure 16.6, enables you to select values or members for the dimensions that were not selected to be row or column dimensions. In the OLAP world, these dimensions are often called paged or sliced dimensions.
Figure 16.6. The Slice/Page screen of the OLAP Report Creation Wizard allows manipulation of the dimensions not selected for use on either the rows or columns.
The Slice list box lists all the paged dimensions and their current member settings. The default setting is usually all members for any given dimension. An example is that for the Store Dimension, the default slice setting is All Stores. To change the member selection (slice) for a particular dimension, that dimension must be selected in the Slice list box and the Select Slice button must be used to open the familiar Member Selection dialog (refer to Figure 16.5). This dialog is identical to the Member Selection dialog used previously except that only one member from the selected dimension can be selected. If multiple members from a slice dimension are required in a report, the Page list box should be used and separate pages/grids will be created for each value selected.
The Page list box is initially empty but can contain any dimensions outside the row and column dimensions that require multiple member selection. An example could involve selecting the three countries of North America as store regions. The selection of multiple values for a paged dimension creates completely separate grids (based on the same preselected rows and columns) for each selected member value. To select multiple members for a dimension, the involved dimension needs to be selected in the Slice list box and moved to the Page list box using the transfer arrow buttons between the list boxes. Once moved to the Page list box, the Select Page Values button enables multiple member selection through the Member Selection dialog.
The last, but perhaps most powerful, feature of the Slice/Page screen is the Link to Parameter functionality provided for each of the Filtered and Paged dimensions. This capability provides the business user or report consumer with the capability to interact with the report and control its content by entering parameters that directly affect the information displayed in the OLAP grid(s) on the report.
Of significance for this wizard screen is that the parameter creation process is directly accessible here, and this facilitates the rapid development of formatted and interactive OLAP reports.
For more information on creating and editing parameters, p. 129
Continuing with the creation of the sample report, the following steps walk through the Slice/Page dialog part of this report creation example and will enable you to select the measure that will be displayed in the OLAP grid. Follow these steps to select measures on the page/slice dimensions:
Figure 16.7. The Create Parameter Field dialog called from the Slice/Page screen.
Figure 16.8. The Set Default Values dialog for the OLAP Slice Parameter.
After Parameters or Multi-Value Paged Dimensions have been set in the OLAP Report Creation Wizard, you can only access them for editing through the OLAP Design Wizard under the main Report menu. These settings are not configurable in the OLAP Expert.
Adding Report Styles in the OLAP Report Wizard
The Style dialog in the OLAP Report Creation Wizard enables you to select any one of a predetermined number of styles for OLAP grids available in Crystal Reports. Figure 16.9 displays the Style dialog. The styles are often considered a good starting point for formatting the OLAP grids on your reports and can be enhanced through both the Customize Style tab of the OLAP Expert (described later in the chapter) and using many of the advanced formatting features you have already learned about.
Figure 16.9. The Style dialog of the OLAP Report Creation Wizard.
Adding Charts via the OLAP Report Wizard
The Chart dialog provided in the OLAP Report Creation Wizard enables you to add graphics quickly to the OLAP report being created. The graphics available in this wizard, shown in Figure 16.10, are only a subset of the graphics available in Crystal Reports (refer to Chapter 8, "Visualizing Your Data with Charts and Maps," for a refresher), but they do enable the rapid visualization of your OLAP data without the need for using the Chart Expert.
Figure 16.10. The Chart dialog of OLAP Report Creation Wizard enables you to select between different basic chart types.
Aside from selecting the type of chart (bar, line, or pie) and specifying a title on this screen, an On Change Of field must be specified with an optional Subdivided By field before this screen is complete. As Chapter 8 discussed, the On Change Of field is the field in your data source that provides the breaking point for the involved graphic. Examples could include country, region, year, store, product, and so on. The Subdivided By field can provide a second variable to base your charts on. An example of a two-variable OLAP Chart using the FoodMart sample cube would be a chart showing salary information by year and then subdivided by store type. Using pie charts, Figure 16.11 shows what that might look like.
Figure 16.11. A two-variable OLAP Chart showing Avg Salary based on Year and subdivided by Store Type.
Now, to complete the OLAP report creation process, the following steps will take you through the addition of a style, a chart, and the creation of the finished report:
Figure 16.12. The sample OLAP report created using the OLAP Wizard.
The OLAP Report Creation Wizard provides an efficient and effective method to getting value out of OLAP data in a short timeframe. After an OLAP grid or OLAP chart has been placed on your report through the wizard, further formatting and analysis can be performed through a variety of built-in Crystal Reports formatting tools. The next two sections explore further customization options and the three subsequent sections discuss the powerful new interactivity available in Crystal Reports OLAP objects.
Customizing Styles in the OLAP Expert
After an OLAP grid has been added to a report, with or without a selected style, Crystal Reports provides the capability to enhance and customize the formatting of that grid through the Customize Style tab accessed on the OLAP Expert. The OLAP Expert dialog is displayed in Figure 16.13 and is accessed by right-clicking on an existing OLAP grid object and accessing the OLAP Grid Expert, or by selecting the Insert OLAP Grid option from the Insert menu.
Figure 16.13. The OLAP Expert dialog provides the capability to edit many of the OLAP Grid display properties including the customization of styles.
Four of the tabs in the OLAP Expert have identical functionality as presented in the previous Report Wizard sections. The Customize Style tab shown in Figure 16.13 is unique to the OLAP Expert and provides the capability to fine-tune the formatting of the row and column dimensions selected for the involved OLAP grid. By selecting any of the column or row dimensions from the presented list boxes, custom colors can be selected for the backgrounds of the OLAP grid row and column headings. This tab also provides a number of formatting options for the presentation of the grid including indentation, blank column/row suppression, margins, and labels. Also provided is an option to format grid lines, shown in Figure 16.14. This dialog enables granular level formatting and selection of grid lines for display on the OLAP grid's layout.
Figure 16.14. The Format Grid Lines dialog is accessed from the Customize Style Tab of the OLAP Expert dialog and enables granular level control of the OLAP grid's grid lines.
Customizing Labels in the OLAP Expert
The Labels tab of the OLAP expert, shown in Figure 16.15, provides the capability to customize the display of the paged-dimension (non row/column dimensions) labels on the OLAP grid.
Figure 16.15. The Labels tab of OLAP Expert enables you to specify display properties around the OLAP grid's dimensions.
Paged/Sliced Dimension member values for the display grid can be displayed or hidden by simply moving the selected dimension between the unlabeled dimension and labeled dimension list boxes using the transfer arrow (>, >>, <, <<) buttons. Additional labeling optionssuch as label location, label spacing, and dimension namescan also be selected in this tab.