Hi,
I have a dataset that has multiple lines per invoice. I need to get invoice total - place negative amounts in one column and positive in another. The data is then grouped at three more levels above invoice number.
I am having trouble getting group totals of the +ve and negative columns at those higher grouped levels.
Year Amt +veInvAmt -veInvAmt
2006 300.00 -500.00
GH: Inv1 300.00 0.00
Inv1 100
Inv1 200
GH Inv2 0.00 -500.00
Inv2 -250.00
Inv2 -250.00
I can get up till the GH:Inv1 level. But when I get to the Year grouping, the formula gets to be a sum(IIF(sum(Amt,"grp1")> 0, (sum(Amt,"grp1"),0)) which is aggregate inside aggregate and apparently not allowed. I am using a report model as the datasource and the entity is at the transaction level. I am not sure I can get the data to come in grouped at the invoice level. Is there any way around this?
Thanks for your time.
I have a dataset that has multiple lines per invoice. I need to get invoice total - place negative amounts in one column and positive in another. The data is then grouped at three more levels above invoice number.
I am having trouble getting group totals of the +ve and negative columns at those higher grouped levels.
Year Amt +veInvAmt -veInvAmt
2006 300.00 -500.00
GH: Inv1 300.00 0.00
Inv1 100
Inv1 200
GH Inv2 0.00 -500.00
Inv2 -250.00
Inv2 -250.00
I can get up till the GH:Inv1 level. But when I get to the Year grouping, the formula gets to be a sum(IIF(sum(Amt,"grp1")> 0, (sum(Amt,"grp1"),0)) which is aggregate inside aggregate and apparently not allowed. I am using a report model as the datasource and the entity is at the transaction level. I am not sure I can get the data to come in grouped at the invoice level. Is there any way around this?
Thanks for your time.
