Crosstabs - Problems with % Calculation

[brxmas]

Crystal 9.0
Connection ODBC
MSQL 2000

Hopefully someone can help me....Thanks in advance.

Instead of using a subreport which takes forever to run, lbass suggested that I use crosstabs, which works great. My problem is I not getting the correct % for each category.

The report shows the total of categories after each Sales Rep - Current Year (CY), Last Year (LY) Var and %. The problem I'm having after I get the variance LY 6,355 - CY 12,363 (6,008), then I divide 6008 / 6355 = 94.5 which is correct


Sales Rep# 001
CY $ LY $ Var %
Adjustments 168 420 - 252 -59.9
Color 12,363 6,355 6,008 - 49.46 (wrong)

Here's my formula for %
IF Sum ({@CY_cost}, {@category}) >= 0 AND
Sum ({@CY_cost}, {@category}) < 1 THEN 0

ELSE
IF Sum ({@LY_cost}, {@category}) >= 0 AND
Sum ({@LY_cost}, {@category}) < 1 THEN 0
ELSE

IF Sum ({@LY_cost}, {@category}) >= 0 AND
Sum ({@LY_cost}, {@category}) < 1 then 100
ELSE

IF Sum ({@CY_cost}, {@category}) >= 0 AND
Sum ({@CY_cost}, {@category}) < 1 then -100
ELSE

((Sum ({@Rev_var}, {@category}) / Sum ({@LY_cost},{@category})))*100

I've tried all the various calculations 'Average', 'Sum', etc.

Any help would be greatly appreciated.

 

[ShortyA]

Hi Brxmas,
what value does the @Rev_Var formula bring out as it is referred to in the %age formula ? Does it show the value you are expecting ?

ShortyA

 

[brxmas]

The @Rev_var formula substracts LY $ from CY $ which in the example above:

CY LY Var
12,363 - 6,355 = 6,008

So the % would be 6,008 / 6,355 = 95

When I use this formula in a subreport, I get the correct data. Hopefully, someone can help resolve this issue....
Thanks....bc