show members with empty measure values/filters on filters 
Author Message
 show members with empty measure values/filters on filters

Hi, All!
we have an AS2K cube whith few dimensions. Some
dimensions are logically linked with each other.
For example, Sales dept-Salesreps on one dim, State-
Territory-City on other dim. Dependency is that salersep
works with fixed set of territories, and these sets do not
cross for different salesreps.
This relationship stored in MSSQL table. We cannot include
this table in cube scheme, because it will lead to a loop and
non-working cube. So the only inter-dimension relation for
the cube is in cube fact table.
But what we need is to show in some OLAP reports all the
territories for sales rep, regardless of current measure value.
For example, we need to see all territories with zero/null
sales for asking a salesrep "why?". But only this salesrep's
territories.
Trying to filter empty cells - we lose zero sales territories,
showing emty cells - we see all the world territories for each
salesrep... No solution?
Second problem is to show in dimension filters only
members with previous condition applied. For example, if we
selected a salesrep in one filter, we want to see only this
salesrep's territories in second filter. Found no OLAP client
software allowing it. Of course, we can build custom
solution, but maybe we missed something?

thanks in advance,
Andrew



Sun, 01 Aug 2004 15:11:01 GMT
 show members with empty measure values/filters on filters

How do you mean "lead to a loop"? You can always use views to separate
the information for different dimensions out of a single table. Can you
give exact information on the tables and their fields? The kind of
report you need to generate sounds bread-and-butter for almost any OLAP
client.

Quote:

> Hi, All!
> we have an AS2K cube whith few dimensions. Some
> dimensions are logically linked with each other.
> For example, Sales dept-Salesreps on one dim, State-
> Territory-City on other dim. Dependency is that salersep
> works with fixed set of territories, and these sets do not
> cross for different salesreps.
> This relationship stored in MSSQL table. We cannot include
> this table in cube scheme, because it will lead to a loop and
> non-working cube. So the only inter-dimension relation for
> the cube is in cube fact table.
> But what we need is to show in some OLAP reports all the
> territories for sales rep, regardless of current measure value.
> For example, we need to see all territories with zero/null
> sales for asking a salesrep "why?". But only this salesrep's
> territories.
> Trying to filter empty cells - we lose zero sales territories,
> showing emty cells - we see all the world territories for each
> salesrep... No solution?
> Second problem is to show in dimension filters only
> members with previous condition applied. For example, if we
> selected a salesrep in one filter, we want to see only this
> salesrep's territories in second filter. Found no OLAP client
> software allowing it. Of course, we can build custom
> solution, but maybe we missed something?

> thanks in advance,
> Andrew

--
George Spofford
Microsoft MVP
Chief Architect / OLAP Solution Provider
DSS Lab

ISVs & IT organizations: Find out how DSS Lab can speed your
development!


Sun, 01 Aug 2004 20:22:36 GMT
 
 [ 2 post ] 

 Relevant Pages 

1. Filtering a measure to create a calculated member

2. MDX Filter a measure by another measure?

3. Filter Accross Date Range - Different Filter Value for Different Dates

4. Filter dimension members based on current dimension member

5. Filter Time dimension members based on Current member

6. Filter a set on member propeties of current member

7. Filter dimension members based on Current dimension member

8. Showing record x of y when dataset is filtered/showing SQL results

9. How to hide a Measure and show only the calculated member instead

10. How to make a filter on Measure ?

11. filter base on measures

12. Two Measures with Different Filters


 
Powered by phpBB® Forum Software