formula help

[ridhirao22]

Hi, Using oracle 10, CR2008 on a new project.

Working on report which run for a given claim number.
Claim number has more than one settlements and product items. The issue I am facing is product items are same under different settlement and the report need to show the remaining amount after the current transaction to date and amt pay and prior payment. I don't how better I can explain.
For this is what the report shows now.
5343 settlement
Item Prior current Current Remain
paid PTD Trans Bal
032-00 1789.45 1698.8 -90.65 0
112-01 523473.66 488613.17 -3717.27 -31,143.22
112-05 75620.54 70584.63 0 -5035.91

6021 Settlement

032-00 1789.45 1698.8 0 -90.65
112-01 523473.66 488613.17 -31,143.22 -3717.27 (already paid for settlement 5343 -3717.27 so needs to show 0)
112-05 75620.54 70584.63 0 -5035.91


I need to work to display the report as below:


5343 settlement
Item Prior current Current Remain
paid PTD Trans Bal
032-00 1789.45 1698.8 -90.65 0
112-01 523473.66 488613.17 -3717.27 -31,143.22
112-05 75620.54 70584.63 0 -5035.91

6021 Settlement

032-00 1789.45 1698.8 0 0
112-01 523473.66 488613.17 -31,143.22 0
112-05 75620.54 70584.63 -5035.91 0

Any help is helps me make my day....

Thanks,
RR

 

[ridhirao22]

Please advice....

 

[ridhirao22]

let me try explain my issue again, Hope this helps me find an advice....

I need to display report for settlement number and product for remain amount

for ex: Settlement number = 5353 and product number = 0032-01

pay amount = -34,860.49
trans paid = -3,717.27
@remain amt = pay amt - trans paid
i.e -31,142.22
which is fine so far. but when I another settlement and same product I asked to calculate this way(don't know why)

for ex: Settlement number = 5367 and product number = 0032-01

pay amount = -34,860.49
trans paid = -31,142.22 -3,717.27
since @remain amt = pay amt - trans paid report will display -3,717.27
seems like I need to calculate to show as 0 since that product 0032-01 has pay of -3,717.27 in before settlement

It's so confusing to me....Please advice. TIA

 

[lbass]

I think you should save the report under another name and then insert a subreport (in the detail section of the main report) that is linked on item and on settlement. Go into the subreport->report->selection formula and then change the criteria to:

{table.item} = {?pm-table.item} and
{table.settlement} <= {?pm-table.settlement}

This assumes a group on settlement in ascending order. Then in the subreport, insert a running total {#transactions} that does a sum of transaction, evaluate for each record, reset never. Then create a formula {@bal}:

{table.priorpaid}-{table.currentPTD}+{#transactions}

Use this for your balance column and place it in the sub report footer. Display only the subreport report footer in the main report.

-LB

 

[ridhirao22]

LB,
Thanks for your time to reply my question. I was looking up for you. You give great advices.

You mean I create a subreport and display only the @bal inthe report footer and place this subreport in place of my @remain bal in the main report.

If I create parameter in the subreport wouldn't the report ask for input when it run?

I will giv it a shot and let you know. Thanks!

 

[ridhirao22]

It tooklong time to reading records and while still reading it close crystal with the CRW error...Any help

 

[lbass]

When you create the subreport links it automatically creates the parameter. I'm not sure how you implemented my suggestion. In the subreport, go to report->selection formula->record and copy what you have there and paste it into this thread.

-LB

 

[ridhirao22]

Thanks a lot LB. You made my day!.

The subreport logic works, but I had to change the @bal
{table.priorpaid}-{table.currentPTD}+{#transactions}
formula in order for me to et the correct result set.

Thanks again!