Crosstab Totals 1

[briangriffin]

CR 2008

I have a crosstab in the report footer with various location names down the vertical axis and some numeric values down each column (each column is a month).
Each cell has two summed values and one percentage calculation based on those values.

I don't have the "don't summarize" option for the percentage formula, so I made it a max. Since each row only has one record, the result is the same.

I've suppressed the totals at the bottom, but the vertical totals were displaying the max value for the row. I changed the display string for the total field to a calculation (sum%count).

This works fine for the grand total, but each row also displays the grand total instead of the total for that row only.

I've played with the display string formula, but even changing it to just the location name gives the value of the last record (it's not the max value since I can change the sorting of the groups and the value changes). That means I can't use sum(value,location name) since the location name is always the same.

I get why the crosstab won't handle this automatically, but I don't get why the display string formula won't work.

Thanks in advance for your help.

 

[lbass]

Please identify the column, row, and summary fields, and show the exact formulas you are using. If you are already using the formatting formula areas to do some of these calculations, please show the content of those formulas, too. I'm also unclear on whether you are having trouble with all of the row total summaries or just the percentage.

-LB

 

[briangriffin]

Jan Feb Total
All Cases On Time % All Cases On Time % All Cases On Time %
Room 1 10 5 50% 18 6 33% 28 11 42%

Room 2 15 5 33% 22 11 50% 37 16 42%




Row = Room
Columns = Month

Summarized Fields:
All cases = count(caseID)
On Time = sum(formula that returns 1 or 0)
% = Max of formula that calculates % at group (month) level


All numbers are correct except the 42% - this is the correct figure for the report total, but not for the individual row. If there were additional rows the percent total would be 42% for all of them.

It seems that the % formula should be a the room level, but when I change it the total % becomes the correct figure but each monthly total then becomes the total for the room (all percentages are the same across each row for both month and total). I need it to be broken down by both room and month.

 

[lbass]

Please show the actual content of both formulas you are using for the summaries, instead of describing them.

-LB

 

[briangriffin]

All Cases: count of database field caseID, count is performed in the CT summarized fields list.



On Time Cases: sum of this formula (@Started on Time):

if {Command.delayminutes} > 0 then 0 else 1



% is max of this formula (@CT On Time %):

Sum ({@Started on Time}, {Command.wholeday}, "monthly") % Count ({Command.casemain_id}, {Command.wholeday}, "monthly")

I have to select a summary function, which is why it's currently a max.

Thanks for your help.

 

[lbass]

The simplest solution would be to select (in preview mode) the total column->all cases summary->right click->format field->suppress->x+2 and enter:

whileprintingrecords;
numbervar allcases := currentfieldvalue;
false

Then select the total ontime summary->right click->format field->suppress->x+2 and enter:

whileprintingrecords;
numbervar ontime := currentfieldvalue;
false

Then select the total percentage summary->right click->format field->DISPLAY STRING->x+2 and enter:

whileprintingrecords;
numbervar allcases;
numbervar ontime;
totext(ontime % allcases, 0,"")+"%"

-LB

 

[briangriffin]

I like simple solutions, and this one worked perfectly. Thanks again.