Charting column summary values in a pie chart

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.

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.

But Pie chart does not allow having multiple show values.

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.

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

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.

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

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

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”

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

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

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.

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,

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

Definition of pie chart is as below,

Preview the main report,