After a couple of days off work last week with SQL Bits III and SQL 2008 Unleashed, it’s back to the grindstone this week; however before I get onto the serious stuff I’d like to say thank you to the organisers of both events. Watching you on stage headbanging to Rockstar – legendary! (You can see for yourself here and here…).

Anyway, back to MDX…

This post explains how you can build a dynamic MDX query in Reportins Services, customised to the users requirements. This can often bring some quite major performance benefits.

Lets say for example that you want to have a sales report grouped dynamically by either product, sales person, department or customer. Normally you would use a single static MDX query, and then add a dynamic grouping to the table in the report. This is fine, until you try it on a large dataset. If you only have 50 products, 2 salesmen, 5 departments and 100 customers, your MDX needs to return 50,000 records, the report then has to summarise all of this into the level of grouping you want. This renders the pre-calculated aggregations in OLAP pretty much worthless.

To get around this, you can generate your MDX dynamically, so that the query returns the data already grouped into the correct level. You can also use this to add extra filters to the query, but only when they are required.

To start with, lets see how we would do this normally with SQL. Assuming we’re working from a denormalised table such as this

Dynamic MDX Table

Dynamic SQL is pretty simple, instead of having your dataset query as

  SELECT SalesPerson,
      Sum(Sales) AS Sales
   FROM tblData
   GROUP BY SalesPerson

you can add a report parameter called GroupBy,

Dynamic MDX SQL

and then use an expression as your dataset

  ="SELECT "
      + Parameters!GroupBy.Value + " AS GroupName,
      Sum(Sales) AS Sales
   FROM tblData
   GROUP BY " + Parameters!GroupBy.Value

However MDX queries don’t let you use an expression in the dataset, so we have to work around that quite major limitation. To do this we make use of the OpenRowset command. You need to enable it in the surface area config tool, but once it’s enabled you can fire off an OpenRowset command to SQL Server, which will then pass it on to the cube. As the datasource connnection is to SQL Server not Analysis Services, it allows you to use an expression in the dataset.

  ="SELECT * FROM OpenRowset('MSOLAP',
    'DATA SOURCE=localhost; Initial Catalog=SalesTest;',
    'SELECT
      {[Measures].[Sales]} ON 0,
      NON EMPTY {[Product].[Product].[Product].Members} ON 1
      FROM Sales') "

You can then expand this to make it dynamic depending on the value of a parameter. Before we do this though, there are a couple of items I should point out.
1) As the expression can get quite large, I find it much easier to create the query from a custom code function
2) As SSRS can’t interpret the expression at runtime, you need to define the fields in your dataset manually (more on this later)

To use a custom code function, just change the dataset expression to

  =Code.CreateMDX(Parameters)

We pass in the parameters collection so that we can use the parameters to determine the query. Create a function called CreateMDX() in the code block

Dynamic MDX Code

You can then construct your MDX query within the code block.

  Public Function CreateMDX(ByVal params as Parameters) as string

   Dim mdx as string

   mdx = "SELECT * FROM OpenRowset("
   mdx += " 'MSOLAP', "
   mdx += " 'DATA SOURCE=localhost; Initial Catalog=SalesTest;', "
   mdx += " ' SELECT {[Measures].[Sales]} ON 0, "
   mdx += "    NON EMPTY {[Product].[Product].[Product].Members} ON 1 "
   mdx += "   FROM Sales ' "
   mdx += ")"

   return mdx

End Function

