Home > Messages from the Ether > Crystal Reports: displaying a graph of the most recent data

Crystal Reports: displaying a graph of the most recent data

I had to create a report in Crystal Reports--containing graphs of historical data within subgroups of users--that ended up being more complex that I had expected. Much of the information comes from the Crystal Reports Knowledge Base article C2011945 with little else available on the Web. Here are some notes using CR10:

Although the data was based on several joined tables, the basic problem can be simplified to a hypothetical table containing UserID, DataDate, DataName, and DataValue. The report needs to group by UserIDs and then to graph DataValues for the most recent 10 DataDates for each UserID. Ideally, the report will show each UserID's recent value trend, reading from left to right.

1. Create a report and group by UserID,
2. Select Report > Record Sort Expert... and sort by DataDate ascending,
3. Select Insert > Chart...
4. On the Type tab, select Chart type > "Line,"
5. On the Data tab, select Placement > "For each UserID." This show one chart per UserID,
6. Select Layout > Advanced,
7. Move the DataName field to the "On change of" list. This is the source of our X axis values,
8. Move the DataDate and DataValue fields to the "Show value(s)" list. These are the source of our Y axis values,
9. Click the Order button and specify "in original order." This allows us to take the report order (specified above), but display the DataName values for the X axis,
10. Click the TopN... button and sort using a Top N of 10 based on the DataDate field. The DataDate field must be included in the chart to use the TopN feature

The interesting aspect is that the sorting on DataDate must be specified in the report so that we can use the values of DataName for the X axis.

The report is configured at this point but contains two unpleasant problems. First, the DataDate values are displayed on the chart even though we only want the DataValue values. DataDate must be included in order to find the Top N. The Knowledge Base article gets around this by creating a report with only one chart and hiding the date plot points. This is achieved from a context menu item found in the preview mode. Because my report contains a variable number of dynamic charts, I can't hide the values using that technique. Instead, I took advantage of the fact that the data ranges for DataDate and DataValue are very different. I limited the chart's range to cover only the range of relevant values. This left an ugly but acceptable spurious chart line across the top border.

The second problem is that the plotted values are in reverse order across the X axis. Instead of plotting the most current date to the right, it plots it to the left. I tried many combinations of sorting (descending + Bottom N) without success.

I may-or-may-not have hit the limits of the Crystal Reports charts, but at least the results were "good enough" (never a phrase that instills pride) to ship.