Pie Charts and Grand Totals

[Former Member]

I'm sure if I didn't have this huge headache, and the immense desire to leave for the weekend, I could search myself for the answer.

But posting a fresh question is much easier.

First off, I'm using Crystal Repots XI

I have a report where I need to show a pie chart wherein the sections show values representing sums from counting records based on the values of certain fields.

Let me explain...

I have a field called "Progress".  I have several formulas that return 1 to 0 based on the contents of the field.  For instance, I have a formula object called "Sales In Transit" which has the code like this:

Dim nRetVal As Number

If {MYTABLE.Progress} = "In Transit" Then

    nRetVal = 1

Else

    nRetVal = 0

End If

formula = nRetVal

This is repeated for a few others such as "Ordered", "In Picking", "Delivered" etc.

In the Report Footer, I have Sums of each of these, and the values are correct.  Now I want a pie chart (also in the Report Footer) that shows a segment for each of the "Progress" values.

In the Chart Expert, I tried putting the field objects in the "Show Objects" section of the Data tab (and selected For All Records), but that doesn't work.

How do I do this?

TIA

[former_member260594 · Accepted solution]

Hello Clifton,

What if you just inserted a pie chart and set the onchange of field to Progress and the show value field to count of progress?

If you are trying to limit the number of values that will appear in the chart then you could create a formula, if {progress} = 'in transit' then 'in transit' else .....

so that you only include the ones that you want and use the formula field in the chart.

Regards,

Graham

[Former Member]

Graham Cunningham,

That was what I needed.

Abhilash Kumar,

I'm fairly certain that your suggestion would have worked, but Graham Cunningham's suggestion was much simpler and worked perfectly.

Thanks.

[abhilash_kumar]

Hi Clifton,

I see what you're trying to do here, however, you cannot use the summary you've created in the pie chart. There are always workarounds with Crystal Reports though!

Here's what you need to do:

1) Create a formula (@str) in the Main Report that basically 'stitches' all the Progress' totals together using a character. I see that you have 4 Progress values - "In Transit", "Ordered", "In Picking" and "Delivered".

I also assume you have 4 separate formulas that calculate the summaries. So, here's the formula you need to create and place this on the Report Footer beside the other sums towards the end:

WhilePrintingRecords;
stringvar s;
s := totext(Sum ({@In Transit}),0) + ">" + totext(Sum ({@Ordered}),0) + ">" + totext(Sum ({@In Picking}),0) + ">" + totext(Sum ({@Delivered}),0)

This formula will concatenate the sums with the ">" character and create a string similar to this:

12>13>16>18.

2) Next, create a Report Footer b section and insert a Subreport with the same data-source in this section. Please note that the number of records in the Subreport should be same as the numbers of the records in the Main Report. For this, you need to make sure the same record selection formula is also applied to the Surbeport.

3) Once this is sorted out, go back to the Main Report and right-click the Subreport > Select 'Change Subreport links' > Choose the formula field we created above and move it to the 'Fields to link to' area. Then uncheck the option 'Select data in Subreport based on field'.

4) Go back to the Subreport and create a group on the 'Progress' field. Make sure the Subreport only shows 4 groups or at least the same number of Progresses.

5) Create this formula (@Gtot) which displays the amounts for each segments:

tonumber(split({?Pm-@str},">")[GroupNumber])

6) Place this formula on the Group Header. You may choose to suppress all the sections except the Report Footer.

7) On the Report Footer, insert the chart. Choose the 'Progress' field under 'On Change of' and the @Gtot formula under 'Show Values'. Also make sure 'Don't Summarize' option for 'Show Values' is Checked.

😎 By default, the pie chart shows percentages. To Show the actual amounts instead, go Chart Expert > Options tab > Look at the bottom for the option 'Layout'. Choose 'Amount' from the drop-down.

I've attached a sample report for your reference. Please follow the instructions and have a look at the report too just in case you don't follow the instructions.

The extension for the report has been changed to .txt. After extracting the zip file, please change the extension to .rpt.

Let me know how this goes!

-Abhilash

Pie.txt.zip