... how to correct Top N/Group Selection grand totals. Continued from March 2007.... Very often you'll combine both subtotal and summaries at the group level, with grand totals or summaries that are placed in the report footer. This way, you'll be able to analyze not only individual group numbers, but the totals for the entire report. The grand totals are created with the Insert Grand Total option (in CR 8.x) or the Insert Summary option (in CR v9). These choices automatically place the desired total or summary in the report footer.

However, if you use this feature in conjunction with the Top/N Sort Group Expert or Group Selection, you may notice odd behavior with grand totals. In particular, if you use techniques (discussed in the March 2007 newsletter) to limit the number of report groups shown on the report, you'll notice that the grand totals don't add up. This may not only be difficult to explain - it could be disastrous if important business decisions are based on the combination of group totals and grand totals being shown on the report.

The Top N/Sort Group Expert is known as the Group Sort Expert in CR 9.

The explanation for this behavior becomes clearer if you consider how the Top N/Sort Group Expert and Group Selection work. Because these features use group subtotals and summaries to determine how many and which groups appear on the report (Group Selection limits display of groups to only those that have subtotals or summaries that meet a criterion, and the Top N/Sort Group Expert limits groups to the Top or Bottom N groups based on a subtotal or summary), report totaling must take place before the groups are limited.

As grand totaling in the report footer takes place during the same Crystal Reports process that creates totals in group footers, the grand totals are already calculated before Group Selection or the Top N/Sort Group Expert limits the groups. As such, the grand totals will always show values for all groups on the report, not just those that are being shown.

One way to solve the problem: don't add grand totals to the report if you are limiting group display with Group Selection or Top N/Sort Group Expert. However, this may be considered a severe Crystal Reports limitation, if you desire to, for example, show the top 10 salespeople in the company, and the associated grand total resulting from just the top 10 salespeople.

The way to solve this dilemma is by using running totals. Running totals are designed to accumulate or "build up" a total as the report progresses. A running total will solve the inaccurate grand total problem for one simple reason: running totals are processed by Crystal Reports after Group Selection and the Top N/Sort Group Expert have been processed.

1. Open or create a report that makes use of the Top N/Sort Group Expert or Group Selection.
2. If you have added a grand total to the report footer with the Insert Grand Total or Insert Summary Option, double-check the total. Since you have limited the display to only certain report groups, the grand total will not equal the total of the displayed groups.
3. Create a running total to replace the grand total. Begin by selecting the field in the details section that you wish to base the running total on.
4. Right-click. Choose Insert|Running Total from the pop-up menu. The Create Running Total Field dialog box will appear.
5. Alternatively, you may click on the Running Totals category of the Field Explorer, right-click, and choose New from the pop-up menu. The Create Running Total Field dialog box will appear.
6. Type a meaningful name for the running total in the Running Total Name text box.
7. If you started from the Field Explorer, select the field you want to total in the Available Tables And Fields list. Click the right arrow button next to the Field To Summarize box. If you had already selected a field in the details section, the Field To Summarize box will already contain the field name.
8. Choose the type of summary you want to perform (sum, count, average, etc.) in the Type of Summary drop-down list.
9. Leave the Evaluate radio button set to For Each Record.
10. Leave the Reset radio button set to Never.
11. Click OK to save the running total.
12. If you originally selected a field in the details section, the running total will be placed in the details section. Drag it from the details section to the report footer.
13. If you began running total creation from the Field Explorer, drag the running total from the Field Explorer to the report footer.
14. Preview the report and check the value of the running total. It should correctly match the totals of groups that are actually appearing on the report.

email your favorite Crystal tip, trick or technique.

Training – is it truly the best solution?
The boss comes in and says, “There’s a huge project coming up. Get report design training for everybody involved.” Your reputation is on the line. So you search the web, pick some vendors and start emailing and calling them. Of course they want to sell you as much training as possible whether it meets your needs or not. It all sounds wonderful, very expensive and time consuming. But then, one of the vendors asks, “why are you looking for training?” That gets you thinking….

Training is the best solution when the goal is solely to train people – period.

When someone calls me for the first time and they’re looking for training, I ask them, “Why?” Always keeping the customer in mind, I want to make sure they are doing the right thing for their organization. This guides the person to think about what they truly need. OK, so there’s this huge reporting project. If the reports, specs and data source are simple and to the point, then of course, the newly trained people can take over and probably do very well. If the project involves complex reports, a tangle of tables, a datamart, spreadsheets, sophisticated formulas, subreports and linking, well, your newly trained report designers may not be up to the task. This is not their fault – they just don't have the experience. You’re not going to look very good when the training you selected (and the time and money invested) results in unmet goals, inaccurate reports and frustrated upper management.

Consulting, rather than training, is often the best solution. Why? For a number of reasons. The two considered the most important are time and money. Equally important are accuracy and efficiency. Think about it. In the time it takes to train employees (let’s say a 2-day class), and getting them familiar with the tool (give them 10 working days), a consultant will be well on her or his way to completing the project! When you hire a consultant there’ll be a few meetings and calls about specs and the data source, but after that, all you have to do is wait for the reports to be completed and delivered. You verify them for correctness and sign off.

Ah, but what about the consultant? Good question. You have to be careful there, too. Once again, if all you care about is cost, remember the Designer’s Holy Triangle (I do not know the triangle’s origin) – choose two: fast, cheap, good. Choose your consultant wisely: research, reputation, referrals. Avoid fixating on getting a low hourly rate. Concentrate on the development of a comprehensive, detailed plan, the end result being a well-defined timeline with clear definitions, deadlines, deliverables, and ultimately, the solution. Doing otherwise will put you at risk. You may end up in serious trouble, struggling to salvage the project as well as your reputation and good standing in your organization.

You’ll look good when you tell the boss, “We can have accurate, sophisticated, efficient reports, delivered and signed off ahead of schedule at considerably less cost when we choose consulting over training. Here’s how.”

So, the next time you are given the responsibility of finding training, protect your reputation as well as your organization’s time and money by answering this all-important question: is training the best solution? When you have a solid answer to that question you’ll be on your way to being a hero.

FREE Monthly Online Learning demo session now available.
Call 800 773 3472.

Logon...Learn...LIVE! Ablaze Group Online Learning 2007.