Percentages In Cross-Tab Table

[CIBS]

Hi

I'm Using CR9

I am trying to create a cross-tab table that consists of staff performances using figures and percentages.

Grouping in Reporting

Grouping 1 - @Staff
Grouping 2 - Call.Call_Number

I have a formulas call "Call_Status" & "Staff"

Formula - "Call_Status"

IF {THREAD_EVENT.THREAD_EVENT_TYPE} = 8.00
THEN "Closed Complete"
ELSE
IF {THREAD_EVENT.THREAD_EVENT_TYPE} = 9.00
THEN "Closed Incomplete"
ELSE
""

Formula - "Staff"

{PERSON.PERSON_FAMILY_NAME}& " "&{PERSON.PERSON_OTHER_NAMES}

in my cross table I have

Columns - @Call_Status
Rows - @Staff
Summarised -

Distinctcount of Call.Call_Number
Percentage Count of @Call_Status

an example of what I am currently getting is

Closed Complete Closed Incomplete Total
Jake Stevens 26 86% 4 13% 30 100%

I have managed to hide the total percentage but the 2 columns only ever equal 99%. they need to equal 100%

Thanks for any help

Stephen

 

[lbass]

Create a formula {@totcnt}:

distinctcount({call.call_number},{@call_status})

Add this as your final summary (first remove your earlier percentage), and choose maximum as the type of summary. Then in preview mode, select the inner cell (and column summary field if you wish) for your distinctcount summary->right click->format field->suppress->x+2 and enter:

whileprintingrecords;
numbervar curr := currentfieldvalue;
false

Then select the maximum of {@totcnt} in the inner and summary cells->right click->format field->suppress->x+2 and enter:

whileprintingrecords;
numbervar tot := currentfieldvalue;
false

Then while in the same common tab->display string->x+2 and enter:

whileprintingrecords;
numbervar tot;
numbervar curr;
if tot > 0 then
totext(curr%tot,1)+'%'//1 for one decimal

I suggest using at least one decimal, to get closer to the display of 100%. There still could be cases where because of decimals and rounding, the sums don't quite make it to 100%, e.g., compare the following:

54 33% 61 37% 48 29% 163 100%

54 33.1% 61 37.4% 48 29.4% 163 100.0%

54 33.13% 61 37.42% 48 29.45% 163 100.0%

Notice that all are accurately rounded, but only the last display totals 100%. Most viewers will understand this.

There IS a problem with crosstab percentages not totalling 100% in CR. They seem to be based on truncated values, instead of rounded values. The technique above gets around that problem.

-LB