Cross Tab % Calculation Question 1

[Sange]

Hi am using Crystal Reports v10 and have developed a cross tab based on the number of days it has taken for an assessment to be completed by gender.

Each Assessment is allocated a unique number and as a result, the summarised field is made up of distinct count of this unique number.

The results need to be at one decimal place and the requestor has asked the % be calculated in a specific way which I thought would be easier to explain by demonstrating rather than a long winded explanation:

The actual cross tab count looks like like this:

No. of Days Required to Submit
<=7 Days 8-14 Days 15-21 Days >=22 Days Total
Female 3 1 0 23 27

Male 7 0 5 148 160

Total 10 1 5 171 187


The requestor would like the % to display as follows:

No. of Days Required to Submit
<=7 Days 8-14 Days 15-21 Days >=22 Days Total
Female 11.1% 3.7% 0% 85.2% 14.4%

Male 4.4% 0% 3.1% 92.5% 85.6%

Total 5.4% 0.5% 2.7% 91.4% 100%

In addition, the requestor wanted the days to submit heading to appear specifically within the cross tab and the only way I could think to do this was to create a formula that contained the text "No. of Days Required to Submit" and then add this as the first column in the cross tab.

What I'd like to know is whether these calculations with regard to the % doable and if so, how do I go about doing this?

My apologies in advance if columns don't line up after I submit this.

Thanks.

 

[lbass]

You could just use row percentages, but there is a glitch where they don't total quite 100%, so your best bet is to make sure you have a group on gender in the main report, and then create two formulas:

//{@grtot}:
count({table.assessID})

//{@grpcnt}:
count({table.assessID},{table.gender})

Add these as your first two summary fields. Then add the count of {table.assessID} as your third summary.

Select the {@grtot} summary in an inner cell and for the column totals (along the bottom)->format field->suppress. Then select it for the row total->right click->format field->suppress->x+2 and enter:

whileprintingrecords;
numbervar grtot := currentfieldvalue;
true

Next select the inner cell AND the column total at the bottom for {@grptot} ->format field->suppress->x+2 and enter:

whileprintingrecords;
numbervar tot := currentfieldvalue;
true

Suppress {@grptot} in the row total column.

Then select the inner cell AND column total AND row total for count of {table.assessID}->format field->suppress->x+2 and enter:

whileprintingrecords;
numbervar curr := currentfieldvalue;
false

Then with the inner cells and column totals selected->right click->format field->DISPLAY STRING->x+2 and enter:

whileprintingrecords;
numbervar curr;
numbervar tot;
if tot = 0 then
"0.0%" else
totext(curr%tot,1)+"%"

Then select the row total for count of {table.assessID}->format field->DISPLAY STRING->x+2 and enter:

whileprintingrecords;
numbervar curr;
numbervar grtot;
if grtot = 0 then
"0.0%" else
totext(curr%grtot,1)+"%"

You can minimize the height of the suppressed summaries in design mode.\

For the grand total percentage, you can do a calculation along the former lines, or you can just use display string to show "100%".

-LB

 

[Sange]

Hi LB, you have been extremely helpful once again. Have a vague idea of how this works but would have never worked it out myself so thank you very much.

 

[Sange]

Hi LBass, I'm hoping you won't mind me asking you another question about this cross tab.

I have managed to get my row percentages working based on your suggestion but the column totals percentages are not calculating as I would hope.

Ideally I would like to keep the row % as is but the column total % to be based on the overall total. So if the total number of assessments submitted is 136 and a column total is 31 then the column total % would 22.8%

The percent is actually coming up as 25.6% which appears to be based on the total number of male assessments which is 121.

Do you know if whether I am asking is possible and if so, what on earth am I doing wrong???

Thank you in advance.

 

[lbass]

Make sure you are using the {@grtot} summary when setting the variable "grtot". The "curr" variable should be set on the column total, which you will also use for the display string formula which should be:

whileprintingrecords;
numbervar curr;
numbervar grtot;
if grtot = 0 then"0.0%" else
totext(curr%grtot,1)+"%

My apologies--I think I misdirected you in this step in the previous post.

-LB

 

[Sange]

LBass you have come through me yet again. The report is perfect. Thank you so much.