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.
For financial reporting (often demanding addition and subtraction within sets of data) we used so-called (a coin phrased by the competion) 'Report control tables'. These basically stored accountnumbers , type and a value . This value was either 1 or - 1 If a combination would call for subtraction the true value was multiplied with -1. The control table was used in a direct join to the real financial data. From a report perspective nothing had to be fixed, reducing all kind of 'if then else' logic. By maintaing everything within one table all definitions were stored in a single piece of (meta)data