Distinct Count Problem

[slickp007]

Crystal 11

I've created a report grouped by FSA type and have coloured the different rows in the report depending upon the time of the month. i would like to do a distinct count of how many blacks, blues and reds i have in each FSA group. However i have supressed in the section expert anything where {@Exp Rpt New} = "N", so i came up with the formula:

if {@Colour} = "Black" and {@Exp Rpt New} = "Y" then {RISK.RiskReference}

then i would do a distinct count on this formula to find how many policies are coloured black, however i get the error that this field cannot be summarised!! HELP!!

I tried to explain myself as best as possible, but will supply extra info if needed. Cheers

 

[lbass]

What are the contents of your formulas within the formula?

-LB

 

[slickp007]

{@Exp Rpt New}
if month ({RISKEXT.RiskStartDate}) = {@Month -2} then {@Cur Month} else {@No Policy Issued}

{@Colour}
if month ({RISKEXT.RiskStartDate}) = {@Month -2} then "Black" else
if month ({RISKEXT.RiskStartDate}) = {@Month -2}-1 then "Blue" else "Red"


Hope this helps will supply more if you need it. Cheers

 

[lbass]

All nested formulas must be revealed also.

-LB

 

[slickp007]

{@Cur Month}
if month ({RISKEXT.RiskStartDate}) = {@Month -2} and {@Max Date Reached?} = "N" then "N" else
if month ({RISKEXT.RiskStartDate}) = {@Month -2} and {@Pol Issued in Time?} = "N" then "Y" else "N"

{@Month -2}
if month (currentdate) = 1 then month(currentdate)+10 else
if month (currentdate) = 2 then month(currentdate)+10 else
if month (currentdate) = 3 then month(currentdate)-2 else
if month (currentdate) = 4 then month(currentdate)-2 else
if month (currentdate) = 5 then month(currentdate)-2 else
if month (currentdate) = 6 then month(currentdate)-2 else
if month (currentdate) = 7 then month(currentdate)-2 else
if month (currentdate) = 8 then month(currentdate)-2 else
if month (currentdate) = 9 then month(currentdate)-2 else
if month (currentdate) = 10 then month(currentdate)-2 else
if month (currentdate) = 11 then month(currentdate)-2 else
if month (currentdate) = 12 then month(currentdate)-2

{@No Pol Issued}
if isnull ({@Policy Issue Date - New}) then "Y" else
if {@Policy Issue Date - New} = date (2999,01,01) then "Y" else "N

 

[slickp007]

is that far enough, or would you like more of the nested formulas?

 

[lbass]

All.

-LB

 

[slickp007]

{@Max Date Reached?}
if {@CD-MAXPD} < 0 then "N" else "Y"

{@Pol Issued in Time?}
if isnull ({@Policy Issue Date - New}) then "N" else
if {@Policy Issue Date - New} = date (2999,01,01) then "N" else
if {@Policy Issue Date - New} <= {@Max POL Date} then "Y" else "N"

{@CD-MAXPD}
CURRENTDATE - {@Max POL Date}

{@Max POL Date}
if {@FSA Type} = "RT" then {@Max POL Date Retail} else {@Max POL Date Comm}

{@FSA Type}
if isnull ({RISK.FSARiskType}) then "RT" else
if {RISK.FSARiskType} = "RT" then "RT" else "CM"

{@Max POL Date Retail}
if {@FSA Type} = "RT" and {@Direct or WS} = "Direct" then {@Inc Date + 7 WD} else
if {@FSA Type} = "RT" and {@Direct or WS} = "W/Sale" then {@Inc Date + 5 WD}

