• Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint
Share this Page URL

Chapter 16. Multidimensional Reporting A... > Crystal Reports in the Real World—OL...

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 are displayed.

Start by creating a simple sales report against the sample Xtreme data source. For the data, select the Customer table. Group the Report by Region, City, and then Customer. Hide the City and Customer groups and enable drill-down on these sections. The report at design time should look like Figure 16.21.

Figure 16.21. Framework for drill-down.

Now add an OLAP grid to this report that will go against an offline cube file. Using the steps described earlier in this chapter, point the grid at the xtreme.hdc file located in C:\Program Files\Crystal Decisions\Crystal Reports 10\Samples\En\Databases\Olap Data. Keep the defaults assigned by the grid expert except in the Rows/Columns tab; here, you will change the Customer Rows to only include AZ, CA, and MA to limit the number of rows displaying in the report. Perform the same task in the report select expert so that both data sources return the same information.

Drop the OLAP Grid in the Report Header area. Now split the Report Header area into A and B sections using the Insert Section Below right-click menu function from the Report Header Title area. The OLAP Grid remains in Report Header A. Insert a pie chart based on the relational source that displays Last Year's Sales on change of values in the Region and City fields and place the chart in Report Header B to enable the user to also visually understand what the contribution of sales is from each of the selected regions. The report in design view should look similar to Figure 16.22.

Figure 16.22. 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. Create two parameter fields, which will specify whether to display the grid or chart or both. This enables the users to decide how they want to look at information in the report.

Create a parameter of Boolean type called Display Grid and a parameter of the same type called Display Chart.

Next conditionally suppress sections containing the grid and the chart based on the values supplied to the parameters. To do this, right-click on Report Header A and select Section Expert from the Report Explorer. Make sure Suppress is checked and then click next to the suppress option on the formula sign. Inside the formula editor type in

								{?Display Grid}=false

and close the editor. Highlight Report Header B and repeat this step using the parameter you created before called Display Chart. Now if the user runs the report he will be prompted to select whether he wants to see the chart, the grid, or both. Save the report as chap16OLAPLinkedToDetail.rpt. On Display, it should look similar to Figure 16.23.

Figure 16.23. Report showing both the OLAP grid and charts.



Not a subscriber?

Start A Free Trial

  • Creative Edge
  • Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint