CrossTab Ratio Problem

[Derek7]

hello,

this is my first post, so apologies if I don't include everything you need to tackle my problem and thanks in advance for your time.

I am using Crystal XIR2 and I have a crosstab report which works great except for one of the summarized fields is not working as I expect it to. The goal is to get a ratio of closings to openings (ex: out of 10 open orders, 5 closed so the ratio would be 50%). This is on a per customer and per product (several different products) basis so the crosstab is located in the CustID group footer and contains 1 row: productID.

The basic formula for the field in question looks like:
(Sum ({rpt_MonthlyOrderSummary;1.Closing}, {rpt_MonthlyOrderSummary;1.CustID}) / Sum ({rpt_MonthlyOrderSummary;1.Opening}, {rpt_MonthlyOrderSummary;1.CustID})) * 100

the closing value is 42
the opening value is 154
should evaluate to 27.27

however giving 3.82 as result

note: this formula works perfectly when not used in the crosstab.

I believe this is happening because the cross tab is running the formula on every record.

I tried doing:

formula - @test_new
Sum ({rpt_MonthlyOrderSummary;1.Closing}, {rpt_MonthlyOrderSummary;1.CustID})

formula - @test_new1
Sum ({rpt_MonthlyOrderSummary;1.Opening}, {rpt_MonthlyOrderSummary;1.CustID})

formula for crosstab summarized field:
EvaluateAfter ({@test_new});
EvaluateAfter ({@test_new1});
if {@test_new} <> 0 then
if {@test_new1} <> 0 then
{@test_new} / {@test_new1}
else
0

also tried
OnLastRecord;
if {@test_new} <> 0 then
if {@test_new1} <> 0 then
{@test_new} / {@test_new1}
else
0


All of these attempts result in the 3.82 value mentioned above. I am pulling my hair out on this one. any help is appreciated.

Thanks,
Derek

 

[Derek7]

Issue resolved.

the post by lbass on thread

http://www.tek-tips.com/viewthread.cfm?qid=1356319

worked like a charm. Thanks.