OLAP Report Creation Wizard and OLAP Expert

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.

graphics/bookpencil_icon.gif

Although Crystal Reports has been designed to report off of numerous multidimensional\OLAP databases including Hyperion Essbase, Microsoft SQL Server Analysis Services, and SAP BW, for the purposes of demonstration in this hour, examples will be based on the SQL Server sample cube FoodMart. 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 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.

graphics/20fig01.jpg

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.

graphics/20fig02.jpg

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:

  1. Create a New Crystal Report and select the OLAP Wizard from the Crystal Reports Gallery dialog.

  2. Click the Select Cube button from the OLAP Data dialog.

  3. Assuming that the location of the OLAP Server has not already been identified to the Crystal OLAP Connection Browser, Click the Add Server button and identify the location of your SQL Server Analysis Server and the sample HR cube. Figure 20.3 highlights the New Server dialog.

    Figure 20.3. The New Server dialog is used to connect to new OLAP data sources.

    graphics/20fig03.jpg

  4. Enter a caption for the OLAP Server you will be adding. This caption will appear in the Crystal OLAP Connection Browser. Enter the name of the SQL Server Analysis Server for the server name and click OK.

  5. Back in the Crystal OLAP Connection Browser, navigate into the presented listing of servers (there will likely only be the one you just added) and double-click on the sample HR cube.

  6. Click the Next button to proceed..

graphics/bookpencil_icon.gif

On the Data Screen of the OLAP Report Creation Wizard, in addition to the Select Cube button, a Select CAR File button exists. CAR files are Crystal Analytic Reports (CAR) and are created with the sister product to Crystal Reports Crystal Analysis. This product is an OLAP-focused reporting and application tool and will be briefly introduced later in this hour. These CAR files can be treated as multidimensional data sources because they themselves contain connectivity information to an underlying OLAP data source.


At this point, we will review the concept of Rows and Columns in our OLAP report.

Specifying OLAP Rows and Columns

The 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.

graphics/20fig04.jpg

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.

graphics/20fig05.jpg

graphics/lightbulb_icon.gif

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. Example selection shortcuts include the ability 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:

  1. Select the Store Type Dimension from the available dimensions list as the Row Dimension using the Row Dimension Arrow Button. (Note: It will likely be necessary to remove a default dimension to ensure that this is the only dimension in the Row Dimensions list view.)

  2. Using the Select Row Field's button, select all the Store Types (for example, Supermarket, Headquarters, and so on) from the Member Selection dialog, but deselect the aggregated top level All Stores field. This will enable the OLAP grid to present all the different store types down the side of the grid as rows.

  3. Select the Time Dimension from the available dimensions list as the Column Dimension using the Column Dimension arrow (>) button. (Note: It will likely be necessary to remove a default dimension to ensure that this is the only dimension in the Column Dimensions list view.)

  4. Using the Select Column Field's button, select the years 1997 and 1998 from the Member Selection dialog, but deselect all children members for these members. This will enable the OLAP grid to present a comparison of the two years data in two side-by-side columns.

  5. Click the Next button to proceed.

At this point, we will review the concept of OLAP dimension filters and pages in our OLAP report.

Specifying OLAP Dimension Filters and Pages

The 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.

graphics/20fig06.jpg

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:

  1. Select the Measures dimension from the Filter list box.

  2. Instead of selecting a specific filter using the Select Filter Value button, click the Link to Parameter Create/Edit button to enable the business user to dynamically select this filter every time the report is run. The Edit Parameter Field dialog, as displayed in Figure 20.7, will appear.

    Figure 20.7. The Edit Parameter Field dialog called from Filter/Page screen.

    graphics/20fig07.jpg

  3. In the Prompting Text text box, enter the text that you want your user to be prompted with when this report is run. In this case, it could be something similar to Please select the Measure to be used in your report. Also, ensure that the Discrete Value(s) radio button is selected because a range of entries is not required (or allowed) here.

  4. To avoid the requirement of the user needing to type in any text, defaults can be set so that selection from a drop-down box is possible. To do this, click the Select Default button and the dialog in Figure 20.8 will appear.

    Figure 20.8. The Set Default Values dialog.

    graphics/20fig08.jpg

  5. The Measures table will be preselected because the report understands the links that are being made based on your previously highlighted dimension. Select the level 0 field in the table drop-down box. (There will only be one option here because only one level is available in this dimension.)

  6. Move all the member values presented to Description list box by clicking on the double arrow (>>) button. Note that the program automatically displays the more meaningful member descriptions instead of the more cryptic long names.

  7. Ensure that the Display drop-down box has Description selected and that the Order drop-down box has no order selected. Click OK twice to get back to the Filter/Page dialog of the OLAP Report Creation Wizard.

  8. Once back, highlight the Pay Type dimension in the Filter list box and click the arrow transfer/select button to move this to the Page list box. The Member Selection dialog will immediately appear with the Pay Type Dimension Hierarchy presented.

  9. Select the Hourly and Monthly pay types (children of All Pay Types) and deselect the All Pay Types field. Page views (individual OLAP Grids) will now be created for each of the monthly paid employees and the hourly paid employees. If this isn't clear now, it should make more sense when we are visualizing the report.

  10. Click OK and then Next to proceed.

graphics/alarmclock_icon.gif

After Parameters or Multi-Value Paged Dimensions have been set in the OLAP Report Creation Wizard, you must access the OLAP Report Settings option under the main Report menu to edit them. These settings are not configurable in the OLAP Expert.


At this point, we will review the concept of styles in our OLAP report.

Adding Report Styles in the OLAP Report Wizard

The 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.

graphics/20fig09.jpg

Adding Charts Via the OLAP Report Wizard

The 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.

graphics/20fig10.jpg

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.

graphics/20fig11.jpg

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:

  1. On the Style dialog, select any style that suits your preference and Click the Next button.

  2. On the Chart dialog, select Pie Chart as the Chart Type by selecting the radio button associated with that chart type. This will provide a nice way of visualizing comparables across different store types.

  3. Provide your chart with a title similar to Hour 20 Sample OLAP Report by entering this in to the Chart Title text box.

  4. Select Store Type as the On Change Of field. This will facilitate the comparison of the six different store types. Leave the Subdivided By drop-down field empty.

  5. Click Finish on the OLAP Report Creation Wizard. You will be prompted to select a parameter for the Measure dimension. After selecting Average Salary (or another field if you prefer), a report will be generated that should look similar to Figure 20.12.

    Figure 20.12. The sample OLAP Report created using the OLAP Wizard.

    graphics/20fig12.jpg

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 Expert

After 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.

graphics/20fig13.jpg

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.

graphics/20fig14.jpg

Customizing Labels in the OLAP Expert

The 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.

graphics/20fig15.jpg

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.



Sams Teach Yourself Crystal Reports 9 in 24 Hours
Sams Teach Yourself Crystal Reports 9 in 24 Hours
ISBN: B003D7JUVW
EAN: N/A
Year: 2005
Pages: 230

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net