Creating Summary and Drill-Down Reports


Creating Summary and Drill-Down Reports

A details report shows every individual detail record in the database. This may often be preferable for certain listings or smaller transaction-type reports. Often, however, a viewer will only want to see subtotals, counts, averages, or other summary information for certain groups on the report. The details information used to arrive at those summaries isn t as important. This calls for a summary report.

In its simplest form, a summary report is a report with one or more groups with the details section hidden or suppressed. Consider Figure 10-3, a details report of orders, grouped by customer.

click to expand
Figure 10-3: Orders detail report

This shows every order for the customer, with order count and order amount subtotals appearing in the group footer. While this may be useful for a report viewer concerned about individual orders, the sales manager or account representative may often just be interested in the summary information for each customer ”just the bottom line. All the order details just get in the way of their analysis.

In this case, simply hiding or suppressing the details section will create a much more meaningful report for these viewers . You can hide or suppress the details section from the Section Expert. As a shortcut, there are Hide and Suppress options available right in the section pop-up menu, as well. Just right-click the gray details section name at the left of the Design or Preview tab and choose Hide (Drill-Down OK) or Suppress (No Drill-Down) from the pop-up menu.

The details section will simply disappear from the Preview tab, while the group header and footer still show up. Figure 10-4 shows the resulting summary report, which is much more succinct and meaningful to a viewer looking at the big picture.

click to expand
Figure 10-4: Orders summary report

If you later want to change your report back to a details report, you need to have the details section reappear. Just display the Section Expert by using the toolbar button or pull-down menu option. Select the details section and turn off the Hide or Suppress property. You can also display the Design tab and right-click the gray area to the left of the screen where the details section is hidden or suppressed. To redisplay the details section, choose the opposite of the Suppress or Hide option you initially set.

Tip  

You can choose how a hidden or suppressed section appears in the Design tab. Choose File Options from the pull-down menus and look for the Show Hidden Sections option in the Design View section of the Layout tab. If this is checked (the default), hidden or suppressed sections will still appear in the Design tab, but they will have gray shading. If you turn this option off, they will only show the bottom border of the hidden or suppressed section in the Design tab.

Choosing whether to hide or suppress a section determines whether or not you want a report viewer to drill down into the section. Also, note that the Suppress property can be conditionally set in the Section Expert, while the Hide property cannot.

Drill-Down Reports

One of the most powerful features of an online reporting tool like Crystal Reports is interactive reporting. You may be creating a report to distribute to a large audience via a web page in a Crystal web application (covered in Part II of this book), or as part of a custom Windows application (covered in Part III). A truly useful report will initially present viewers with higher-level summary or total information. If the viewer sees a number or other characteristic of the report that interests them, you want them to be able to drill down into just that particular area.

Note  

Drill-down is an interactive feature only applicable to viewers looking at a Crystal Report in its native format. Drill-down isn t available in any reports exported to another format, such as Word or Excel. And obviously, drill-down isn t a feature that applies to reports printed on paper!

In its simplest form, a drill-down report can be created by hiding the details section (hence, the Drill-Down OK notation alongside the Hide property) in a report that has one or more groups. When you point at the group name field, or a subtotal or summary object in a group header or footer, you ll notice your mouse cursor change to a magnifying glass, or drill-down cursor. When you double-click, a separate drill-down tab will appear, showing the group header, footer, and details sections for that group. You can then navigate to the main Preview tab to see the summary report, or back to individual drill-down tabs to see the details information. You can double-click in the Preview tab as many times as you want in order to create additional drill-down tabs.

More complicated drill-down reports can be created by using multiple levels of grouping, along with creative use of section hiding. Figure 10-5 shows a drill-down report containing a details section and three groups: country, region, and city. Initially the report just shows countries and their totals. If you drill down on a country, you ll find region subtotals for that country. Drilling down on a region will show city totals. And finally, drilling down on a city total will show individual orders placed from that city.

click to expand
Figure 10-5: A more complex drill-down report
Tip  

Make note of the Show All Headers on Drill-Down option. When you turn this option on (it s off by default), your report will display all higher levels of group headers when you drill into a group, instead of showing just the previous level of group headers. Choose this option for the current report only by selecting File Report Options. To choose the option for all new reports in the future, set the option on the Reporting tab after choosing File Options.

