Cross Tab Problem

[slickp007]

Crystal 11

Ok i have a problem with a cross tab, i have a report which shows business raised by sales advisor, and in the report footer i have cross tab which summarises everything in the report.

I am having a problem showing the number of policies created in the sub report. In the report i suppress anything where the gross premium is zero in the section expert so i can't do a simple distinct count of the the riskref. So i created a formula to count the disintct number of riskref as shown below:

{@reset count}
//in risk ref header
whileprintingrecords;
numbervar cnt;
stringvar x;
if not inrepeatedgroupheader then (
cnt := 0;
x := ""
);


{@risk count}
//in details section
whileprintingrecords;
numbervar cnt;
stringvar x;
if Sum ({@GP exc Fee}, {Analysis.RiskReference}) = 0 and Sum ({@Net Comm ex Fee}, {Analysis.RiskReference}) = 0 and Sum ({@Fee}, {Analysis.RiskReference}) = 0 and
instr(x,{Analysis.RiskReference}) = 0 then (
x := x + {Analysis.RiskReference} + ",";
cnt := cnt + 1
);


{@count}
//in risk ref footer
whileprintingrecords;
numbervar cnt;


I then take the value of {@count} away from the distinct count of Risk Ref and i have my value. However when i summarise this in the crosstab as a maximum of this field, in only shows the total distinct count of risk ref totally ignoring my formula.

Any help, or anyway in which i could do this better? i hope this makes sense.

Cheers

 

[IanWaterman]

YOu could use a simple formula in details

If gross premium = 0 then 0 else 1

A sum of this formula will give you a distinct count

Ian

 

[slickp007]

i couldn't because i couldn't summarise that formula.

 

[IanWaterman]

As long as that formula is evaluated in details then it will summarise.

Where are you placing your variable based formulae.

Ian

 

[slickp007]

i have multiple transactions in the details section and it is only the sum of these transactions that are = 0 that i don't want to see. That is why i have done the formula as in my first post. The sum is found on the group for risk ref.

 

[IanWaterman]

I am not sure what this means.

I then take the value of {@count} away from the distinct count of Risk Ref and i have my value. However when i summarise this in the crosstab as a maximum of this field, in only shows the total distinct count of risk ref totally ignoring my formula.

YOu should not be able to use these formulae in a Crosstab

Ian

 

[lbass]

First open and save a formula {@Null} without entering anything into the formula area. Then create a formula like this:

if {@GP exc Fee} <> 0 or
{@Net Comm ex Fee} <> 0 or
{@Fee} <> 0 then
{Analysis.RiskReference} else
{@Null}

You can then use this in your crosstab, using a distinctcount.

-LB

 

[slickp007]

Cheers Ibass, a legend as always!!