Jeff Prom's SQL Server Blog

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

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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: