Cross-tab % of Total for Units Used 1

[LT2]

Hello,

Using CRXIr2 w/SQL Svr. I had a similar thread posted, (thread767-1468549)that dealt with a count for a percentage but I'm unsure how to apply to this situation.

What I want is the percentage of units used just like a pivot table.

Cross-tab: Treatment Codes Authorized and Used by Agency
Row#1 Agency
Row#2 Treatment Code
Sum#1 Sum of Command.Authorized
Sum#2 Sum of Command.UnitsUsed
?#3 % of UnitsUsed

So for each Treatment Code and Summary Totals, the % of Units Used would be determined by UnitsUsed/Authorized.

Example:
[tt]
ABC GroupTherapy 10
ABC GroupTherapy 5
ABC GroupTherapy 50%
ABC Individ 4
ABC Individ 3
ABC Individ 75%
TotalAuth'd 14
TotalUsed 8
%Used 57%
[/tt]

I was trying to use variation of @%Used
(((Sum({Command.UnitsUsed})/Sum({Command.Authorized}))*100)) which I used for my group summary but summing a sum isn't correct.

Your time is appreciated.

 

[Madawc]

I doubt a crosstab can do anything this fancy. It would be easy enough using grouping and totals, maybe in a subreport in a report footer. Group and suppress detail lines. Have group summary totals for all 'GroupTherapy' and then for the particular type of 'GroupTherapy'. Use a formula field to find the lower-order group as a percentage of the higher.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[IanWaterman]

If you are using a command you may be better off summarising data in your command. Then it would be easy to do your %age calc as no need to use a Crosstab.

Ian

 

[lbass]

In preview mode, select the inner cell and the column total for the authorized field->right click->format field->suppress->x+2 and enter:

whileprintingrecords;
numbervar auth := currentfieldvalue;
false

Then select the units used summary in both inner cell and column total->right click->format field->suppress->x+2 and enter:

whileprintingrecords;
numbervar used := currentfieldvalue;
false

Create a formula->{@0} for your third summary field, which will act as a holder for the percentage:

whilereadingrecords;
0

Add this as your third summary, and in preview mode->select inner cell and column total and right click->format field->DISPLAY STRING->x+2 and enter:

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

-LB

 

[LT2]

Thank you both, Madawc & IanWaterman for your input.

LBass, you ROCK!

Please explain why this has to be done in Preview Mode rather than Design Mode.

Thanks,
LT

 

[lbass]

It can be done in design mode, too. I just say that ordinarily as a way to clarify that I mean in the main report, not within the crosstab expert.

-LB

 

[LT2]

Excellent. Again thank you very much for your time and expertise.
LT