Search code examples
mdxolap

MDX Request : How to Topcount without specific members

I'm new to MDX and i am having troubles with the TOPCOUNT() function.

Lets say i want the following result →

enter image description here

This is what I get → Img02

Here is like what my request would look like with this exemple →

WITH

SET SpecificStores AS EXCEPT([Stores].CHILDREN,[Stores].&[Store3])

SET TopProducts AS TOPCOUNT(SpecificStores * [Products],3,[Measures].[QTE])

SELECT {[Measures].[QTE]} ON COLUMNS,

{TopProducts} ON ROWS

FROM [MyCube];

The SpecificStores Member seems to work like a list and not like a unique member, is there a way to fix that ?

Solution

  • You defined your SpecificStores as a set: a list of all children (except the one you're excluding). The TopProducts set will be calculated based on that.

    What you want is to define SpecificStores as a member:

    WITH
  MEMBER [Stores].[SpecificStores] AS Aggregate( EXCEPT([Stores].CHILDREN,[Stores].&[Store3]) )
  SET TopProducts as TopCount( {[Stores].[SpecificStores]} * [Products].Children , 3, [Measures].[QTE])
SELECT
  TopProducts on Rows,
  [Measures].[QTE] on Columns
FROM [MyCube]