{@Inc Date + 5 WD}
if dayofweek ({RISKEXT.RiskStartDate}) = 1 then {RISKEXT.RiskStartDate}+5 else
if dayofweek ({RISKEXT.RiskStartDate}) = 2 then {RISKEXT.RiskStartDate}+7 else
if dayofweek ({RISKEXT.RiskStartDate}) = 3 then {RISKEXT.RiskStartDate}+7 else
if dayofweek ({RISKEXT.RiskStartDate}) = 4 then {RISKEXT.RiskStartDate}+7 else
if dayofweek ({RISKEXT.RiskStartDate}) = 5 then {RISKEXT.RiskStartDate}+7 else
if dayofweek ({RISKEXT.RiskStartDate}) = 6 then {RISKEXT.RiskStartDate}+7 else
if dayofweek ({RISKEXT.RiskStartDate}) = 7 then {RISKEXT.RiskStartDate}+6

{@Inc Date + 7 WD}
if dayofweek ({RISKEXT.RiskStartDate}) = 1 then {RISKEXT.RiskStartDate}+9 else
if dayofweek ({RISKEXT.RiskStartDate}) = 2 then {RISKEXT.RiskStartDate}+9 else
if dayofweek ({RISKEXT.RiskStartDate}) = 3 then {RISKEXT.RiskStartDate}+9 else
if dayofweek ({RISKEXT.RiskStartDate}) = 4 then {RISKEXT.RiskStartDate}+9 else
if dayofweek ({RISKEXT.RiskStartDate}) = 5 then {RISKEXT.RiskStartDate}+11 else
if dayofweek ({RISKEXT.RiskStartDate}) = 6 then {RISKEXT.RiskStartDate}+11 else
if dayofweek ({RISKEXT.RiskStartDate}) = 7 then {RISKEXT.RiskStartDate}+10

{@Max POL Date Comm}
if {@FSA Type} = "CM" and {@Direct or WS} = "Direct" then {RISKEXT.RiskStartDate} + 30 else
if {@FSA Type} = "CM" and {@Direct or WS} = "W/Sale" then {RISKEXT.RiskStartDate} + 21

{@Policy Issue Date}
if isnull ({@UDS Keep}) then Minimum ({@POL Date}, {RISK.RiskReference}) else
if {@UDS Keep} < Minimum ({@POL Date}, {RISK.RiskReference}) then {@UDS Keep} else
Minimum ({@POL Date}, {RISK.RiskReference})

{@UDS Keep}
if {@Old UDS - ID} < 90 then {@Old UDS Date} else date (2999,01,01)

{@Old UDS - ID}
{RISKEXT.RiskStartDate}-{@Old UDS Date}

{@Old UDS Date}
if not isnull ({U1187CCERT_MAIN.MA_POL}) and isnull ({U1187CCERT_MAIN.MA_POST4}) then {U1187CCERT_MAIN.MA_POL}
else {U1187CCERT_MAIN.MA_POST4}


Sorry there is a hell of a lot of nested formulas, it is an arse of a report. If i've missed any out, or you require more info then let me know. Cheers

 

[lbass]

Okay, the problem you are having is because of {@Policy Issue Date} which is referencing summaries. You can use variables like this (I'm assuming riskreference is a string):

//{@reset} for the FSA group header:
whileprintingrecords;
numbervar cnt;
stringvar x;
if not inrepeatedgroupheader then (
cnt := 0;
x := ""
);

//{@accum} for the detail section:
whileprintingrecords;
numbervar cnt;
stringvar x;
if {@Colour} = "Black" and
{@Exp Rpt New} = "Y" and
instr(x,{RISK.RiskReference}) = 0 then (
x := x + {RISK.RiskReference} + ",";
cnt := cnt + 1
);

//{@display} for the FSA group footer:
whileprintingrecords;
numbervar cnt;

-LB

 

[slickp007]

Genius mate!! Thanks for that!!

Just a question off topic, can you recommend any books that would help me with problems like this in the future? Or any advanced crystal coding books?

Cheers

Chris

 

[lbass]

Most of what I know I picked up in Tek-Tips, but the Ken Hamady guides are good. Take a look at his website.

-LB