Hi All,
I have a Fact table that has transaction detailes for Actual and Budget data for daily Sales. I also have a dimension table that holds the definition of record type.
I called the dimension table 'scenario' it has 'A' for Actual and 'B' for Budget. This column is linked in the backend to a similar Column in the Fact Table.
My question is if I added another set of transaction data to the Fact Data that are not necessary Actual or Budget, let's assume 'overseas Cost' and I created an entry 'C' in the scenario table for oversees Cost.
So I will end up having 'A' for Actual, 'B' for Budget, 'C' for oversees Cost.
Now how I can create a Scenario let's say 'D' that is equal to 'A' + 'C' ( Actual +overseas Cost) without having to create a set of data in the Fact table for 'D'.
I mean I need Cognos to add 'A' +'C' internally.
Fact table
SCenario Sales_id Amount
A 100 200K
B 100 170K
C 100 2K
D 100 202K ( I don't want to have to add A+C in the Fact table)
Dimension Table 'Scenario'
Scenario Name
A Actual
B Budget
C Overseas Cost
D Actual+Overseas Cost
Multiple ways to do this.. One way would be -
You already have a column in the Fact table that has the codes A, B and you are going to add C.
So once you bring this in FM as a Query subject, you can create a calculated column with the expression -
If ([Scenario] = 'A' or [Scenario] = 'C') then ('D') else ([Scenario]).
Now you have a column parallel to Scenario with the code 'D'.
In the report, you can set a filter to read only D and the agrregated result would be A + C. You have to be a little creative in the Report making process so as to be able to set the filter in the individual column itself if required.
You can also modify the query subject definition, that way the value will be always calculate.