Cricket fans will be familiar with the wagon wheel chart. This blog post will give you the steps on how to create this type of chart in Crystal Reports. The wagon wheel chart will be a Radar type chart formatted specifically to give you an output similar to below. And for those of you who have been following Tom Flanagan’s blog posts on 2014 cricket here you may have seen this particular report being used.

Untitled.jpg

The report shown in the screenshot is attached to this blog. Please extract the contents of the attachment and change the .txt extension to .rpt.  Unfortunately there is no saved data with the report as the raw data is proprietary data and therefore I cannot publish a report with this raw data. However, the attached report should help you get a better idea as to how everything fits together.

The chart will rely on the data set that is returned in the report. Therefore, the first and most important step is to ensure that you have the correct data coming into your report so that the chart will represent 360 degrees worth of data. If you do not bring in data points for all 360 degrees, then your wagon wheel chart will look like a typical radar chart wherein the existing data points will produce a polygon shape vs. the ‘starburst’ format above.

Returning the Proper Data

The easiest way to ensure that all 360 degrees are represented in the data set returned is to create a dummy record set of 360 records. This can be done by basing your report off of a Command object as opposed to tables. Have a look at the sample Command syntax below which is written for an SAP HANA database. Note that your syntax may vary as Commands are written in your database specific syntax.

SELECT
“C50_COMBINED”.”id”,
“C50_COMBINED”.”field_direction”,
“C50_COMBINED”.”field_magnitude”,
“C50_COMBINED”.”batsman_runs”,
“C50_COMBINED”.”batsman_name”,
“C50_COMBINED”.”batsman_id”,
“C50_COMBINED”.”game_id”
FROM  
“CRICKET”.”C50_COMBINED” “C50_COMBINED”
WHERE
“C50_COMBINED”.”batsman_name” = ‘{?BatsmanName}’ AND
TO_DATE(“C50_COMBINED”.”timestamp”) = {?Date}

UNION ALL

SELECT TOP 360
“C50_COMBINED”.”id”,
ROW_NUMBER() OVER (ORDER BY “C50_COMBINED”.”id”),
229,
0,
”,
0,
0
FROM  
“CRICKET”.”C50_COMBINED” “C50_COMBINED”

The above syntax uses a UNION ALL of two queries. The first query is used to get the normal data for our report. Using the cricket example, the first query brings back records for a particular batsman on a particular date representing a match / game. The second query is used to bring back the dummy record set. It brings back 360 records where the ROW_NUMBER partition function is used to represent the individual degrees, 1 to 360, that will be used later in the chart for the On Change Of value. Note that these dummy records also use a value of 229 for the magnitude column. This 229 value is used in the cricket wagon wheel so that the chart scale will be based on a magnitude of 229. Note that the column for the batsman ID will have a value of 0 in the second / dummy record set.

For those not familiar with using a UNION or UNION ALL, it is important to note that the queries that you are unioning must have the same number of fields returned and the types must also match. Also note that the names of the fields will be taken from the first set of fields or the first query.

Creating Formulas for the Chart

When creating a chart in Crystal Reports, I often use formulas for the Show Value(s) and the On Change Of value(s). The reason for this is that you can easily change the formula syntax of the formulas used in the chart and you don’t need to edit the chart object itself or worry about the formatting / work that you’ve done being lost by making changes to the chart.

Since a wagon wheel chart shows several different series based on the Run Types, we will want to create several formulas, one for each run type. For example, for 1’s we create a formula with syntax like

if {Command.batsman_runs} = 1

then {Command.field_magnitude}

And then we create similar formulas for run types of 2’s & 3’s, 4’s, & 6’s.

We also need to create an additional formula so that the scale of the chart is constant.  This is an optional step, but using a formula with a constant value ensures that all of your reports with this chart will have a uniform Y axis scale. This scale formula will be the following syntax and will produce a single line in the chart with a magnitude of 226 when the direction is 90 degrees. Later on this line will be formatted so that it is not visible as it is only used to keep the scale of the chart constant.

if {Command.batsman_id} = 0

and {Command.field_direction} = 90

then {Command.field_magnitude}

Creating the Chart

Now that we have the proper data in the record set and the formulas created, we can create a chart which will have Radar selected as the Type. The On Change Of field will be the field that represents the direction or angle and we know that we will be bringing in 360 degrees worth of data to the report.

Now we need to add our formulas to the Show Values list. To simplify this step by step procedure, we’re only going to use the formula that we created for 1’s and the formula that we used to create the scale. Change the Summary Type from Sum to Maximum to ensure that the values in the chart are not multiplied. When we preview the chart it will look something like this.
Untitled.jpg

Formatting the Chart

If you right click on any of the gridlines emanating from the centroid of the chart, choose Format Gridlines and change the Color to Transparent.

Now right click on the chart and choose Chart Options > Gridlines tab and ensure that there are no gridline options checked.

Right click somewhere just outside of the circular border of the chart until you see a Format Chart Frame option and then go to the Border tab and change the Color to Transparent.

Now your chart should look something like this.

Untitled.jpg

If you now right click on one of the numbers on the Y axis and choose Data (Y) Axis Options > Format tab, you can uncheck the Show Axis Line & Show Axis Labels options.

Right click on the legend icon for the Show Value that you used as a scale, in my case the blue square in the legend to the left of “Max of @runs0”, and then choose Format Series Marker. Change the Foreground Color to Transparent. Right click on the legend icon for the other value and follow the same process if you wish to change the color for the second series. Note that in Radar type charts, the legend is used to format properties such as series colors. The legend can be hidden later after you’re done formatting your chart.

Right click on your chart and choose Chart Expert > Options tab and under the Layout section, de-select Auto-arrange. Having Auto-arrange selected in the Chart Expert when designing a chart can cause issues when you are trying to change things in the Chart Options dialogue.

Right click again on the chart and this time choose Chart Options and go to the Data Labels tab. Select the Show Data Labels option and for the Labels Location choose Center. Now go to the Appearance tab change the Shape to Circle and then the Size to something a lot larger. Note that if you did not make the change to the Auto-arrange as suggested in the step above, you will not be able to change the marker size at this point.

Once you’re done with the formatting of your chart you can right click on the chart and choose Chart Expert > Options and then de-select the Show Legend option. At this point you should have a chart that looks similar to below.

Untitled.jpg

Note that you will see the value 229 or your maximum magnitude value (used to properly scale the chart) at the top of the chart. This cannot be suppressed using any chart options without affecting the other labels. The best thing to do would be to cover it with a Text Object with a Background Color, or just leave it there to show the Y axis maximum scale value.

Now your wagon wheel chart should be fairly close to completion. In the case of the sample report, there would be several more Show Values to be added to the chart so that the other Run Types could be visualized on the same chart. Remember that you need to have the Legend shown in order to format the colors of the series in a Radar type chart.

Review

If you need to create a wagon wheel chart (or sometimes known as a starburst chart) then this blog post will hopefully get you to the final result. There is a bit of formatting to get the Radar chart to look like the final result but the most important step will be to ensure that you bring back a record set for each of the 360 degrees of the chart’s circle.

As mentioned earlier, the above Command object syntax uses SAP HANA SQL. If you are a Crystal Reports developer and are using HANA or just wish to find out more about HANA SQL etc. then please visit the SAP HANA Academy for lots of free ‘how to’ videos.

To report this post you need to login first.

2 Comments

You must be Logged on to comment or reply to a post.

    1. Jamie Wiseman Post author

      hi Divya,

      unfortunately i can’t save the report with data and post it for public download. the data is created by Opta and is not available for any kind of distribution by us for legal reasons.

      -jamie

      (0) 

Leave a Reply