sqlssasmdx

How to insert a string literal as a new column in an mdx query


In the following MDX query, I want to set the value of [Measures].[Label] as the string literal "Net Value" instead of NULL (i.e. using a string literal to populate the values in the label column). I'm scratching my head about how to do this in MDX. Tons of background with SQL, but a relative newbie with SSAS.

`WITH 
Member [Measures].[Label] AS NULL
    Member [Measures].[Value] AS [Measures].[Net Amt]

SELECT NON EMPTY
  (
    {[Time].[Quarter].[Quarter]},
    {[Time].[Month].[Month]},
    {[Time].[Work Week].[Work Week]},
    {[Customer].[Region Cd].[Region Cd]},
    {[Product].[Cd Nm].[Cd Nm]}
  ) ON Rows,
  NON EMPTY
  (
    {
      [Measures].[Value],
      [Measures].[Label]
    }
  ) ON Columns
FROM [Reporting]
WHERE 
  (
    {
      [Time].[Year].[Year].&[2022]:[Time].[Year].[Year].&[2023]
    },
   {[Segment].[Segment Nm].[Segment Nm].&[SEG VALUE]}
  )`

If I try a value in double quotes, the query just times out without finishing. Just using a null like this only takes 2 seconds to return. Member [Measures].[Label] AS "Net Amt"


Solution

  • The problem with putting a constant is that the NON EMPTY now returns every combination of Time, Customer and Product.

    Instead you want to return a constant but only on rows where the Net Amt measure is not empty.

    Member [Measures].[Label] AS IIF(Not(IsEmpty([Measures].[Net Amt])),"Net Amt",Null)
    

    Alternately you could use a constant but use the NonEmpty function instead against the Net Amt measure only:

    Member [Measures].[Label] AS "Net Amt"
        Member [Measures].[Value] AS [Measures].[Net Amt]
    
    SELECT NonEmpty(
      {
        {[Time].[Quarter].[Quarter]}*
        {[Time].[Month].[Month]}*
        {[Time].[Work Week].[Work Week]}*
        {[Customer].[Region Cd].[Region Cd]}*
        {[Product].[Cd Nm].[Cd Nm]}
      },
      [Measures].[Net Amt]
    ) ON Rows,
      {
          [Measures].[Value],
          [Measures].[Label]
      }
      ON Columns