If you re interacting with a report, it s important to remember what will print or export if you click the Print or Export button on the Standard toolbar, or choose File Print or File Export from the pull-down menus. Only what s shown in the current drill-down tab (or Preview tab) will print or export. If you want to just print one drill-down tab, choose the drill-down tab and then click the Print button. If you want to print all the summary information in the main Preview tab, make sure it s selected before you print or export. If you want to print or export both summary and details information in the same report, you must either display the details section and print from the Preview tab, or create a separate report more appropriate for printing.

Using Tool Tip text (under the common section of the Format Editor) can help a viewer determine what will happen when they double-click an object. By creating a string formula with the Tool Tip text Conditional Formula button in the Format Editor, you can give a viewer more information about what the object contains. For example, this tool tip will appear given the following Tool Tip text Conditional Formula added to the summary field in the Country group footer:

 "Double-click to see regional totals within " & 
GroupName ({Customer.Country})
start sidebar
Creative Use of Column Headings and Group Headers in Drill-Down Reports

If you leave Crystal Reports' default column headings in the page header and simply hide or suppress lower-level group headers in your drill-down reports, you may get undesirable results when the viewer sees the initial Preview tab.

Column Heading Problems

The first problem will be the appearance of column headings in the Preview tab above the first group header, but with no matching detail records. But when you eventually drill down to the details level, the page header won't appear, so the viewer won't see the column headings.

There are two ways of resolving this problem, depending on how many levels of grouping exist on the report. If you only have one level of grouping, perform the following steps:

  1. Move the column headings from the page header to the group header, either above or below the group name field (or remove the group name field altogether), depending on how you want the drill-down tab to appear.

  2. Copy the group name field from the group header into the group footer.

  3. Hide the group header along with the details section.

    This way, the summary report will just show one line per group until you drill down. Then, the group header (containing the column headings) will appear inside the drill-down tab, along with the details sections.

    However, if you have more than one level of grouping, the previous technique won't work properly ”you'll see the column headings appear over and over again at the last group level. In this case, use this approach:

  4. Create a second details section (Details b) and move the column headings into it.

  5. Swap Details b and Details a so that the column headings are on top of the details section that contains database fields.

  6. Select the text objects that comprise the column headings. You may multi-select them with ctrl -click or an elastic box. Using the Format Editor, choose the Suppress if Duplicated formatting option.

  7. Using the Section Expert, choose the Suppress Blank Section formatting option for Details a.

    This technique, while a little more time-consuming , provides perfect results. When the lowest level of grouping appears, there will be no column headings (because they've been moved to the details section). But when you drill down to the details level, the column headings only show up once at the top of the drill-down tab, because of the Suppress if Duplicated/Suppress Blank Section formatting combinations.

    Repeating Group Headers

    If you simply choose the Hide (Drill-Down OK) formatting option on group header sections, your report may suffer from extra sets of group headers that appear when you drill down. For example, if you drill down into a country group to see all the regions within the country, the region group header will print before every group footer. To solve this problem, you may try to suppress the region group header so that it will never show up. However, when you then drill into a region group to see cities within the region, the region group header won't print at the top of the list of cities.

    In theory, you'd like the region group header to show up when you've drilled into the region group to see cities, but not show up when you're looking at the region group at its summary level. But, what conditional formula can you use to suppress the region group header so that it only shows up when there isn't a country group header there as well?

    Crystal Reports features the DrillDownGroupLevel function in the Crystal Reports formula language. This function allows you to test for a certain drill-down level and conditionally suppress a group header (or perform any other functions or calculations, for that matter), based on the level of the drill-down. Making use of this function to conditionally suppress group headers will allow you the ultimate control over your report behavior when the viewer drills down.

    Using the previous Country/Region/City drill-down scenario as an example, you suppress the region group header (Group Header 2) conditionally so that it won't appear when you drill down to the country level, but will appear when you drill down to the region level. This is simply a matter of using this formula to conditionally suppress Group Header 2:

     DrillDownGroupLevel = 1 

    This will suppress the group header when a Country drill-down occurs (level 1), but will not suppress it when a Region drill-down occurs (level 2). You may also use the DrillDownGroupLevel function to determine if the main Preview tab is being displayed. If the DrillDownGroupLevel function returns a zero, no drill-down is occurring at all.

    Examples of the drill-down techniques discussed here can be found in a sample report on this book's companion web site. Look for the Drill Down.RPT sample report at www.CrystalBook.com.

end sidebar
 



Crystal Reports 10
Crystal Reports 10: The Complete Reference
ISBN: B005DI80VA
EAN: N/A
Year: 2004
Pages: 223
Authors: George Peck

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