We’re almost there…
The next problem is that the field names returned by the query are less than helpful. To fix this we just need to alias the fields in the query. I usually take the opportunity of casting the numerical fields so that the report treats them as such, rather than as a string.

  Public Function CreateMDX(ByVal params as Parameters)
                  as string

   Dim mdx as string

   mdx = "SELECT "
   mdx += "  ""[Product].[Product].[Product].[MEMBER_CAPTION]"" AS GroupName, "
   mdx += "   Cast(""[Measures].[Sales]"" AS int) AS Sales "
   mdx += " FROM OpenRowset("
   mdx += " 'MSOLAP', "
   mdx += " 'DATA SOURCE=localhost; Initial Catalog=SalesTest;', "
   mdx += " ' SELECT {[Measures].[Sales]} ON 0, "
   mdx += "    NON EMPTY {[Product].[Product].[Product].Members} ON 1 "
   mdx += "   FROM Sales ' "
   mdx += ")"

   return mdx

End Function

(please do watch out for the quotes, double quotes and double double quotes, it can get a little confusing!)
We then need to tell the dataset which fields to expect from the query.

Dynamic MDX Fields

You can now use the dataset in your report.
However, the original point of this was to make the query dynamic… All we need to do to achieve this is expand the VB.Net code accordingly.

  Public Function CreateMDX(ByVal params as Parameters) as string

   Dim mdx as string

   mdx = "SELECT "

IF params("GroupBy").Value.ToString()="Product" THEN
   mdx += "  ""[Product].[Product].[Product]"
ELSE IF params("GroupBy").Value.ToString()="SalesPerson" THEN
   mdx += "  ""[Sales Person].[Sales Person].[Sales Person]"
ELSE IF params("GroupBy").Value.ToString()="Customer" THEN
   mdx += "  ""[Customer].[Customer].[Customer]"
END IF

   mdx += ".[MEMBER_CAPTION]"" AS GroupName, "

   mdx += "   Cast(""[Measures].[Sales]"" AS int) AS Sales "
   mdx += " FROM OpenRowset("
   mdx += " 'MSOLAP', "
   mdx += " 'DATA SOURCE=localhost; Initial Catalog=SalesTest;', "
   mdx += " ' SELECT {[Measures].[Sales]} ON 0, "

IF params("GroupBy").Value.ToString()="Product" THEN
   mdx += "  NON EMPTY {[Product].[Product].[Product]"
ELSE IF params("GroupBy").Value.ToString()="SalesPerson" THEN
   mdx += "  NON EMPTY {[Sales Person].[Sales Person].[Sales Person]"
ELSE IF params("GroupBy").Value.ToString()="Customer" THEN
   mdx += "  NON EMPTY {[Customer].[Customer].[Customer]"
END IF

   mdx += ".Members} ON 1 "

   mdx += "   FROM Sales ' "
   mdx += ")"

   return mdx

End Function

It’s certainly not that simple, and debugging can cause a few headaches, but you can benefit from a massive performance in complex reports if you’re prepared to put the work in.

Dynamic MDX Results

You can download the project files here

As always, please let me know how you get on with it, and shout if you have any queries…

Alex

© Alex Whittles, Purple Frog Systems Ltd

