question about hierarchical dimensions
Hi,
I have a cube that has a "Cost Centre" dimension and a "Product" dimension. Both of these are hierarchical and have multiple levels. I've been asked to write a report which will show a breakdown by "product" & "cost centre" for a certain measure. I wrote the following query which does what I wanted...
SELECT
NON EMPTY { Hierarchize([Cost Centre].[L01 Cost Centre Name].members) } ON COLUMNS,
NON EMPTY { Hierarchize([Product].[High Level Product Name].members) } ON ROWS FROM [MyCube]
WHERE ([Measures].[Costs])
I've now been asked to show the cost centes in groups which are not in the hierarchy. For example, lets say that I have the following Cost Centres members in level 2 of my dimension...
- Level 1 Cost Centre
- Debt Markets Origination PMO & Securitisation
- Other Debt Market
- Flow Credit Trading
- Other T&IP
- Aviation Capital
- Project & Export Finance
I've been told that the cost centres belong to another level not defined in the hierarchy...
- Level 1 Cost Centre
- Origination
- Debt Markets Origination PMO & Securitisation
- Other Debt Market
- Sales
- Flow Credit Trading
- Other T&IP
- Banking
- Aviation Capital
- Project & Export Finance
My question is, if "Origination", "Sales" and "Banking" don't exist in my Cost Centre hierarchy, is it possible to create them in AS somehow or is it possible to select them with MDX somwehow?
Thanks for any help, I hope my question makes sense.
Lachlan
