Ever come across a situation where the data is returned by query or table in such a way that does not fulfill the requirements of reporting component, like cross-tab or chart? Recently, I had such requirement where I have a query that returns five columns that contain values for five cost centers and to summarize the columns to get cost center totals.

/wp-content/uploads/2013/08/img_1_267267.png

The report was required to show these summarized values in a pie chart, each cost center column summary as a slice of the pie chart.

/wp-content/uploads/2013/08/img_2_267268.png

But Pie chart does not allow having multiple show values.

/wp-content/uploads/2013/08/img_3_267269.png

What I need to do is to show my cost center as individual group and respective sums as group totals, before I could chart the values in a pie. How do we do that?

Sample Data and Set up

I have created a sample data set in excel, on which my test report will be based. As you can see my sample data is represented in sheet called Data.

/wp-content/uploads/2013/08/img_4_267270.png

I created another sheet called Group, in which my cost centers are in rows, not in column.

/wp-content/uploads/2013/08/img_5_267271.png

In real scenario, the Group sheet could be a table that is representing column in rows.  And Data sheet is the original query/table.

Report Design

Create a report off sheet Data, bringing all the columns and summarizing them.

/wp-content/uploads/2013/08/img_6_267272.png

Insert a sub-report into report footer of this report.

/wp-content/uploads/2013/08/img_7_267273.png

This sub-report is based on Group sheet and create group on its column (there is only one column in my sample here).

/wp-content/uploads/2013/08/img_8_267274.png

In Sub-report design tab, suppress Group Header, Details and Group Footer section.

Go back to main report design tab and create sub-report link. Right click on the sub-report and choose “Change Subreport Links”

/wp-content/uploads/2013/08/img_9_267275.png

Select all the summaries that were created on each cost center column in main report, as field  to link on

/wp-content/uploads/2013/08/img_10_267276.png

Make sure to uncheck “Select Data in subreport based on field” option for each of the field in “Field(s) to link to:” box,

/wp-content/uploads/2013/08/img_11_267277.png

The above steps will create parameters on the sub-report with values coming from summary values from main report, without matching them to any object in sub-report.

/wp-content/uploads/2013/08/img_12_267278.png

Next, in Sub-report design tab, create a formula, I called it “show_GTOTAL”, and place it in Group Footer. I have following code in this formula,

/wp-content/uploads/2013/08/img_13_267279.png

Next, insert a pie chart in report footer of the sub-report,

/wp-content/uploads/2013/08/img_14_267280.png

Definition of pie chart is as below,

/wp-content/uploads/2013/08/img_15_267281.png

Preview the main report,

/wp-content/uploads/2013/08/img_16_267282.png

To report this post you need to login first.

2 Comments

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

  1. Prathamesh Acharya

    I liked the topic and the presentation is very nice.Thanks for sharing.

    There is a separate topic on ‘charting on print-time formulas’ that guides the user on creating charts based off summary values of groups, using sub reports.This will eliminate the intermediate step of converting column to row that was done in the spreadsheet. So, thanks to Crystal Reports tool for such a possibility.

    -Prathamesh

    (0) 

Leave a Reply