32 Responses to “Dynamic MDX in Reporting Services”

  • Chris Webb:

    Interesting approach – incidentally you can do the same thing much more easily with Intelligencia Query (which I help develop – http://www.it-workplace.co.uk/IQ.aspx).

    With IQ, by default all column headers for a dataset are numbered and do not relate to the data the dataset contains, although this can be changed. This then means that you can have any dynamic MDX you want so long as the cellset returns the same number of columns; and that means you can easily parameterise what goes on rows. One of the sample reports shows you how to do this in fact.

  • Alex:

    Hi Chris

    Looks like a pretty useful addin – I’ll check it out when I get a minute spare… It certainly looks like it would save a bit of work.

    Thanks for all of your work on SQLBits – I hope life is starting to return to normality..?

  • PedroCGD:

    Fantastic post! An example for the comunity!!! I will check you example right now in my laptop!
    Thanks for the post!!!
    Cheers Alex!!

  • Chris Webb:

    Glad you enjoyed SQLBits! I’m pleased it’s all over though, yes.

  • RYErnest:

    Nice post u have here :D Added to my RSS reader

  • Amy Pham:

    This is an amazing post. This saved me so much time. Thank you so much for posting this information. I almost cried when it works!

  • Chris Corbin:

    I quote from your own article…

    “However MDX queries don’t let you use an expression in the dataset”…

    This is not true. You can use expressions with an MDX dataset. I am doing it in a report currently. It is a pain in the ass to maintain, because it forces you to store your MDX in the report in a format in which you cant simply copy and paste into Management Studio and execute, but it does work.

    Although, interesting idea on using the VB. I will try that and see which performs faster. I always forget SSRS 2005 allows you to use VB… probably because I’m a C# guy, and view VB as the plague ;)

  • Edson Crevecoeur:

    This works great. I was able to build the following:

    Public Function CreateMDX(ByVal params As Parameters) As String
    Dim sConn As String
    Dim sPeriodTypeShort As String
    Dim sPeriod As String
    Dim sMonthEnding As String
    Dim sTimePeriod As String
    Dim sMember As String
    Dim sMeasure As String
    Dim sCast As String
    Dim DBSelect As String
    Dim Axis0 As String
    Dim Axis1 As String

    sConn = “‘MSOLAP.3′,’DATA SOURCE=XXXX; Initial Catalog=XXXX;’”

    Select Case params(“PeriodType”).Value
    Case “Calendar”
    sPeriodTypeShort = “(CY)”
    Case “Fiscal”
    sPeriodTypeShort = “(FY)”
    End Select

    Select Case params(“Period”).Value
    Case “MTH”, “YTD”, “MAT”, “RMAT”
    sPeriod = “Month”
    sMonthEnding = params(“MonthEnding”).Value

    Case “QTR”
    sPeriod = “Quarter”
    sMonthEnding = params(“MonthEnding”).Value & “.PARENT”

    Case “FY”
    sPeriod = “Year”
    sMonthEnding = params(“MonthEnding”).Value & “.PARENT.PARENT”
    End Select

    sTimePeriod = “[Time Periods].[\" & params(\"PeriodType\").Value & \" Hierarchy].[\" & params(\"PeriodType\").Value & \" \" & sPeriod & \"]”
    sMember = “[IMS Products].[\" & params(\"Member\").Value & \"].[\" & params(\"Member\").Value & \"]”

    Select Case params(“Period”).Value
    Case “MTH”
    sMeasure = “[Measures].[IMS \" & params(\"Value\").Value & \"]”
    Axis0 = sMeasure
    Axis1 = “NON EMPTY {PARALLELPERIOD(” & sTimePeriod & “,23,” & sMonthEnding & “):” & sMonthEnding & “} * ” & sMember

    Case “QTR”
    sMeasure = “[Measures].[IMS \" & params(\"Value\").Value & \"]”
    Axis0 = sMeasure
    Axis1 = “NON EMPTY {PARALLELPERIOD(” & sTimePeriod & “,7,” & sMonthEnding & “):” & sMonthEnding & “} * ” & sMember

    Case “YTD”
    sMeasure = “[Measures].[IMS \" & Trim(params(\"Value\").Value & \" YTD \" & sPeriodTypeShort) & \"]”
    Axis0 = sMeasure
    Axis1 = “NON EMPTY {PARALLELPERIOD(” & sTimePeriod & “,48,” & sMonthEnding & “), ”
    Axis1 = Axis1 & “PARALLELPERIOD(” & sTimePeriod & “,36,” & sMonthEnding & “), ”
    Axis1 = Axis1 & “PARALLELPERIOD(” & sTimePeriod & “,24,” & sMonthEnding & “), ”
    Axis1 = Axis1 & “PARALLELPERIOD(” & sTimePeriod & “,12,” & sMonthEnding & “), ”
    Axis1 = Axis1 & sMonthEnding & “} * ” & sMember

    Case “FY”
    sMeasure = “[Measures].[IMS \" & Trim(params(\"Value\").Value & \" YTD \" & sPeriodTypeShort) & \"]”
    Axis0 = sMeasure
    Axis1 = “NON EMPTY {PARALLELPERIOD(” & sTimePeriod & “,4,” & sMonthEnding & “), ”
    Axis1 = Axis1 & “PARALLELPERIOD(” & sTimePeriod & “,3,” & sMonthEnding & “), ”
    Axis1 = Axis1 & “PARALLELPERIOD(” & sTimePeriod & “,2,” & sMonthEnding & “), ”
    Axis1 = Axis1 & “PARALLELPERIOD(” & sTimePeriod & “,1,” & sMonthEnding & “), ”
    Axis1 = Axis1 & sMonthEnding & “} * ” & sMember

    Case “MAT”
    sMeasure = “[Measures].[IMS \" & Trim(params(\"Value\").Value & \" MAT \" & sPeriodTypeShort) & \"]”
    Axis0 = sMeasure
    Axis1 = “NON EMPTY {PARALLELPERIOD(” & sTimePeriod & “,48,” & sMonthEnding & “), ”
    Axis1 = Axis1 & “PARALLELPERIOD(” & sTimePeriod & “,36,” & sMonthEnding & “), ”
    Axis1 = Axis1 & “PARALLELPERIOD(” & sTimePeriod & “,24,” & sMonthEnding & “), ”
    Axis1 = Axis1 & “PARALLELPERIOD(” & sTimePeriod & “,12,” & sMonthEnding & “), ”
    Axis1 = Axis1 & sMonthEnding & “} * ” & sMember

    Case “RMAT”
    sMeasure = “[Measures].[IMS \" & Trim(params(\"Value\").Value & \" MAT \" & sPeriodTypeShort) & \"]”
    Axis0 = sMeasure
    Axis1 = “NON EMPTY {PARALLELPERIOD(” & sTimePeriod & “,23,” & sMonthEnding & “):” & sMonthEnding & “} * ” & sMember

    End Select

    Axis0 = Axis0 & ” ON COLUMNS, ”
    Axis1 = Axis1 & ” DIMENSION PROPERTIES MEMBER_NAME, MEMBER_CAPTION, MEMBER_VALUE, MEMBER_UNIQUE_NAME ON ROWS ”
    DBSelect = “SELECT CAST(” & Chr(34) & sTimePeriod & “.[MEMBER_CAPTION]” & Chr(34) & ” AS NVARCHAR(MAX)) AS TimePeriod”
    DBSelect = DBSelect & “, CAST(” & Chr(34) & sMember & “.[MEMBER_VALUE]” & Chr(34) & ” AS NVARCHAR(MAX)) AS Member”
    DBSelect = DBSelect & “, CAST(” & Chr(34) & sMeasure & Chr(34) & ” AS NVARCHAR(MAX)) AS Measure ”
    DBSelect = DBSelect & “FROM OpenRowset(” & sConn
    DBSelect = DBSelect & “, ‘ SELECT ” & Axis0
    DBSelect = DBSelect & Axis1
    DBSelect = DBSelect & “FROM ( SELECT ( STRTOSET(” & Chr(34) & “{” & JOIN(params(“Countries”).Value, “,”) & “}” & Chr(34) & “, CONSTRAINED) ) ON COLUMNS ”
    DBSelect = DBSelect & “FROM ( SELECT ( STRTOSET(” & Chr(34) & “{” & JOIN(params(“Regions”).Value, “,”) & “}” & Chr(34) & “, CONSTRAINED) ) ON COLUMNS ”
    DBSelect = DBSelect & “FROM ( SELECT ( STRTOSET(” & Chr(34) & “{” & JOIN(params(“TherapeuticClasses”).Value, “,”) & “}” & Chr(34) & “, CONSTRAINED) ) ON COLUMNS ”
    DBSelect = DBSelect & “FROM ( SELECT ( STRTOSET(” & Chr(34) & “{” & JOIN(params(“TherapeuticClassGroups”).Value, “,”) & “}” & Chr(34) & “, CONSTRAINED) ) ON COLUMNS ”
    DBSelect = DBSelect & “FROM ( SELECT ( STRTOSET(” & Chr(34) & “{” & JOIN(params(“ProductMarkets”).Value, “,”) & “}” & Chr(34) & “, CONSTRAINED) ) ON COLUMNS ”
    DBSelect = DBSelect & “FROM ( SELECT ( STRTOSET(” & Chr(34) & “{” & JOIN(params(“IMSMolecules”).Value, “,”) & “}” & Chr(34) & “, CONSTRAINED) ) ON COLUMNS ”
    DBSelect = DBSelect & “FROM [IMS Market Base])))))) ”
    DBSelect = DBSelect & “WHERE ( IIF( STRTOSET(” & Chr(34) & “{” & JOIN(params(“IMSMolecules”).Value, “,”) & “}” & Chr(34) & “, CONSTRAINED).Count = 1, ”
    DBSelect = DBSelect & “STRTOSET(” & Chr(34) & “{” & JOIN(params(“IMSMolecules”).Value, “,”) & “}” & Chr(34) & “, CONSTRAINED), [IMS Molecules].[IMS Molecules].currentmember ), ”
    DBSelect = DBSelect & “IIF( STRTOSET(” & Chr(34) & “{” & JOIN(params(“ProductMarkets”).Value, “,”) & “}” & Chr(34) & “, CONSTRAINED).Count = 1, ”
    DBSelect = DBSelect & “STRTOSET(” & Chr(34) & “{” & JOIN(params(“ProductMarkets”).Value, “,”) & “}” & Chr(34) & “, CONSTRAINED), [Product Markets].[Product Markets].currentmember ), ”
    DBSelect = DBSelect & “IIF( STRTOSET(” & Chr(34) & “{” & JOIN(params(“TherapeuticClassGroups”).Value, “,”) & “}” & Chr(34) & “, CONSTRAINED).Count = 1, ”
    DBSelect = DBSelect & “STRTOSET(” & Chr(34) & “{” & JOIN(params(“TherapeuticClassGroups”).Value, “,”) & “}” & Chr(34) & “, CONSTRAINED), [IMS Products].[Therapeutic Class Groups].currentmember ), ”
    DBSelect = DBSelect & “IIF( STRTOSET(” & Chr(34) & “{” & JOIN(params(“TherapeuticClasses”).Value, “,”) & “}” & Chr(34) & “, CONSTRAINED).Count = 1, ”
    DBSelect = DBSelect & “STRTOSET(” & Chr(34) & “{” & JOIN(params(“TherapeuticClasses”).Value, “,”) & “}” & Chr(34) & “, CONSTRAINED), [IMS Products].[IMS Therapeutic Classes].currentmember ), ”
    DBSelect = DBSelect & “IIF( STRTOSET(” & Chr(34) & “{” & JOIN(params(“Countries”).Value, “,”) & “}” & Chr(34) & “, CONSTRAINED).Count = 1, ”
    DBSelect = DBSelect & “STRTOSET(” & Chr(34) & “{” & JOIN(params(“Countries”).Value, “,”) & “}” & Chr(34) & “, CONSTRAINED), [IMS Products].[Countries].currentmember ) ) ”
    DBSelect = DBSelect & “CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS;’)”

    Return DBSelect

    End Function

    This works for the most part. I do get the following error sometimes:

    Msg 7341, Level 16, State 2, Line 1
    Cannot get the current row value of column “[MSOLAP.3].[IMS Products].[IMS Products].[IMS Products].[MEMBER_UNIQUE_NAME]” from OLE DB provider “MSOLAP.3″ for linked server “(null)”. Could not convert the data value due to reasons other than sign mismatch or overflow.

  • Alex:

    Hi Edson

    That’s a great example of the power of this method – thanks for posting. (If anyone wants to examine the code, I’d suggest copying and pasting into notepad – my blog format makes it a little tricky to follow!).

    Your post does highlight one of the problems of having to use this method, debugging is difficult, however it can be done.

    Your best option for debugging the problem is to analyse the MDX query results outside of the OpenRowset function. Make a copy of the report and then delete the offending dataset and report items. Create a new textbox with
    =Code.CreateMDX(Parameters)
    Then run the report and just copy and paste the result into Management Studio.

    I’m not sure what would cause the error you mentioned, but looking at the contents of the MDX results should give you a pointer in the right direction.

    Alex

  • Alex:

    Hi Chris (delayed response from Feb 23 – sorry!)

    I’m intrigued how you go about creating an MDX query using an expression – can you explain how you do it? Have I missed an obvious slution?! :-)

    Thanks
    Alex

  • Edson Crevecoeur:

    I fixed my issue. I think the problem is the [IMS Products] attribute column name is stored as WCHAR in the Dimension, but the MSOLAP.3 provider seems to have trouble with long column names. I created a calculated member which applied the MEMBERTOSTR function to the [IMS Products] attributes name property. Here’s the new fixed generated mdx code:

    SELECT CAST(“[IMS Products].[IMS Products].[IMS Products].[MEMBER_KEY]” AS VARCHAR(MAX)) AS MemberKey,
    CAST(“[Time Periods].[Calendar Hierarchy].[Calendar Year].[MEMBER_CAPTION]” AS NVARCHAR(MAX)) AS TimePeriod,
    CAST(“[Measures].[IMS Products]” AS NVARCHAR(MAX)) AS MemberName,
    CAST(“[Measures].[IMS USD YTD (CY)]” AS NVARCHAR(MAX)) AS Measure
    FROM OpenRowset(‘MSOLAP’,’DATA SOURCE=XXXX; Initial Catalog=XXXX;’, ‘ WITH MEMBER [Measures].[IMS Products] AS ”[IMS Products].[IMS Products].CURRENTMEMBER.PROPERTIES(“Name”)” SELECT {[Measures].[IMS Products],[Measures].[IMS USD YTD (CY)]} ON COLUMNS, NON EMPTY {[IMS Products].[IMS Products].[IMS Products]}*{PARALLELPERIOD([Time Periods].[Calendar Hierarchy].[Calendar Year],4,[Time Periods].[Calendar Hierarchy].[Calendar Month].&[200809].PARENT.PARENT), PARALLELPERIOD([Time Periods].[Calendar Hierarchy].[Calendar Year],3,[Time Periods].[Calendar Hierarchy].[Calendar Month].&[200809].PARENT.PARENT), PARALLELPERIOD([Time Periods].[Calendar Hierarchy].[Calendar Year],2,[Time Periods].[Calendar Hierarchy].[Calendar Month].&[200809].PARENT.PARENT), PARALLELPERIOD([Time Periods].[Calendar Hierarchy].[Calendar Year],1,[Time Periods].[Calendar Hierarchy].[Calendar Month].&[200809].PARENT.PARENT), [Time Periods].[Calendar Hierarchy].[Calendar Month].&[200809].PARENT.PARENT} DIMENSION PROPERTIES MEMBER_KEY, MEMBER_CAPTION ON ROWS FROM ( SELECT ( STRTOSET(“{[IMS Products].[Countries].[All]}”, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(“{[IMS Products].[Regions].&[4]}”, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(“{[IMS Products].[Therapeutic Classes].[All]}”, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(“{[IMS Products].[Therapeutic Class Groups].&[2]}”, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(“{[Product Markets].[Product Markets].[All]}”, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(“{[IMS Molecules].[IMS Molecules].[All]}”, CONSTRAINED) ) ON COLUMNS FROM [IMS Market Base])))))) WHERE ( IIF( STRTOSET(“{[IMS Molecules].[IMS Molecules].[All]}”, CONSTRAINED).Count = 1, STRTOSET(“{[IMS Molecules].[IMS Molecules].[All]}”, CONSTRAINED), [IMS Molecules].[IMS Molecules].currentmember ), IIF( STRTOSET(“{[Product Markets].[Product Markets].[All]}”, CONSTRAINED).Count = 1, STRTOSET(“{[Product Markets].[Product Markets].[All]}”, CONSTRAINED), [Product Markets].[Product Markets].currentmember ), IIF( STRTOSET(“{[IMS Products].[Therapeutic Class Groups].&[2]}”, CONSTRAINED).Count = 1, STRTOSET(“{[IMS Products].[Therapeutic Class Groups].&[2]}”, CONSTRAINED), [IMS Products].[Therapeutic Class Groups].currentmember ), IIF( STRTOSET(“{[IMS Products].[Therapeutic Classes].[All]}”, CONSTRAINED).Count = 1, STRTOSET(“{[IMS Products].[Therapeutic Classes].[All]}”, CONSTRAINED), [IMS Products].[IMS Therapeutic Classes].currentmember ), IIF( STRTOSET(“{[IMS Products].[Countries].[All]}”, CONSTRAINED).Count = 1, STRTOSET(“{[IMS Products].[Countries].[All]}”, CONSTRAINED), [IMS Products].[Countries].currentmember ) ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS;’);

  • Alex:

    Hi Edson

    Glad you got it working, thanks for posting the update – much appreciated.

  • Jayesh:

    Hi Alex,

    Nice post !

    Can we achieve the same in SQL?
    Any pros/cons for the same?

    Thanks again for great post !

    Cheers,
    Jayesh

  • Jayesh:

    hey,

    I mean the string handling operations?

    -Jayesh

  • Alex:

    Hi Jayesh

    Yes, you can do exactly the same with SQL, however it’s more simple because you don’t need to use OpenRowset – you just build up your SQL dynamically.

    You can do this in the query editor without using custom code by just using somthing like this
    =”SELECT * FROM ” & Parameters!FromTable.Value

    Or, if you want it in custom code for clarity then follow the same method as MDX, just leave out the OpenRowset call as you don’t need it.

    Hope this helps

    Alex

  • Jayesh:

    Hey alex,

    Is there any way to handle no rows condition?

    I need to have error handling if no rows are returned.

    Thanks in advance,

    Jayesh

  • Alex:

    Hi Jayesh

    Can you explain what you mean? If there are no rows what do you want to happen? What is happening in your case? Is this with SQL or MDX? If SQL, using OpenRowset or not?

    Thanks

    Alex

  • Quinn Nguyen:

    Thanks so much! This is EXACTLY what I am looking for! I was thinking of doing the dynamic MDX query this way but wasn’t sure if anyone else is doing the same thing. I’m glad to see that I’m not crazy.. These sample codes really save me a lot of time.

  • 안경면:

    Thanks
    Alex

  • karl:

    i made createmdx function but…
    do you know error:
    OLE DB provider “MSOLAP” for linked server “(null)” returned message .access denied ??

  • Alex:

    Hi Karl

    Have you enabled OpenRowset and OpenDataSource in the SQL Server Surface Area Configuration tool (for features)?

    Alternatively, it can be caused by querying across different versions of SQL (http://support.microsoft.com/kb/947512).

    Hope this helps
    Alex

  • karl:

    I do not know what is happening.
    i have enabled openrowset in the sql surface.
    i have the same versions of sql server: analysis services 9.0 and sql server 2005

    i exec : sp_configure ‘show advanced options’, 1
    reconfigure
    RECONFIGURE
    sp_configure ‘Ad Hoc Distributed Queries’, 1 RECONFIGURE
    .

    my createmdx is :

    Public Function CreateMDX() as string

    Dim mdx as string

    mdx = “SELECT * FROM OpenRowset(”
    mdx += ” ‘MSOLAP’, ”
    mdx += ” ‘DATA SOURCE=W-ANALISIs; Initial Catalog=myanalysis’, ”
    mdx += ” ‘SELECT {[Measures].[qty]} ON 0, ”
    mdx += ” NON EMPTY { [Olap].[Empresa].[Empresa].[MEMBER_CAPTION] as grupoA * [Olap Analisis Planta Dw].[Proyecto].[Proyecto].[MEMBER_CAPTION] AS grupoB } ON 1 ”
    mdx += ” FROM [mycube] ‘ ”
    mdx += “)”

    return mdx
    End Function

    thanks

  • Alex:

    Try replacing MSOLAP with MSOLAP.3

    It could be that the wrong MSOLAP provider is being used by default.

  • karl:

    hi alex,

    the same error:

    cannot read the next data row for the data set mydataset.
    OLE DB provider “MSOLAP.3” for linked server “(null)” returned message . Access denied

    i have the sqlserver 2005 sp3 , not sp2

    Can it be that the error is another cause?

  • karl:

    ok I’ve got it.

    But now i have another problem on the report. in the cell of matrix i write =sum(fields!medida.value), and when i esecute report error message appears into the cell (# errror) , but if I remove the word ‘sum’ there is not error , but the result not the correct.

    my code :

    Public Function CreateMDX(ByVal params as Parameters) as string
    Dim mdx as string
    ….
    ….
    …..
    mdx +=”SELECT “”[Empresa].[Empresa].[MEMBER_CAPTION]“” AS group1 ”

    mdx += “, “”[Measures].[qty]“” as medida FROM OpenRowset( ‘MSOLAP’, ‘…………………”
    …..
    ….

  • Alex:

    Could you post how you solved the problem?

    The next problem is probably caused by the value being returned as a string. Cast it to a number before applying sum. That should sor it out.

  • karl:

    i solved the problem, Checked access rights of the user which is used to create the db connection.
    it was problem of my user.

  • karl:

    i think now is my last problem of all this . i hope.

    the things that is behind the ‘where’.

    I have 2 parameter(tiempo and project). it is a date day and name project

    ……FROM [cube]
    where ( [Time].[Date].&[2009-10-01T00:00:00]) ,
    ,([Project].&[one])

    i have tried…
    t=params(“tiempo”).value.ToString()
    p=params(“project”).value.ToString()

    mdx += ” …. FROM [ANALISIS DE PLANTA] WHERE ([Time].[Date].&[\" + t + \"]“) ,([Project].&[\" + p + \"]“)

    but fall in the query.

    any idea??

  • Alex:

    Have you checked that the variables do actually contain the exact value of the attribute key?

    Try making a copy of the report, then delete the dataset from the copy. Then put a large textbox on the report which just shows the contents of the mdx query you are trying to run. You can then copy that query into SSMS to get a more helpful error message.

  • Nulled Scripts:

    Excellent post..Keep them coming :) Thanks for sharing.

  • Naveen Das:

    Alex

    You CAN use expressions in the underlying MDX query. I have used it with success, example as under:

    =”SELECT [Measures].[Internet Sales Amount] on 0,TOPCOUNT( [Customer].[State-Province].CHILDREN,”+cstr(Parameters!ParameterN.Value)+ “,[Internet Sales Amount]) ON 1 FROM [Adventure Works]“

  • Muthu Vijayans:

    Hi thanks for giving the ideas. am looking for Mdx analyser, formatter and Parser.

    is there any links to check plz help.

    http://www.dotnetpgm.co.cc

The Frog Blog

I'm Alex Whittles.

I specialise in designing and implementing SQL Server business intelligence solutions, and this is my blog! Just a collection of thoughts, techniques and ramblings on SQL Server, Cubes, Data Warehouses, MDX, DAX and whatever else comes to mind.

I'm Organising SQL Relay 2014

Submit a session for SQLBits

Frog Blog Out
twitter
rssicon