CODEWITHSUNDEEP
ssasmdxolapcube
Dmytro Shevchenko
Dmytro Shevchenko

Reputation: 34591

Aggregating MDX query results on existing facts only

I am very new to MDX, so probably I'm missing something very simple.

In my cube, I have a dimension [Asset] and a measure [Visits], calculating (in this case) how many visits an asset has been consumed by. An important thing to note is that not every visit is associated with an asset.

What I need to find out is how many visits there are that consumed at least one asset. I wrote the following query:

SELECT
  [Asset].[All] ON COLUMNS,
  [Measures].[Visits] ON ROWS
FROM
  [Analytics]

But this query just returns the total number of visits in the cube. I tried applying the NON EMPTY modifier to both axes, but that doesn't help.

Upvotes: 0

Views: 1019

Answers (2)

Benoit
Benoit

Reputation: 1993

This query should give you what you expect:

WITH MEMBER [Asset].[Asset Name].[All Assets] AS
  AGGREGATE( EXCEPT( [Asset].[Asset Name].MEMBERS, { [Asset].[All] } ) )
SELECT
  { [Asset].[Asset Name].[All Assets] } ON COLUMNS,
  [Measures].[Visits] ON ROWS
FROM
  [Analytics]

You may need to put {[Asset].[Asset Name].[All]} as second argument of Except if the All member was not excluded.

In the query I create a calculated member [Asset].[Asset Name].[all assets] that should represent all your existing assets. I supposed that your existing assets are all the members of the level [Asset].[Asset Name] but the All member.

You can find more information about the Aggregate function here.

Upvotes: 1

Dmytro Shevchenko
Dmytro Shevchenko

Reputation: 34591

This works as well:

SELECT
  [Measures].[Visits] ON 0
FROM
  [Analytics]
WHERE
  DRILLDOWNLEVEL([Asset].[All])

Update: as well as this:

SELECT
  [Measures].[Visits] ON 0
FROM
  [Analytics]
WHERE
  [Asset].[All].CHILDREN

Upvotes: 0

Related Questions