Help with the percentage in Crosstab 2

[CrysRptDev]

I'm using CR XI.

Right now I am having a problem with calculating percentage in Crosstab. I wrote a query with all data needed for Crosstab. Below is an example when I put in Crosstab.

New Connects Upgrades
Red Scheduled 11 10
Completed 5 4

Blue Scheduled 20 30
Completed 10 20


So I want to insert a percentage under completed section and this percentage calculated from (completed / scheduled). Below is an example of the result I want for this report.


New Connects Upgrades
Red Scheduled 10 10
Completed 5 4
Percentage 50% 20%

Blue Scheduled 20 30
Completed 10 20
Percentage 50% 66.7%


Does anyone know how to calculate for the percentage above?

Thanks,
AN

 

[lbass]

In preview mode, select the schedule summary (if you are using totals, select the schedule total, also)->right click->format field->common tab->suppress->x+2 and enter:

whileprintingrecords;
numbervar sched := currentfieldvalue;
false

Then select the completed summary->right click->format field->common tab->suppress->x+2 and enter:

whileprintingrecords;
numbervar compl := currentfieldvalue;
false

Next add a third summary (this could be anything--or add one of the earlier summaries again--since it will just be a holder for the result of the variable). Then right click on this new summary->format field->common tab->DISPLAY STRING->x+2 and enter:

whileprintingrecords;
numbervar sched;
numbervar compl;
if sched = 0 then
"0%" else
totext(compl%sched,2)+"%"//2 for two decimals

-LB

 

[CrysRptDev]

Thanks lbass,

Yes it works, however one more thing, I forgot to mention that there is a row total on right. How to calculate percentage for this total row. Like example below.


New Connects Upgrades Total
Red Scheduled 10 10 20
Completed 5 4 9
Percentage 50% 20% 45%

Blue Scheduled 20 30 50
Completed 10 20 30
Percentage 50% 66.7% 60%


Thanks so much lbass!

Cheers.

 

[lbass]

As I mentioned, you could have also selected the corresponding summaries in the row totals when you added the formulas the first time. However, now you can simply copy/recreate the same formulas in the row total summaries.

-LB

 

[CrysRptDev]

I used the same logic and I got it. Now I can relax. Thanks!

 

[CrysRptDev]

lbass,

I did exactly what you suggested but I found it weird that I didn't see 0% whenever completed = 0 as your formula says. It just has blank fields.

whileprintingrecords;
numbervar sched;
numbervar compl;
if sched = 0 then
"0%" else
totext(compl%sched,2)+"%"//2 for two decimals

 

[CrysRptDev]

I meant i already changed to

whileprintingrecords;
numbervar sched;
numbervar compl;
if compl = 0 then
"0%" else
totext(compl%sched,2)+"%"//2 for two decimals

Still blank fields whenever compl = 0

 

[CrysRptDev]

NVM, got it!

 

[lbass]

You should be checking whether sched (the denominator) is 0, not compl.

-LB

 

[devnaren]

Hi All,

Is this possible to show the same values on the Chart.

 

[trenttc]

The Cross tab column is date and row is category. The summarized fields are A sum, B sum, and A+B sum. I want the summarized A sum and B sum to show a percentage of A+B sum, not show a percentage of all A sum or B sum. CR 11
Date 1 Date 2
Category 1 A sum% A sum%
B sum% B sum%
A+B sum A+B sum
Category 2