Advanced programming cross-tab reports using Crystal Reports XI.

Prepared by Dr. Alexander Werner. KB# 10001.

Cross-tab reports present data in one of the most efficient ways to discover trends by different factors. As such, they are widely utilized in BI analysis and reporting systems. The great variety of BI tasks requires different types of functions to be displayed in the cross-tabs.

Unfortunately, the choice of functions available in Crystal Reports XI is limited to a small set of statistical aggregate functions and their extensions to calculate percentages for ratios of the field to horizontal or vertical subtotals of the same field.

This limitation leaves many typical tasks unresolved, including ones that are frequently requested by the users and constitute a basis for any serious data analysis. Programming solutions that I was able to find so far – including support responses from Business Objects support – were either partial, or limited.

Some of these poorly or not supported typical tasks include:

• Reporting on semi-additive aggregated functions. As an example we can take inventory count field: inventory should be summed among products shipped in cases of different sizes, but not along time dimension – only the latest inventory count should be shown. I opened with Crystal Reports support case #302711440 about this inventory count issues – and the request was eventually closed/cancelled because “the feature … will not be available with the Crosstab. This is by the design of the Crystal Reports”. Well, my users need it.

• Functions linking fields displayed on cross-tab. An example of such functions can be growth percent between time periods, where time periods are shown as columns for reports with multiple pages.  

• Functions linking fields present in tables, but not included into cross-tabs. For example, users often need to analyze various forms of market penetration percentages, based, for example, on ratio of distinct count of stores to the total number of stores in the region.

First, let’s review some of the available ideas and check those limitations. Then, I will suggest a methodic resolving many of the discussed issues.

The methodic relies on programming done in both Crystal Reports and in the reporting application, with Visual Basic.Net and T-SQL syntax utilized.