Crystal Reports: Dynamic TopN

TopN is a great way to present meaningful data to End Users. Rather than displaying all your groups and summary fields  you can make your report display only the top 8 or bottom 12 groups. You also have the choice to group the remaining data into one big “Others” group.

(Remember that if you un-tick “Others” the grand total remains unchanged).

On www.Tek-Tips.com, there was a request for a dynamic TopN and we’d like to share our solution with you.

Use the TopN feature to Sort your groups by the total. The “All” option lets your sort by the summary field.

For a dynamic TopN use a Sort Descending, and for a dynamic bottomN use Sort Ascending.

Then to decide how many groups to display, use a numeric parameter {?Groups}and conditionally suppress the group and all lower level groups and details with 
GroupNumber > {?Groups} 
Dynamic Total for Others 

The next question to ask is how to calculate the total value for the others. A running total can’t be used as it can’t refer to Print State functions like “GroupNumber.”

But we can use the three formula technique using a variable to accumulate the total as required. The condition in the calculate formula is 
WhilePrintingRecords; 
Global numbervar Others; 
if GroupNumber>{?Groups} then Others:=Others+{Customer.Last Year's Sales} 
Then create a formula to display the value of the “Others” in your report. 
WhilePrintingRecords; 
Global numbervar Others; 

Viewer selection?

A question on www.tek-tips.com  asked if it was possible to have a report ask if the viewer to select either the TopN or BottomN. The answer was based on the discovery that a BottomN report is the same sequence as a TopN of the negative of the summary field. 

1) Create a parameter {?Top/Bottom} It can have a value of “Top” or “Bottom” 
2) Create a formula {@Sort} 
If {?Top/Bottom} ="Top" then {table.value} 
else -{table.value} 
3) Create a subtotal of this in your group and suppress it. 
4) use TopN (or the Group Sort Expert in CR9) to do a Sort on this subtotal (Descending).

This will enable the user to select “Top” or “Bottom” and see the selection in their report 

Make parameter values dynamic

If you have Crystal Reports XI or higher, you can make parameter values dynamic by selecting a Dynamic list of values, a different set of options is available. You can select a field to populate the list of values at run time. This is a feature users have been asking for many versions now.

But there is more. The list of values can come from a table or view used inside the report or a SQL command you control more directly.

If the report is managed inside Business Objects Enterprise, you must use a Business View to populate the list. Using BO Enterprise management the list can be scheduled and each series of cascading values can be scheduled at different intervals. So a field that doesn’t change frequently can be populated at a less frequent rate. And something that is very dynamic can be populated at run time.

The BO Enterprise environment also enables the sharing of the list among multiple users. The cache server retains the list for a short period so other users can make use of the same query against the database.

A final comment is that the parameter input form has been standardized to look the same to Crystal Reports and BO Enterprise users. The Windows calendar control has been replaced with an html style sheet and dates must now be entered in the format yyyy-mm-dd. risk report object.

It can summarize a large volume of data into a grid of columns and rows. Each new version of CR introduces some great new formatting features of Crosstabs, so now we have multiple rows and columns, Customized Group Names, Styles, various summary functions, and a wide range of custom formatting.

The crosstab must have a field to specify the column and row. Tony Burns wanted a crosstab where the user could select the fields to use for the columns and the rows. This can be done with the user entering a value via a parameter.

The create a formula to use the parameter to select what field to use for the column:

if {?Col1}="Customer" then {Customer.Customer Name} else

if {?Col1}="Employee" then {Employee.First Name} + " " + {Employee.Last Name} else

if {?Col1}="Region" then {Customer.Region} else

if {?Col1}="Country" then {Customer.Country} else

if {?Col1}="Product" then {Product.Product Name} else

if {?Col1}="Product Class" then {Product.Product Class} else

if {?Col1}="Product Type" then {Product_Type.Product Type Name} else "Undefined"

You could have a similar formula for the Row and even the summarized field. The structure of the crosstab report is fixed with one row field, and one column field. You could easily create other report variations with two rows and/or two columns.

This article is copyrighted by Crystalkeen, Mindconnection, and Chelsea Technologies Ltd. It may be freely copied and distributed as long as the original copyright is displayed and no modifications are made to this material. Extracts are permitted. The names Crystal Reports and Seagate Info are trademarks owned by Business Objects.