Iccube - Reporting: I would like to use certain values of measures as a filter.
For example: dimension: product - measure: price
I would like to filter all products which are < 50€. A workaround would be a second dimension "price" but I dont want that because than the schema will load much longer.
Optional: i can define the price as a custom property of the dimension.
Note: i dont want to use something like this ([product].CurrentMember.properties("price") = '50') because there are many different values
cheers j
You might solve this on the server or in the reporting.
In the server :
You can add a utility hierarchy similar to the one described here. As this applies only to one product you would need to do this as a secondary hierarchy of the dimension with the products (you need a new column with this info when loading the dimension).
In the reporting.
The filter is missing yet from the data wizard but you might enter an MDX that filters your products. You can do this with the MDX Filter function.
The point with this solution is if you query defines a subselect or a where clause and whether you want to use this when filtering your products. Using the query context means that if your query defines a particular filter, e.g. [MyCountryInFilter], it will be used when evaluating the filter measure.
Filter( [Products] , [Measures].[MyMeasure] )
will evaluate [MyMeasure] as ( [MyMeasures], [MyCountryInFilter] ). You can indeed make this independent from the filter by using
Filter( [Products], ( [Country].defaultmember, [MyMeasure] )
The more robust version to make this 'filter' independent is by using a static set. If the set is always the same you might create a static set (define it in the schema) as described here :
CREATE STATIC SET [MyFilteredProducts] as Filter( ...
it's also possible to define a set evaluated with a static context at request level
WITH
STATIC SET [MyFilteredProducts] as Filter( ...
...
hope it helps