Crystal Reports 9 provides two easy ways to create reports against OLAP data sources. As introduced in Hour 4, "Using the Default Report Wizards," Crystal provides several report wizards to step you through the creation of some popular types of reports one of those is OLAP. The OLAP Wizard involves five steps/screens 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.
Specifying an OLAP Data SourceThe 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 Hour 3, "Accessing Your Data," is to relational data. Figure 20.1 shows the OLAP Data screen from the OLAP Wizard. Figure 20.1. The OLAP Data dialog from the OLAP Report Creation Wizard.When this tab/screen is first displayed, a cube will need to be selected with the Select Cube button. Clicking on this button brings up the Crystal OLAP Connection Browser, which is displayed in Figure 20.2. From the tree control presented in this dialog, the desired cube is selected. Figure 20.2. Crystal OLAP Connection Browser.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 tabs/screens have been explained:
At this point, we will review the concept of Rows and Columns in our OLAP report. Specifying OLAP Rows and ColumnsThe Rows/Columns dialog screen enables the selection of 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 20.4. Figure 20.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 Field or Select Column Field 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 depicted in Figure 20.5. Figure 20.5. The Member Selector dialog.
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:
At this point, we will review the concept of OLAP dimension filters and pages in our OLAP report. Specifying OLAP Dimension Filters and PagesThe Filters/Page dialog, shown in Figure 20.6, of the OLAP Report Creation Wizard enables the selection of 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 dimensions. Figure 20.6. The Filter/Page screen of the OLAP Report Creation Wizard.The Filter 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 (filter) for a particular dimension, that dimension must be selected in the Filter list box and the Select Filter Value button must be used to bring up the familiar Member Selection dialog (see Figure 20.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 paged dimension are required in a report, the Page list box should be used. 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 Filter 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 Filter/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 ability 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. Because Hour 12, "Implementing Parameters for Dynamic Reporting," has already 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 Hour 12 for a refresher on creating and editing parameters. Continuing with the creation of the sample report, the following steps walk through the Filter/Page dialog part of this report creation example and will enable the business user to select the measure that will be displayed in the OLAP grid:
At this point, we will review the concept of styles in our OLAP report. Adding Report Styles in the OLAP Report WizardThe Style dialog in the OLAP Report Creation Wizard enables the selection of any one of a predetermined number of styles for OLAP Grids available in Crystal Reports 9. Figure 20.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 hour) and using many of the advanced formatting features you have already learned about. Figure 20.9. The Style dialog of the OLAP Report Creation Wizard.Adding Charts Via the OLAP Report WizardThe Chart dialog provided in the OLAP Report Creation Wizard allows for the quick addition of a graphic to the OLAP report being created. The graphics available in this wizard, as shown in Figure 20.10, are only a subset of the graphics available (refer back to Hour 11, "Visualizing Your Data with Charts and Maps," for a refresher) in Crystal Reports 9, but they do enable the rapid visualization of your OLAP data without the need for using the Chart Expert. Figure 20.10. The Chart dialog of OLAP Report Creation Wizard.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 Hour 11 previously discussed, the On Change Of field is the field in your data source that will provide 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 store type and then subdivided by year. Using pie charts, Figure 20.11 displays what that might look like. Figure 20.11. A two-variable OLAP Chart.Now, to complete the OLAP report creation process, the following steps will take us through the addition of a style, a chart, and the creation of the finished report:
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 9 OLAP objects. Customizing Styles in the OLAP ExpertAfter an OLAP grid has been added to a report, with or without a selected style, Crystal Reports provides the ability 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 20.13 and is accessed by right-clicking on an existing OLAP Grid object or by selecting the Insert OLAP Grid option from the Insert menu. Figure 20.13. The OLAP Expert dialog.Four of the tabs in the OLAP Expert have identical functionality as presented in the previous Report Wizard sections. The Customize Style tab highlighted in Figure 20.13 however, is unique to the OLAP Expert and provides the ability 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 20.14. This dialog enables granular level formatting and selection of grid lines for display on the OLAP grid's layout. Figure 20.14. The Format Grid Lines dialog.Customizing Labels in the OLAP ExpertThe Labels tab of the OLAP expert, shown in Figure 20.15, provides the ability to customize the display of the paged-dimension (non row/column dimensions) labels on the OLAP grid. Figure 20.15. The Labels tab of OLAP Expert.Paged Dimension member values for the display grid can or cannot be displayed by simply moving the selected dimension between the unlabeled dimension and labeled dimension list boxes using the transfer arrow (>, >>, <, <<) buttons. Additional labeling options such as label location, label spacing, and dimension names can also be selected in this tab. |