Help with Metric Dimensionality?

[bittmapp]

I am trying to created two metrics to use on a report, I am having a problem with one.

There are Stores and Departments, Stores have many departments (Parent>Child)

here is my report



Department Sales StoreSales
------------------------------------------------------
10 100 800
20 100 800
30 100 800
40 100 800


The report has filters that says where department in (10,20,30,40) and date = 12/06/2004

the thing is, the StoreSales metric should be the sum(sales) to the Store level for 12/06/2004 but without the department filter condition.

How to do this?

Thanks,

bitt

 

[aabid]

Add 'filtered' dimensionality to your StoreSales metric on the Store column. This will add up sales for all the departments on the report, as against all the departments in all the stores.

 

[mstrNate]

You should be able to do this by adding some dimensionality to the StoreSales metric. In the metric editor for "StoreSales" metric, go to the Level (Dimensionality) windown pane, and add "Store" to your metric dimensionality. Make the filtering for the Store level "Absolute." Leave the Grouping alone for now. I think that should do it.

Also, a question on your model. If users want to run reports by stores and departments, would your model be able to handle it? It seems like you have a many to many relationship, unless you're taking care of the department ids somehow behind the scenes (making them unique by incorporating the store id somehow). If you're just saying that a store has many departments and a department has one store, when you run a report for multiple stores at the same time you could be setting yourself up for incorrect data. Of course, I don't know your data, just asking.