MDX Count over time without specific hierarchy

Question

This follows on from my question about count from start of time.

I would like to be able to do the count without basing it on a DateTime Hierarchy, but have this as a calculated measure, which works when you drag a dimension against it. I currently have

with member [Measures].[Commitment Total] as
sum(
[Date Dim].[FY Hierarchy].[Fiscal Year].members(0):[Date Dim].[FY Hierarchy].currentMember
, [Measures].[Commitment Count])

Then using the calculation:

select [Measures].[Commitment Total] on 0
, [Date Dim].[FY Hierarchy].[Fiscal Year] on 1
from [Cube]

I would like to have the [Measures].[Commitment Total] not built upon the [Date Dim].[FY Hierarchy], but be independent in the calculation, then use the date axis in the select statement - i.e. we could calculate by the Calendar Year, Fiscal Year, Fisal Year / Month Dim's.

So I'm thinking it'd look like this:

with member [Measures].[Commitment Total] as
sum(
NULL:[Date Dim].currentMember
, [Measures].[Commitment Count])

Then to use the calculation:

select [Measures].[Commitment Total] on 0
, [Date Dim].[FY Hierarchy].[Fiscal Year] on 1
from [Cube]

OR

select [Measures].[Commitment Total] on 0
, [Date Dim].[CY Hierarchy].[Fiscal Year] on 1
from [Cube]

Not sure if what I'm asking is possible or not?

Meff Meff · Accepted Answer · 2011-02-02T13:05:45

I've had a similar problem, and solved it as follows:

SSAS can't really tell you which hierarchy you're on, so you can't do this as simply as in your example. however, the following worked for me. I've tried to rework it to your naming, so check for obvious syntax errors...

This all goes in your cube calculation script, you may need to use 'script view' instead of 'block view'.

CREATE MEMBER CURRENTCUBE.[Measures].[Commitment Total] AS NULL;

Then define it later in the script for each hierarchy:

SCOPE (DESCENDANTS([Date Dim].[FY Hierarchy],,AFTER));    
[Measures].[Commitment Total] =
sum(
[Date Dim].[FY Hierarchy].[Fiscal Year].members(0):[Date Dim].[FY Hierarchy].currentMember
, [Measures].[Commitment Count]);
END SCOPE;

SCOPE (DESCENDANTS([Date Dim].[CY Hierarchy],,AFTER));    
[Measures].[Commitment Total] =
sum(
[Date Dim].[CY Hierarchy].[Calendar Year].members(0):[Date Dim].[CY Hierarchy].currentMember
, [Measures].[Commitment Count]);
END SCOPE;

Now it will behave accordingly, dependent on the hierarchy present in the query. Note that it will be NULL if neither hierarchy is in the query.

MDX Count over time without specific hierarchy

1 Answers