Crosstabs and %'s 1

[deeya]

Crystal 10 SQL Server,

When im trying calculate percentages in crosstabs for some reason I cannot get the % correct. Whats the best way to handle %'s in crosstabs?

I.e i have a formula that calculates the percentage correctly in a normal report but when adding the formula to a crosstab it summarises the figure?

 

[lbass]

Please provide the specifics of your crosstab (row, column, summary) and explain what you are trying to do. Your question as is is too general.

-LB

 

[deeya]

Total Calls Within SLA %Within SLA
ADMIN 39 16 41%
APPSUPP 275 235 87%
COMMS 28 13 50%
etc

%Within SLA =
(DistinctCount ({CALL_HDW.CALLID_HDW}, {CALL_HDW.CALLID_HDW}) - Sum ({@Breached}, {CALL_HDW.CALLID_HDW}))
%
(DistinctCount ({CALL_HDW.CALLID_HDW}, {CALL_HDW.CALLID_HDW}))

for example COMMS should be 53%. Also how do you summarise the '%Within SLA' formula within a crosstab.

Thanks in advance.

 

[lbass]

Please tell us the names of your row field, your column field (None?), and your summary fields. If they are formulas, please show the contents.

Also, how did you get the %'s that are displayed?

You say that COMMS % should be 50%, but it looks to me like it should be 46% if "Within SLA" is in fact 13.

-LB

 

[deeya]

ok,

row = CALL_HDW.CLERGRP

columns
Total Calls = DistinctCount of CALL_HDW.CALLID_HDW
Within SLA = If {CLTRCPT_HDW.CMPLACT_HDW} = "CMP" and
{CLTRCPT_HDW.STATUS_HDW}<>"BREACHED" then 1 else 0
%Within SLA =
(DistinctCount ({CALL_HDW.CALLID_HDW},{CALL_HDW.CALLID_HDW}) - Sum ({@Breached}, {CALL_HDW.CALLID_HDW}))
%
(DistinctCount ({CALL_HDW.CALLID_HDW}, {CALL_HDW.CALLID_HDW}))

 

[lbass]

I don't really know what you're doing, as you didn't show the content of {@Breached} and you didn't explain whether what you really want is 16/39, 13/28, etc., but let's assume that's the case.

Remove your % formula, and instead create the following formula:

whilereadingrecords;
0

Add this as your third summary to use as a holder. Then select the total field->format field->common->suppress->x+2 and enter:

whileprintingrecords;
numbervar tot := currentfieldvalue;
false

Then select the {@withinSLA} summary->format field->common->suppress->x+2 and enter:

whileprintingrecords;
numbervar winSLA := currentfieldvalue;
false

Then select {@0}->format field->common->DISPLAY STRING and enter:

whileprintingrecords;
numbervar winSLA;
numbervar tot;
totext(winSLA%tot,2)+"%" //2 is for two decimals.

-LB

 

[deeya]

LB that works perfect for the total figure, thanks!

but what about the rest of the columns against seperate SLA's

 

[deeya]

sorry should have detailed this earlier:
SLA's >>>>>>>>>>>>>>>>>>>>>>
1hr SLA - 4hr SLA
Total Calls Within SLA %Within SLA - Total Calls W
ADMIN 39 16 41% - 0
APPSUPP 275 235 87% - 6
COMMS 28 13 50% - 2
etc

 

[deeya]

Hi Lbass,

Dont worry ive fixed the issue by applying the formulas and also put in a clause to handle 0's.

Thanks
again.