- Introduction to OLAP
- OLAP Concepts and OLAP Reporting
- Recently Added or Changed OLAP Features in Crystal Reports
- Using the OLAP Report Creation Wizard and OLAP Expert
- Advanced OLAP Reporting
- Troubleshooting
- Crystal Reports in the Real WorldOLAP Summary Report with Drill-Down
This chapter is from the book
This chapter is from the book
This chapter is from the book
Crystal Reports in the Real World—OLAP Summary Report with Drill-Down
The scenario discussed here describes the flexibility behind accessing multidimensional and relational data sources in one report. The benefit of this type of functionality is to enable the user to see aggregated information coming from a cube while allowing drill-down on the relational data to provide greater detail. By using parameters in this report, you let the user decide which information elements to display.
- Start by creating a simple sales report against the sample Xtreme data source. For the data, select the First Name, Last Name, and Last Year's Sales fields from the Customer table. Group the report by region, city, and then customer. Hide the Details section and the City and Customer groups and enable drill-down on these sections. The report at design time should look like Figure 16.19. Before moving on, add Summary fields for Last Year's Sales into each of the Group Header fields (Country, Region, and City). You can quickly accomplish this by using the new (in version 2008) Add to All Group Levels check box in the Insert Summary dialog.
Figure 16.19 Framework for drill-down integrating both relational and OLAP data.
- Now add to this report an OLAP grid against the sample cube used earlier in this chapter—Sales and Employees from Foodmart. Using the steps described earlier in this chapter, point the grid at the sample Sales and Employee OLAP cube selecting only the Measures, Time, and Stores dimensions. Select the Stores dimension for the rows and the Time dimension for the columns. Change the Stores rows to include only USA, Canada, and Mexico to limit the number of rows displaying in the report. Also change the Time dimension members selected to Q1, Q2, Q3, and Q4 of 1998.
- Drop the OLAP grid in the Report Header area. Now insert a bar chart based on the relational source that displays Last Year's Sales on change of values in the City field and place the chart in the Group Header for Country to enable the user to visually understand the contribution of sales from each of the selected cities. In design view, the report should look similar to Figure 16.20. Perform the same filtering task in the report Select Expert so that the relational data source is limited to the same three countries (USA, Canada, and Mexico).
Figure 16.20 A report using both OLAP and relational data sources. The pie chart based on the relational data enables drill-down into the relational data details.
- If the user viewed this report, he would currently see both the chart and the OLAP grid at the top of the report summarizing the same information but sourced from two different data sources: a pre-aggregated SQL Server data cube and a relational database. (This example assumes that similar data is the basis for both data sources.) To enable end users to turn off the grid display, create a parameter field that specifies whether to display the grid. This enables users to decide whether they want to look at the summary information in both a grid and chart format or only in a chart.
- Create a parameter of Boolean type called Display Grid.
- Conditionally suppress the Report Header section containing the grid based on the values supplied to the parameters by right-clicking on the Report Header and selecting Section Expert from the Report Explorer. Click on the X+2 formula button next to the suppress option. Inside the formula editor, type
{?Display Grid}=falseand close the editor. Now when a user runs the report, he is prompted to select whether he wants to see the summary OLAP grid. Save the report. On display, it should look similar to Figure 16.21.
Figure 16.21 Report showing both the OLAP grid and charts and enabling drill-down from the high-level summary information displayed from the OLAP grid into the relational details.
)
)
)
This example illustrates how to combine relational and multidimensional data in one report to allow for different views based on the same underlying data. This allows drill-down on relational elements and provides aggregate header information for views on summary OLAP data.