Jeff Prom's SQL Server Blog

Sharing knowledge and tips on SQL Server, Business Intelligence and Data Management.

Archive for the ‘MDX’ Category

Dynamic MDX

Posted by jeffprom on February 10, 2011

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) & ")

Posted in MDX, SSRS | Leave a Comment »


Posted by jeffprom on August 30, 2010

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.

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

// 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.

Posted in MDX, SSAS, T-SQL | Leave a Comment »