May 1, 2003 at 2:31 pm
I have a measure called "asset value 1", that
the user can dynamically see summed up across any or more than one dimensions.
I want to add an addional measure
called 'Asset value 2' whose
value is the same as the asset value
but only if the product = 'Loan'
When I am browsing "ANY" dimension
I want to see two measures next to
each other eg:
The records in the table are
Branch Product Value
--------------------------
Branch 1 Funds 100
Branch 2 Loans 200 Branch 2 Securities 300
I want to see this in view 1:
Product AssetValue1 AssetValue2
--------------------------------------------
Funds 100 0
Loans 200 200
Securities 300 0
---------------------------
600 200
I want to see this in view 2:
Branch AssetValue1 AssetValue2
Branch 1 100 0
Branch 2 500 200
---------------------------
600 200
I might add that the Branch and Product
is stored in the dimension tables
and not in the fact table.
You help is appreciated!
Many thanks in advance
May 1, 2003 at 5:54 pm
Hi,
You could use a calc member/measure in your cube, but this will probably use an iif statement for every query and you'll also have to cater for situations where the product dimension is not in use.
If you're using views, why not just add a new measure column that in the SQL for the view you perform your CASE statement, so the view could look like
SELECT
BranchID,
ProductID,
Value AS AssetValue1,
CASE
WHEN ProductID = 2 THEN Value
ELSE 0
END AS AssetValue2
FROM
tblBob
Assumptions in the above are:
1. Your product foreign key is held within the fact table
2. You know the value or range of values for the Loan type product(s)
HTH,
STeve.
Steve.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply