Eliminate specific records in transactions

[proplyds]

Hello,
I have a little problem - not sure if it is trivial however can't seem to make it work.
I have a simple formula:
if {table1.transaction} in
['tra1', 'tra2', 'tra3', 'tra4', 'tra5]
then {table2.amount}
else 0

Now, I have another transaction - call it 'sometra'
so what needs to happen is when {table1.transaction}='sometra'
then i want to make 'tra2' and 'tra3' from table 1 to show $0 instead of the amounts which are legit but actually doubling the end result.
Please help, thanks

 

[pmax9999]

Your request is not clear. It would be helpful if you provided some sample data and your expected results.

Cheers
Pete

 

[proplyds]

Hi Pete,
well, the data in table1.transaction is really a column showing transaction codes so they are in the format TR01....TR15 and some other codes, so basically when I have these codes I want to pull money amounts from table2.amount column. the formula the way I described it works however I need to show TR01 and TR03 as zero when I incur OCC1, otherwize in the grands scheme of things the final amounts will be doubled (SYS2=TR01+TR03) not sure if this helps for better understanding.
Thanks again.

 

[pmax9999]

No, still unclear.

According to your formula, an amount will only be other than 0 when the Transaction Code starts with TR, so any other code (ie, those starting with WA, SYS or OCC) will always return a 0.

The way you describe the problem suggests that OCC1 will be part of the same record (ie row), but your sample data suggests otherwise. Your explanation of the problem, doesn't fit with the sample data.

Please confirm the data structure is as your sample suggests (ie comprising just 2 columns of data), and provide a mock up showing what you expect the report to show, based on that sample.

Cheers
Pete

 

[proplyds]

Hi Pete,
hmmmm... it's a bit more complicated.
the formula I described is used in a subreport which passes a variable to the main report showing all transactions considered as adjustments or other fees (many codes). The SYS2 and WA are not included in the formula (or in the many codes in the formula) because they are called in other sub-reports that will calculate transactions current (i.e. WA), past due (SYS2). With OCC1, that is another standard transaction and in most cases will not affect the results as it is a transaction on it's own, and used too in a separate sub-report.
In general terms, I need the TR's to return their values, except unique situations when there is an OCC1 and the TR01/03 in the same account, in which case I would need the TR's to be artificially made zero otherwise they will add up with the SYS2 - therefore doubling the value. I cannot make the zero in the dataset unfortunately, otherwise all would be clear as crystal
eventually I'll try some mock-up hardly so as the whole report is quite complicated (yeah.. bills - everybody loves bills when they come in negative - hardly ever happens)
anyway, attaching a picture.

Balance - the legit amount
Payment - 0 (hmmm....)
Adjustments - that's where the problem lies because in this particular case the TR01 and 03 are read as they should except they should not be there in the data-set.
because they are there,
the past due amount, another simple formula adding up previous balance and payments and the adjustments get's doubled.
Hope this helps a bit more

 

[lbass]

This is very confusing, as you are showing the display, rather than the underlying design of the report. We also cannot tell whether these codes you mention are all instances of the same field or different fields. I think it would help if you showed a screenshot of the report in design mode and then added an explanation of the fields used for Occ1 and other codes.

-LB

 

[proplyds]

Hi,
Thank you all for your kind answers and sorry about confusions. Truly appreciate your time and patience.
Got this solved by another senior member in our team, it was quite a bit more that I thought would be - as it happens.
I can reproduce at a point in time what was done if may help others... at the end of the day the sub-report that was calling the adjustments was modified adding 2 more running totals, couple extra formulas and re-adjusting the variable formula for "adjustments".
Still learning.
Again, Thank you all.

from:

to: