CODEWITHSUNDEEP
actionssasmdxolaprowset
SacredPanda
SacredPanda

Reputation: 23

SSAS Rowset Action: Replace blank value from DMVs with a text string using MDX

Working in SSAS (inside Visual Studio Shell), I have a rowset action created that returns basic measure information from $SYSTEM.MDSCHEMA_MEASURES. Not all measures in the SSAS database have descriptions.

How can I replace BLANK description values with a simple text string "No description available."

"select [MEASURE_CAPTION], [MEASUREGROUP_NAME], [MEASURE_DISPLAY_FOLDER],[DESCRIPTION], [EXPRESSION]
from $SYSTEM.MDSCHEMA_MEASURES where [MEASURE_UNIQUE_NAME] = '" + Measures].CurrentMember.UniqueName + "'AND [CUBE_NAME] = '" + [Measures].CurrentMember.Properties("CUBE_NAME")  + "'"

Returns:

Current Measure                 Description
Distinct Person Count

DESIRED Return:

Current Measure                 Description
Distinct Person Count           No Description Available

Rowset Action Configuration

Upvotes: 1

Views: 97

Answers (1)

whytheq
whytheq

Reputation: 35557

Is that just straight sql replacement of null or is your script returning a string "BLANK" ?

Try replacing [DESCRIPTION] with:

ISNULL([DESCRIPTION],'No Description Available')

or

CASE WHEN [DESCRIPTION] = 'BLANK' THEN 'No Description Available' ELSE [DESCRIPTION] END

Upvotes: 0

Related Questions