Category Archives: MDX

Dynamic MDX

Posted on by jeffprom

I recently wrote some dynamic MDX queries for my SSRS reports. I didn’t have much trouble until I ran into a StrToMember function that needed a double quote at the beginning and end. Hmm.. how to overcome that. Turns out you can use some VB .Net characters.

Here is a list of some common ones.
chr(39) = single quote
chr(34) = double quote
chr(10) = new line
chr(13) = carriage return

so for my example I had the following:
STRTOMEMBER("[Time Periods].[Fiscal Month Desc].&["+@FromMonth+", "+@FromYear+"]")

After I did some converting, I came up with this:
STRTOMEMBER(" & chr(39) & "[Time Periods].[Fiscal Month Desc].&[" & Parameters!FromMonth.Value & ", " & Parameters!FromYear.Value & "]" & chr(39) & ")

Advertisement

MDX vs T-SQL in SSAS

Posted on by jeffprom

Using SSAS in BIDS, you need to remember that you should be using MDX instead of T-SQL when designing Calculated Members. Here is an example of how both would be used to prevent a divide by zero error.

T-SQL
-- Comment Block
CASE WHEN [Measures].[Measure2] = 0 THEN 0
ELSE [Measures].[Measure1] / [Measures].[Measure2]
END

MDX
// Comment Block
IIF([Measures].[Measure2] = 0, 0, [Measures].[Measure1] / [Measures].[Measure2])

Both will work, but the T-SQL one will give you some warnings. It’s best to remember to use MDX instead.