I have an ordered list report that contain revenues and expenses. The finance group wants the signs reversed on revenue accounts. This has been completed and is working correctly. When the signs are changed some of the totaling/subtotaling is now incorrect. The subtotaling is incorrect in two places 1) Summary Net Income and 2) A calculated net income total reported inside the report. I corrected the summary net income line by creating the following data item:
IF ([Year] = ?Accounting Year? and ?Period? = '01')
THEN (-1* [Financials].[GL035M (Pd Activity)].[Period 1])
ELSE (IF ([Year] = ?Accounting Year? and ?Period? = '02')
THEN (-1* [Financials].[GL035M (Pd Activity)].[Period 2])
ELSE (IF ([Year] = ?Accounting Year? and ?Period? = '03')
......
I placed this data item in the summary and it is correct. All of the group subtotals are correct inside the report with this piece of code in a data item -
IF ([Year] = ?Accounting Year? and ?Period? = '01')
THEN (1* [Financials].[GL035M (Pd Activity)].[Period 1])
ELSE (IF ([Year] = ?Accounting Year? and ?Period? = '02')
THEN (1* [Financials].[GL035M (Pd Activity)].[Period 2])
ELSE (IF ([Year] = ?Accounting Year? and ?Period? = '03')
.....
The issue I am having is the calculated net subtotal being reported for a set of income and expenses inside the report. The logic above does not work because the net subtotal needs to subtract a subset of expenses from revenues. So what I need is logic that states if these subset accounts exist subtract expense from revenues and report the total or else report the total as stated above. Any help would be appreciate.