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

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"

CodePudding user response：

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