Within my GL accounts that are aggregated in my finance cube (standard jet - nav cube) I have an account that i need to multiply by -1. How can i edit the cube so this one account will display correctly.
Date
Votes
1 comment
-
Jet Reports Historic Posts Greetings!
If you want to change the signs on this GL Account for presentation purposes, you could do that inside of Excel. If you really want to change the signs on the back-end, you could accomplish that with a CASE statement in a custom transformation (right-click the field –> field transformations –> custom) and use syntax similar to the following:
CASE WHEN [G/L Account No.] IN ('14200', '52300') THEN [Amount] * -1 ELSE [Amount] END
You could place that custom transformation on the [Amount] field in [G/L Entry] in the Stage, replacing the values inside the parenthesis following IN with the GL Accounts you want to change the signs on. The [G/L Entry] and [G/L Budget Entry] tables in the Stage roll-up to the [Financial Transactions] table in the Data Warehouse. Just remember that, since the accounts are hard-coded in the SQL script, that you'll have to modify and/or undo the operations if business logic changes.
Please sign in to leave a comment.