This chapter is from the book
This chapter is from the book
This chapter is from the book
Advanced OLAP Reporting
Up to this point, the OLAP Expert and OLAP Report Creation Wizard have demonstrated the capability of Crystal Reports to rapidly create OLAP-based reports. In addition to those capabilities, Business Objects provides advanced analytic capabilities against OLAP data sources through some advanced OLAP-oriented features in Crystal Reports and through a set of online OLAP functionality via BusinessObjects Enterprise's Voyager functionality (formerly called OLAP Intelligence and before that Crystal Analysis). The last four sections of this chapter introduce some of these advanced features for Crystal Reports.
Interacting with the OLAP Grid
Crystal Reports provides some powerful interactive OLAP features from within the Crystal Reports Preview and Design tabs. Figure 16.15 displays the right-click menu that appears when you right-click on the year Q4 member in this chapter's sample report.
)
Figure 16.15 The right-click menu provides access to advanced OLAP features.
Advanced features made available here include conditional member highlighting, setting column display options, hiding and showing members for asymmetrical reporting, adding calculations, adding filters, reordering members, changing the member caption, expanding members (that is, drilling into the children members), adding sorts, and adding automatic totals to the OLAP grid. Although exploring these features in detail is beyond the scope of this chapter, it is important to note their availability for enhancing your OLAP grid presentations and reports. For detailed information on all these functions, you can review a legacy chapter on OLAP Intelligence that is available at www.usingcrystal.com.
One feature of note is the active nature of the column and row dimensions in the OLAP grid. By double-clicking on any member in either the row or column headings and assuming that the selected member has lower level members (children), the OLAP grid dynamically expands to include that member's children in the grid. In OLAP parlance, this is drilling down. Figure 16.16 shows the result of drilling down on the Q4 Header in this chapter's sample report. An alternative means to drilling down is to click on the + icon displayed beside any row or column dimension member.
)
Figure 16.16 Sample OLAP-based report with Q4 member's children expanded.
A dimension member can subsequently have its children contracted by double-clicking on the parent member or clicking on the – icon beside the involved parent member. This feature enables you to interactively determine the best static viewpoint to provide to the business user audience for the report.
Pivoting the OLAP Grid
After an OLAP grid has been added to a report, as in this chapter's sample, Crystal Reports provides the capability to easily swap the grid's columns and rows. OLAP parlance calls this pivoting the OLAP grid. Figure 16.17 highlights this chapter's sample report after pivoting with this function. To access this function, right-click on the OLAP grid and select the Pivot OLAP Grid option. Pivoting the OLAP grid does not affect any OLAP charts or maps already on the report.
)
Figure 16.17 A preview of the sample report after pivoting the OLAP grid. Notice how the chart and the grid have changed.
This function is particularly useful when attempting to decide which viewpoint of the involved OLAP grid is most helpful to the business users of the report.
Using the Cube View Functionality
The Cube View (previously called the OLAP Analyzer) is a powerful worksheet analysis tool introduced in version 9 of Crystal Reports. The Cube View is initiated through the View Cube option on the right-click menu of the OLAP grid (make sure that you don't have any specific grid objects selected) and is accessed through the Cube View tab in the Crystal Reports Designer (see Figure 16.18). Report designers and analysts familiar with other OLAP interface tools are instantly comfortable with the Analyzer because it provides access to the OLAP cube through a traditional OLAP worksheet.
)
Figure 16.18 The Cube View tab launched by the OLAP Analyzer provides a powerful analytic tool for report designers and power users.
The Cube View tab's worksheet is designed for rapid analysis of the underlying OLAP data through a rich and interactive interface not available in the OLAP grid presented in the Crystal Reports Preview tab. Dimensions can be rapidly shifted, swapped, and nested by double-clicking on them and dragging them into any row, column, or paged dimension area. A dimension member can be quickly expanded and contracted by clicking on its associated + or − icon. A right-click menu in the OLAP Analyzer view of the cube offers access to additional calculations, sorts, filters, automatic totaling, exception highlighting, data analysis, and custom captions.
The Cube View is a powerful report design tool because it lets Crystal Reports developers create impressive flat views of the underlying multidimensional/OLAP data in a short timeframe and subsequently format the created OLAP grid in the Preview tab.
Using Charts and Maps Based on OLAP Grids
As described in Chapter 8 and discussed briefly in the "Adding Charts via the OLAP Report Creation Wizard" section earlier in this chapter, OLAP grid data can be presented through visually appealing charts and maps. To create a chart or a map based on OLAP data, an OLAP grid must pre-exist on your report as a data source to form the basis of the chart or map. Selecting the Insert Chart or Map command from the Insert menu (or the respective icons on the Insert toolbar) enables the creation of an OLAP-based visualization.
The creation process for both charts and maps requires the specification of an On Change Of field. This is the field that the chart or map breaks its summaries on (for example, country, state, product, sales rep, and so on). You can specify an optional Subdivided On field as well. The results of specifying an extra variable to divide the data on has different results for various chart types. Explore various charts to find those most suitable for your business problem. Using the Subdivided On field with a map adds a bar or pie chart to every main region on the selected map. An example of this might be a pie chart depicting the breakdown of sales for each country.