Crosstab is ignoring grouping?

[aks12]

I am using CR 10 with Oracle.

Data looks like this:

RPT_MONTH PRODUCT PERSIST_INT ACCT_PERSISTENCY
05/01/2007 Protect 0 7161
05/01/2007 Generic 0 5
05/01/2007 Protect 1 6306
05/01/2007 Generic 1 4

There are 2 formulas:

//{@persist interval}
if {TABLE.PERSIST_INT} = 0 then "."
else if
{TABLE.PERSIST_INT} > 0
and {TABLE.PERSIST_INT} < 36
then
"Int. " & totext({TABLE.PERSIST_INT},0)
else
"Int. 36+"

//{@Saved acct. persistency %}
numberVar x;
if {@Persist Interval}='.' then
x := {TABLE.ACCT_PERSISTENCY};

if {TABLE.ACCT_PERSISTENCY} % x=100 then 0
else
{TABLE.ACCT_PERSISTENCY} % x

The report has a grouping on {TABLE.PRODUCT}. A crosstab is built in the GF with {@persist interval} as the column, {TABLE.RPT_MONTH} as the row, and max({@Saved acct. persistency %}) as the summary.

The question the report is answering is: For each product, what % of Interval '.' is each subsequent Interval for each month?

Expected output in 1st crosstab is:

==================================
Protect

. Int. 1

05/01/2007 0 88.06
==================================

Actual output in 1st crosstab is:

==================================
Protect

. Int. 1

05/01/2007 0 126,120.00
==================================

The report is obviously deriving this calculation from the 2nd and 3rd records, rather than the 1st and 3rd
as I need it. I thought that since I have the crosstab in the {TABLE.PRODUCT} GF, the 2nd record would be ignored because it's a diffent product.
Is there a way to get the correct calculation in my crosstab?

 

[lbass]

Are you resetting x in the group header? Did you add {@Saved acct. persistency %} as the summary and then insert a maximum on it?

-LB

 

[aks12]

I tried resetting x to 0 in the group header with
numbervar x:=0;
but the result doesn't change.
The only summary line in my crosstab is
Max of {@Saved acct. persistency %}.
Any other ideas?

 

[lbass]

What happens if you add product as an additional row field?

-LB

 

[aks12]

lbass,
I tried that too before my original post and the crosstab still shows 126,120.00 in that particular cell.

Output looks like this:
===============================
. Int. 1

Protect 05/01/2007 0 126,120.00
===============================

This is no longer a production problem for me, however. The client has since told me that the "Generic" product in the original dataset is not a valid product and should not have been in the table. After those two rows were removed, the report numbers came out as expected.

As a Crystal developer, though, this problem still bugs me because it is counterintuitive to what you'd expect a report would and should do.

If anyone coming across this thread in the future builds the 4 record dataset, formulas, and crosstab as described above and can come up with the correct percentage, please post your solution!