Search code examples
ssasolap-cube

How to get count of all currently selected members of dimension

I have got a cube with date dimension:

Period
- Year
-- Quarter
--- Month
---- Date

I need to get count of all dates, selected by user (f.e. in Excel). I have tried to use calculated members like:

[Period].[Period].CurrentMember.children.count

It is good when 1 month is selected. In other ways (selected only few dates or Quarter) it returns the count of children of the next level of hierarchy.

Descendants([Period].[Period],[Period].[Period].[Date]).count
Descendants([Period].[Period].CurrentMember,[Period].[Period].[Date]).count

So I tried to use Descendants. The results are good when 1 element on any level is selected. But one you select f.e. 2 month in one Quarter - it gives counts of all elements.

How can I get count of all selected elements on Date level?

I have also tried:

[Period].[Period].[Date].count
[Period].[Period].[Date].CurrentMember.count
COUNT([Period].[Period].[Date])
COUNT([Period].[Period].[Date].CurrentMember)
Solution

  • To work out multiselect in Excel, you can try Dynamic Set technique. Assume your [Period] dimension has [YQMD] hierarchy, then try the following
    CREATE DYNAMIC SET SelectedDates AS ( [Period].[YQMD].[Date] ) CREATE MEMBER CURRENTCUBE.[Measures].[SelectedDatasCount] AS ( SelectedDates.count )
    Refer to this SO article and MSDN discussion. Unfortunately, Mosha's article is no longer available.
    Caveat - solution with Dynamic Set can hinder query performance.