Creating Dynamic Legends for Pie Charts with Crystal 9 1

[Oaion]

Good Afternoon,

I'm trying to create a pie chart that creates legends dymanically depending on the output of a formula. For instance, I have a report that has a list of body parts that may have been injured at a place of work. Each record for the body part contains the following:

1. code for the name of the part injured
2. description for the part injured
3. insurance claim number
4. amount of the claim

Keep in mind that there are about 50 body parts. I have separated the body parts into groups by description. I do not want all of these parts to show on the pie chart or within the legend. The only body parts injured I want on the chart are those that meet the following criteria:

sum({amount_of_claim},{body_part_description})/
sum({amount_of_claim}) * 100 > 5

In other words, I want the chart to show only the body parts whose grouped total claim amounts is 5 percent or more of all the claims in the report.

i.e if the total amount of claims for thefingers group is 32% of the total for All claims, it shows on the report. If the total amout of claims for the knee group is 4%, it does not show.

Please Help!!!!!!!!!!!!!!

Thanks

 

[lbass]

I am using 8.0, but the following works:

Create a formula like {@grthan5}:

if sum({amount_of_claim},{body_part_description})>=
5/100 * sum({amount_of_claim}) then
{body_part_description} else ""

Then in the chart expert, choose the advanced layout for the pie chart. Use {@grthan5} as the "on change of" field, and percentage of sum of claims as the summary field.

Select {@grthan5} and choose "Specified groups" and enter all visible results in the dropdown list (but not the blank), and then choose the "Others" tab and choose discard all others.

When I did this, I found that if I changed the criteria to allow more groups in the pie chart, only the groups I specified would appear, although if I tightened the criteria, only those groups that met the stricter criteria appeared. So I first loosened the criteria as much as possible (to 1/100) so that all possible groups were represented, and then entered all but the blank into the specified order from the dropdown list. Then I could change criteria to tighten or loosen them and the appropriate groups would appear. So I recommend that you take this step first. Of course, if the list of body parts changes, the specified order list would have to be updated.

-LB

 

[Oaion]

It worked perfectly. THANKS!!!!!!!!!!!! You're a time and life saver

 

[Oaion]

LBass,

I'm at a stand still with my 1st chart. I have an issue. If anyone can answer please respond.

The forumla from October 10 helped me out a lot, however, now my boss wants to see only the 1st 10 part decriptions over 5%. I've tried to use the Top N Expert, however, when I use the Top N Expert, the legend shows 2 labels for the values descriptions that don't meet the 1st 10 over 5% critera. One value, "Others" comes from the Top N expert and the "" on the legend comes from the formula listed above. My question is, how can I combine these 2 values, for a total "All Others" to show in the legend and the graph. I was thinking that I may have to add a counter in the orignal formula that counted out the 1st 10 groups over 5%. I'm not sure how to do this.

 

[lbass]

When you used my suggestion above and chose "specified groups", you should have then chosen the "Others" tab and checked "Discard all others"--this would eliminate the "" group on the pie chart.

Then to get the top10 in the pie chart, go to the chart expert and again select {@grthan5} (the "on change of" field) and choose topN, N=10, and uncheck "Include Others with the name"--this should result in only the relevant groups appearing in the legend. If there are less than 10 groups that meet the criteria, only those groups will appear in the chart.

This worked when I tested it.

-LB

 

[Oaion]

LBass

Thanks for responding. Yes, you are correct. This does work if you're not wanting to see the "Other" items that don't meet the criteria. I need the "Other" criteria to show. That's where I'm having the problem. Let me give you an example. Before I implement the TOPN, the total Label in the legend shows $336,011. This includes the "other" of $15,551. If I implement what you have stated above, the legend shows a total of $320,460 because the Others were disgarded. I don't want the "Others" to be disguarded. I want it to show in the legend and pie graph.

Thanks for your help. I hope I've expalined it well.

 

[lbass]

Sorry, I didn't read your post carefully enough. If you are using specified order (you don't need to if you are including all records in the chart), you should name the records that don't fall into your primary groupings "Others", and use the same name for non-topN records when you use topN. If you are not using specified order, in the formula, change the final "" to "Others".

If the "Others" category is large, it might end up as one of the topN, in which case you will need to add 1 when you designate the topN value in order to get the display of topN which reflects the "non-Others" values in the initial formula. I'm not sure how to correct for that dynamically, but maybe that won't be an issue for you anyway.

I think that should do it, but let me know.

-LB