Report Design: percentage chart

percentage chart

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=6539
Printed Date: 04 May 2024 at 3:12am

Topic: percentage chart

Posted By: jknockler
Subject: percentage chart
Date Posted: 02 Jun 2009 at 4:37am

Hello,

I'm hoping someone can help me with this problem. It's regarding displaying percentage values on a bar chart.

As my report is setup now,

I have a database field called {yesno} . This field can either be 'yes' or 'no' (obviously haha)

In my stacked bar chart I have in the 'onchangeof' box, "all records".

In the 'show values' box, I have placed 2 formulas:

(1). If {yesno} = 'yes' then 1

(2). If {yesno} = 'no' then 1

Lets say the resultant chart displays a count of 87 for formula1 and a count of 63 for formula2.

My problem is that I want these figures expressed as percentages of the overall total (87+63 = 150)

So one part of the stacked bar will have 58% (87 count) and the other part will have 42% (63 count).

I tried creating running for the counts and then using them in a formula such as:

({#rt1} / {#rttotal}) * 100

and

({#rt2} / {#rttotal}) * 100

but this does not allow me to place these formulas in the chart expert.

Can anyone please help me to get around this problem(?)

Thank you.

-------------
J

Replies:

Posted By: hilfy
Date Posted: 02 Jun 2009 at 8:30am

Have you tried using the Sum and Count functions instead of running totals? Also, the '\' operator does percentages for you. So, you might be able to do something like this (for your Yes values):

If Count({record key field}) > 0 then

sum( mailto:%7b@YesFormula - {@YesFormula }) \ Count({record key field})

The Count get's your total number of records. Also, when dividing, a best practice is to always make sure that the number you're dividing by is not zero - this will prevent "divide by zero" errors that will crash your report if there's no data.

-Dell

-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics

Posted By: DBlank
Date Posted: 02 Jun 2009 at 10:11am

Do you have to use a bar chart here or can you use a pie chart. You can easily dispaly the percentages without any data manipulation...

If you must use a bar chart, Hilfy's suggestion of using formulas for "SUMS \ Count all records" rather then using a RT would be the next best bet.

Posted By: jknockler
Date Posted: 03 Jun 2009 at 2:14am

Hi DBlank,

In this case, I must use a bar chart. As it is an existing report that needs to be modified.

-------------
J

Posted By: DBlank
Date Posted: 03 Jun 2009 at 7:20am

OK. Here is a process...

You already have 2 formulas (@Yes and @No giving you 1 nad 0 resepectively).

Create a new formula called "Yes pct" (or whatever as it will appear in your chart...):

if Count({table.yesno})=0 then 0 else SUM( mailto:%7b@yes - {@yes }) % Count({table.yesno})

Create a new formula called "No pct" (or whatever as it will appear in your chart...):

if Count({table.yesno})=0 then 0 else SUM( mailto:%7b@no - {@no }) % Count({table.yesno})

In your chart expert:

select bar chart

Select percent bar chart (although a stacked will also work if you change the summary function)

For Data tab, Select for all records, for show values select mailto:%7b@yes - {@yes pct} and mailto:%7b@no - {@no pct} fields and (change them to MAX instead of SUM if using a stacked bar rather than a percentage bar chart).

That should do it.