CODEWITHSUNDEEP
sql-serverssasmdxolapcube
Kulis
Kulis

Reputation: 1010

Checking existence of dimension in MDX

How I can check if one dimension exist on axis in MDX statetment?

I need to check how many time units (days, weeks, months...) exist on axis1 and use it to calculate measure. Here is example, what should happen, I take some dimensions:

days -> [Measures].[A] = [Measures].[B] / number of members in axis 1, from only date dimension (365)

months -> [Measures].[A] = [Measures].[B] / number of members in axis 1, from only date dimension (12)

months, product group -> [Measures].[A] = [Measures].[B] / number of members in axis 1, from only date dimension (12)

So dimension different than date dimension should't affect calcutation. I only need to get count on members from [Date] dimension.

Upvotes: 0

Views: 775

Answers (1)

Danylo Korostil
Danylo Korostil

Reputation: 1484

A simple example is counting of days:

With
Member [Measures].[Members on rows] AS 
Axis(1).Count 

Select
Non Empty [Measures].[Members on rows] on columns, 
Non Empty [Date].[Day].[Day].Members on rows
From [Sales]
Where [Date].[Month].[Month].&[201701]

But you'll get only row count, you can't predict what's going on with an axis. Also you may check whether the whole attribute count = the report attribute count:

Count(existing [Date].[Day].[Day].Members) = Count([Date].[Day].[Day].Members)

If it returns true, most likely that means you don't use filter the [Date].[Day] hierarchy within your report.

Upvotes: 1

Related Questions