MDX to get the value and use it on a hierarchy

0
votes

I have a MDX script that my goal is to be able to get the value of a date that could it a day, week, month, quarter or year and then use it on a hierarchy.

WITH 
  MEMBER measures.suma AS 
    Sum
    (
      [Date Local].[year-quarter-month-week-day].Children
     ,IIF
      (
            (
                  [Measures].[Agent Time Logged in hh:mm:ss]
                - 
                  [Measures].[Activity Time Break]
              - 
                [Measures].[Activity Time Meeting]
            - 
              [Measures].[Activity Time Training]
            )
          / 
            26400 / 86400
        > 1
       ,1
       ,
          (
                [Measures].[Agent Time Logged in hh:mm:ss]
              - 
                [Measures].[Activity Time Break]
            - 
              [Measures].[Activity Time Meeting]
          - 
            [Measures].[Activity Time Training]
          )
        / 
          26400 / 86400
      )
    ) 
SELECT 
  {
    measures.suma
   ,[Measures].[MyKeys]
   ,[Measures].[Agent Time Logged in hh:mm:ss]
   ,[Measures].[Activity Time Break]
   ,[Measures].[Activity Time Meeting]
   ,[Measures].[Activity Time Training]
   ,[Measures].[Activity time Lunch]
  } ON 0
 ,NON EMPTY 
    [Agent].[login fullname].[login fullname] ON 1
FROM [HP TRANSACTIONAL]
WHERE 
  [Date Local].[Month].&[2015-08 August];-- DOESNT WORK
--[Date Local].[year-quarter-month-week-day].[Week].&[2015 Week 35] --IT WORKS

As you can see in my MDX script I want to sum all the activities of an agent and the client can choose any filter, per day, week, month, quarter or year.
Of course that thay can use my hierarchy "[Date Local].[Month].&[2015-08 August]" but my client is using EXCEL to create the reports and they can use any attribute of the date dimension.

So my goal is to find a way to indicate to my mdx script that if the client choose any attribute, should be able to get the current member and use the hierarchy to make the calculation.

2
mdx
I think somehow you need to change the first argument in the Sum function: [Date Local].[year-quarter-month-week-day].Children – whytheq
I cannot because I need to use this hierarchy to be able to sum per day. The issue here is that the client can use any attribute of my dim date as a filter and I need to find a way to send the property to the hierarchy and then make the sum. – Raúl Alfonso Jiménez Morocho
I have a simple idea - not sure if it will work though – whytheq
Hi @whytheq, it doesn´t work. I give you more examples: LoginName|DATE|FTE bastien|06/08/2015|0.9542 bastien|25/08/2015|1 FTE = iif.... and it should be 1,9542... as you can see just I have two rows per this user. But if I execute your query I got 6,954242.. – Raúl Alfonso Jiménez Morocho

2 Answers

0
votes

Maybe just try the keyword Existing to try to force context up into your measure suma

   WITH 
      MEMBER [measures].[suma] AS 
        Sum
        (
          EXISTING([Date Local].[year-quarter-month-week-day].MEMBERS)
         ,IIF
          (
                (
                      [Measures].[Agent Time Logged in hh:mm:ss]
                    - 
                      [Measures].[Activity Time Break]
                  - 
                    [Measures].[Activity Time Meeting]
                - 
                  [Measures].[Activity Time Training]
                )
              / 
                26400 / 86400
            > 1
           ,1
           ,
              (
                    [Measures].[Agent Time Logged in hh:mm:ss]
                  - 
                    [Measures].[Activity Time Break]
                - 
                  [Measures].[Activity Time Meeting]
              - 
                [Measures].[Activity Time Training]
              )
            / 
              26400 / 86400
          )
        ) 
    SELECT 
      {
        measures.suma
       ,[Measures].[MyKeys]
       ,[Measures].[Agent Time Logged in hh:mm:ss]
       ,[Measures].[Activity Time Break]
       ,[Measures].[Activity Time Meeting]
       ,[Measures].[Activity Time Training]
       ,[Measures].[Activity time Lunch]
      } ON 0
     ,NON EMPTY 
        [Agent].[login fullname].[login fullname] ON 1
    FROM [HP TRANSACTIONAL]
    WHERE 
      [Date Local].[Month].&[2015-08 August];-- DOESNT WORK
    --[Date Local].[year-quarter-month-week-day].[Week].&[2015 Week 35] --IT WORKS
0
votes

I found a solution!!

WITH 
  MEMBER measures.suma AS 
    Sum
    (
      (EXISTING 
        [Date Local].[year-quarter-month-week-day].[Day name])
     ,IIF
      (
            (
                  [Measures].[Agent Time Logged in hh:mm:ss]
                - 
                  [Measures].[Activity Time Break]
              - 
                [Measures].[Activity Time Meeting]
            - 
              [Measures].[Activity Time Training]
            )
          / 
            26400 / 86400
        > 1
       ,1
       ,
          (
                [Measures].[Agent Time Logged in hh:mm:ss]
              - 
                [Measures].[Activity Time Break]
            - 
              [Measures].[Activity Time Meeting]
          - 
            [Measures].[Activity Time Training]
          )
        / 
          26400 / 86400
      )
    ) 
SELECT 
  {
    measures.suma
   ,[Measures].[Agent Time Logged in hh:mm:ss]
   ,[Measures].[Activity Time Break]
   ,[Measures].[Activity Time Meeting]
   ,[Measures].[Activity Time Training]
   ,[Measures].[Activity time Lunch]
  } ON 0
 ,NON EMPTY 
    [Agent].[login fullname].[login fullname] ON 1
FROM [HP TRANSACTIONAL]
WHERE 
  [Date Local].[Date Standard].&[2015-08-24T00:00:00];
--TEST
--[Date Local].[Week].&[2015 Week 35]
--[Date Local].[Month].&[2015-08 August] 
--[Date Local].[year-quarter-month-week-day].[Week].&[2015 Week 35]

I hope this solution can help to other person. Raul