Hi folks,
today's problem as following:
I have a Top-N-Report on dimensional data (Powercube). I use the topcount-Function on a selected hierarchy-level and a measure to create the Top-N-list. For example... product numer (level) and revenue (measure).
My major task at the moment is, that some folks in our company want some of the products not to be in the Top-N-List. For example 2 "default" products. This products should be filtered out.
A query filter on this specific products works as designed. But now the problem: The filter also reduces my Top-N-List from 10 Products to 8 Products (we remember: 2 products were filtered out).
How can i achieve that the 2 products are filtered out AND my Topcount-N-List still shows N records?
When you say filter, are you talking about the filter function or a detail filter?
Generally it is inadvisable (don't do it!) to use detail filters in a dimensional query.
The Top/Bottom functions work on sets, so the trick is to except the products from the level before the Top/Bottom function.
topCount(except([Cube].[Dim].[Hier].[Level],set([Product1],[Product2]),10,[Measure])
Works perfect - thank you.
In addition to this solved problem, may i ask you some other stuff on dimensional reporting issues?
Following questions:
1.) As is added the except-function to the top-count-element, the two excepted products werde added as single elements to my query. Is this normal behaviour?
2.) Is there a way where i can define the exception-set ONCE per report so i can refer to it from various objects and queries?
3.) In my first post i was talking about an detail filter. The filter worked. Where are the issues with dimensional reportings and detail filters? Performance?
Adding the members to the query is incredibly irritating. The behavior can be disabled by Tools -> Options -> Edit -> uncheck Alias member unique names.
You can define the set once per query. Simply create a new data item, ProductSet, as except([Cube].[Dim].[Hier].[Level],set([Product1],[Product2]). The ProductSet is now a valid memberset. You can reference it in any function that will except sets, such as the topCount([ProductSet],10,[Measure]). The topCount also returns a set, which in turn can be used in any other function that requires a set.
The last question is a bit difficult to answer. Dimensional queries are a completely different concept. You can almost consider each data item to be it's own query. Detail filters in cubes work after the query processes. That mean every member in the level will be processed.
A better way to run a query would be to explicitly state exactly which members you need; only those members would process. Instead of filtering caption([Cube].[Time].[Hier].[Years])=2010, which would effectively add the level to the query, run it, then filter after it's been processed, you could slice on [Cube].[Time].[Hier].[Years]->:[PC].[@MEMBER].[20100101-20101231].
Whenever I teach dimensional reporting, I start by drawing a simple crosstab. Years going down, product type across, with a default measure.
The MDX for that is simple:
select
{[products].[type]} on columns,
{[time].[years]} on rows
from cube
where [measures].[sales]
That where is a slicer. For every intersection of type and years it's adding sales. Getting you, for example, tuple(2010,Mountaineering,sales)