How to Chart on Data that isn't there

This is the full explanation and solution of the blog entry on 18 October, 2010. This is the first solution page from the blog. Others will be added as they arise. The key to this solution is understanding that a Crystal Reports Chart can only be based on data that exists in the database.

The Report was grouped by Site, and the first chart was to show Grades of 1-5 for the customers of that site. The cause of the problem was that the first site was really good, so the main score was 1 (Excellent) with a couple of 2 (Very Goods) and no entries for 3 (Average), 4 (Fair) or 5 (Poor).

The Chart for this Site showed two bars. Similarly, a site that only scored a 3 and lots of 5's would show two bars and wouldn't present a fair comparison with our good region.

The good news is that there are only 5 grades, so 5 formulas and 5 charts are all we need to develop a solution. A 10 grade system would require 10 formulas and 10 charts.

Step 1: Formulas

Create 5 formulas. One for each grade. The Formula for Excellent looks like:
If {Table.Grade}=1 then 1 else 0

Similarly, the formula for Very Good looks for a {Table.Grade}=2

Step 2: Master Chart

Create your first chart to use the Excellent Formula.

The is an Advanced Layout Chart, On Change Of {Site Field} (to match the group in your report, and show value is the Sum of {@Excellent} formula.

Resize the chart so it's narrow and shows the bar the way you want, remembering that four more similar charts are about to be created.

Modify the text and labels to minimize the extra elements.

At this point we copied the chart and started modifying the chart for each formula. We then discovered a problem that meant we had to delete all these duplicates and do it all again.

Step 3: Rescaling

The problem was that all the charts had a single value and the chart would rescale to suit that value. The "Excellent" chart would scale from 1-100 and show a bar the same height as the "Very Good" chart which only went from 1-5.

Yes. We could manually scale the charts, but we don't want to get called in every time the volume of data changed. Also, each site could have widely different volumes so a global scale wouldn't be practical.

We need a second value on the chart. A Total Count of the Primary Key for that site will force all the charts to show the total records. An additional benefit of this approach is that each grade will appear at the correct proportion

Step 4: One Bar and an Invisible Line

Select the bar on the Chart that does the primary key count. You can then Right Click and select the menu Chart Option/Series.

Change the Series from Default to Line.

This will make your chart appear as a mix of bar and line, with the "Excellent" formula being the bar and the Count of the key being the Line.

Then select the line and Chart Options/Selected Item and make the item White, or the background color of the chart if it's not White. Transparent also works well.

Step 5: A Chart for each formula

You are now ready to copy the chart. Create 5 copies and position them side by side across the page. Modify each chart so it displays a different formula. Make sure the new formula is in the same position in the Expert as the first series will the the vertical bar, and the second series the invisible line

Change the color of the bar for each formula see varying results.

Remember to apply changes to all Charts, so every site will get 5 charts. We've also found the charts work better in the Group Footer rather than the Group Header section