Write an advanced MDX query

Question

I am using a multidimensional cube, with a lot of dimensions and measures, which are used to get data for the creation of a report in SSRS.

Here is the task text:

User Name - include users from &[A Group] and &[B Group] from dimension [Group], connected with [Users] dimension.;
Count where [Case Action] = "A" or [Case Action] = "B" and [Reason] = "R" (include all member from &[A Group] and &[B Group]);
Exclude cases where [Case Action] = "A" and [Reason] = "R" or [Case Action] = "S";
Count cases during report period and rolling period of 13 months backwards from the report month.
Report date can be any valid date and can not be a future date.
If report date is not a month End Date, then report will be generated for the previous month and a rolling period of 13 months data available in report from the previous month to backwards.
If report Date is a Month End Date, then Report will be generated from the specified month of 13 months backwards.

There is a measure named [Case Count] which is connected to the required dimensions. Somehow I need to get filtered facts from it, and in the end result must look like user names on rows, months and cases count on columns, showing the count of cases for each user aggregated for each month.

Here is the code which I have written so far:

WITH 
  SET [Report Period] AS 
    Generate
    (
        ParallelPeriod
        (
          [Dim Date].[Calendar Hierarchy].[Calendar Month]
         ,13
         ,StrToMember
          ("[Dim Date].[Calendar Hierarchy].[Calendar Date].[2013-08-30]"
           ,CONSTRAINED
          ).Parent
        )
      : 
        StrToMember
        ("[Dim Date].[Calendar Hierarchy].[Calendar Date].[2014-08-30]"
         ,CONSTRAINED
        ).Parent
     ,{[Dim Date].[Calendar Hierarchy].CurrentMember}
    ) 
  SET [User Name] AS 
    Exists
    (
      [Dim Alert Action Assigned To User].[Group Hierarchy].[User Name].MEMBERS
     ,
        [Report Period]
      * 
        {
          [Dim Group].[Group Name].&[QA Group]
         ,[Dim Group].[Group Name].&[Supervisor Group]
        }
    ) 
  MEMBER [Measures].[Cases] AS 
    CoalesceEmpty
    (
      Aggregate
      (
        {
          [Dim Case Action].[Action Case Alias].&[A]
         ,[Dim Case Action].[Action Case Alias].&[S]
        }
       ,[Measures].[Case Count]
      )
     ,0
    ) 
SELECT 
  {[Report Period] * [Measures].[Cases]} ON 0
 ,{[User Name]} ON 1
FROM [CaseAlerts];

The results must look like the following:

On rows -> User Names,

On Columns -> Months and Cases Count for the current Month.

What have you already written? (Unless we can see your current code I'm unsure how much help you will get) — whytheq
why bother with StrToMember? Can you not just use the member directly? Will that be parameterized? — whytheq
@whytheq - because she is using SSRS, which recognizes and returns STRING. — SouravA
@Sourav_Agasti no worries Sourav - to me the script looks like it can be simplified quite a bit, unfortunately I'm struggling with time today ...maybe you can help. — whytheq
Exclude cases where [Case Action] = "A" and [Reason] = "R" or [Case Action] = "S"; This one doesn't make sense. — SouravA

SouravA SouravA · Accepted Answer · 2014-12-23T17:34:52

The below two conditions seem contradictory.

Exclude cases where [Case Action] = "A" and [Reason] = "R" or [Case Action] = "S"; 

Count where [Case Action] = "A" or [Case Action] = "B" and [Reason] = "R" (include all member from &[A Group] and &[B Group]);

I am going to ignore the first condition.

The below condition should be checked in front end.

Report date can be any valid date and can not be a future date.

As for the other conditions, check if the MDX below works.

WITH 
SET CurrentMonth AS
EXISTS(
[Dim Date].[Calendar Hierarchy].[Calendar Month], 
StrToMember
  ("[Dim Date].[Calendar Hierarchy].[Calendar Date].[2013-08-30]"
   ,CONSTRAINED
  )
  )



  SET [Report Period] AS 
   IIF([Dim Date].[Calendar Hierarchy].CurrentMember IS NOT CurrentMonth.LastChild
   , 
         {CurrentMonth.lag(14).lastChild
              : 
          CurrentMonth.lag(1).lastChild}--last month's last date            
     ,        
        {
          ParallelPeriod(
          [Dim Date].[Calendar Hierarchy].[Calendar Month]
         ,13
         ,
        StrToMember
          ("[Dim Date].[Calendar Hierarchy].[Calendar Date].[2013-08-30]"
           ,CONSTRAINED
          )
          )
          :
        StrToMember
          ("[Dim Date].[Calendar Hierarchy].[Calendar Date].[2013-08-30]"
           ,CONSTRAINED
          )
      }  
        )



  SET [User Name] AS 
    Exists
    (
      [Dim Alert Action Assigned To User].[Group Hierarchy].[User Name].MEMBERS
     ,
        [Report Period]
      * 
        {
          [Dim Group].[Group Name].&[QA Group]
         ,[Dim Group].[Group Name].&[Supervisor Group]
        }
    ) 


  MEMBER [Measures].[Cases] AS 
    CoalesceEmpty
    (
      Aggregate
      (
        ({
          [Dim Case Action].[Action Case Alias].&[A]
         ,[Dim Case Action].[Action Case Alias].&[S]
        },  [Dim Case Action].[Reason].&[R])
       ,[Measures].[Case Count]
      )
     ,0
    ) 


SELECT 
  {[Report Period] * [Measures].[Cases]} ON 0
 ,{[User Name]} ON 1
FROM [CaseAlerts];

Write an advanced MDX query

1 Answers