Hi, so i am new to ACM report designer. I am creating a report of payroll for our vendor, where every detail(job) done by the vendor is listed. Which includes job date, start time, finish time and different payment for the same job(officer pay, city fee, car fee).
Now my report is grouped by job ID. And the information about the different payment is pull out a DAC called” APTran”, where the are stored in a table like below.
But not all job includes all 3 form of payments .Some only have officer pay, some have officer pay and city fee and some have all three(and they are always stored in city fees, car fees, officer pay- in this order).There is a field called inventory id ,which includes the three payment method class. To get the officer pay information in the report row i used
=Last(IIF([APTran.InventoryID]='OFFICERPAY',[APTran.LineAmt],Null))
Since its always the last one in the table. *“LineAmt” is for the Amount field. To get the city fee i used
=First(IIF([APTran.InventoryID]='CITYADMINFEE',[APTran.LineAmt],Null))
Since its always the first one in the table or don't have it. But i am having problem to get the cruiser fee , which either comes in second in the table or don't exist. I used different nested IIF statement to get the value for all the possible circumstances. But are not successfully getting the data.Here is one ,which give me the correct data for some rows but not all of them
=First(IIF( [APTran.InventoryID]='OFFICERPAY',
Null,
Next(iif(
[APTran.InventoryID]='CRUISERFEE',
[APTran.LineAmt],
Null
)
)
My logic is if the first one is officer pay, then the other two don't exist(because its always the last one),If not i want to check the next on the table for ‘cruiserfee’ and returning null if it is not there. What am i doing wrong here? Any help will be appreciated.