Tip 3: Report Sections and Groups
Regardless of the specific product, almost all report writers will break a report apart into logical sections such as a header, footer, and detail area.
The report writer will allow you to add sections to the report, such as a logical group to represent a one-to-many relationship. For example, you might define a group that consists of a customer name in the group header, a list of orders for the customer in the detail area, and then a subtotal line for the customer in the group footer. Additionally, you might define multiple groups, such as a territory, all the customers within that territory, and then all the orders for that customer. In that case, you would have two group headers, one detail line, and two group footers. Regardless of the number of groups, a report can have only one detail band: if you need to display multiple levels of detail, you'll need to build a subreport, which I'll cover in Tip 7.
Sections and Groups in Crystal
An empty Crystal report contains a report header and footer, a page header and footer, and a report detail band. You can insert a group by right-clicking in the main report body, and then selecting "Insert
.group" from the shortcut menu. You can also customize section behavior by right-clicking any section divider and selecting Section Expert, which loads the Crystal Section Expert (see Figure 7
). The Section Expert lets you choose from these options for each section:
Hide. Allows you to implement drill-down capabilities (I'll cover this more in Tip 9).
- Suppress. Allows you to conditionally suppress a section.
- Print at Bottom of Page. Forces Crystal to print a section at the bottom of the page, even if the preceding section (usually a detail band) finishes before the bottom of the page. You'll usually use this for reports or forms where a section (such as a subtotal line) must print at the bottom of a page.
- New Page Before / New Page After. Allows you to force a page break before or after a section. For example, if you are generating a report of broker commissions by broker, you would want to start a new physical page for each broker, so that no brokers would be able to see information on a page for anyone else.
- Reset Page Number After. Typically you use this in conjunction with the New Page Before/After option, so that each new section starts with page one.
- Keep Together. This is one of the most powerful (and sometimes most misunderstood) options. Some reports contain sections that must stay together on the same page, such as a legal terms and conditions clause. This option ensures that if the section would otherwise split across pages, Crystal will start a new page early to fit the entire section. (Note that there is also a group option to keep an entire group together, which I'll cover a little later.)
- Suppress Blank Section. This allows you to suppress a blank section. Have you ever tried to print addresses for a record where the second (or third) address line might or might not exist, where you don't want blank spaces between the first address line and the city/state/zip? By inserting individual sections, placing each data column in a separate section, and checking this option ON, you can format the addresses the way you want them.
- Underlay Following Sections. This option is quite powerful; it allows you to superimpose one section on top of the other. For example, you might have a pie chart in one section, and then a detail band that displays supporting data in the next section, and you want to display them side-by-side.
As for adding groups, Figure 8
and Figure 9
show the two-tabbed Crystal Insert Group dialog box. In this two-tabbed dialog box, Crystal allows you to define such items as whether the group is based on an existing column or a formula, the sort order, and whether the entire group should stay on one page.
Sections and Groups in SSRS
|Figure 8: Crystal Group Expert (1 of 2).||
|Figure 9: Crystal Group Expert (2 of 2).||
By default, an empty SSRS report starts with just that—an empty report. However, by using either the table or matrix report controls, and by inserting a page header/page footer (see Figure 10
), you can easily build a basic report. After you add either a table or matrix to the report body, you can define a group (which will insert a group header and group footer) by right-clicking in the row selector area and selecting Insert Group (see Figure 11
). SSRS will then display the Group Options (see Figure 12
). Note that SSRS does not provide the functionality to keep an entire group on a subsequent page.
|Figure 10: SSRS Report Body Screen, Adding Header/Footer.||
One other note: Recently I conducted an SSRS training session where I created a report with two groups and intentionally defined the groups in the wrong order (to show how to correct it). I am so accustomed to the group selector bars in Crystal that let you visually drag the bars to reverse the order that I mentally assumed that SSRS has the same option. Unfortunately, it doesn't! You'll need to modify each group number manually in SSRS if you need to change the physical group sequences.