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 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,
![]() |
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
![]() |
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.
![]() |
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.
![]() |
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
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.
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..?
Fantastic post! An example for the comunity!!! I will check you example right now in my laptop!
Thanks for the post!!!
Cheers Alex!!
Glad you enjoyed SQLBits! I’m pleased it’s all over though, yes.
Nice post u have here π Added to my RSS reader
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!
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 π
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.
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
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
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;’);
Hi Edson
Glad you got it working, thanks for posting the update – much appreciated.
Hi Alex,
Nice post !
Can we achieve the same in SQL?
Any pros/cons for the same?
Thanks again for great post !
Cheers,
Jayesh
hey,
I mean the string handling operations?
-Jayesh
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
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
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
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
i made createmdx function but…
do you know error:
OLE DB provider “MSOLAP” for linked server “(null)” returned message .access denied ??
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
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
Try replacing MSOLAP with MSOLAP.3
It could be that the wrong MSOLAP provider is being used by default.
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?
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’, ‘…………………”
…..
….
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.
i solved the problem, Checked access rights of the user which is used to create the db connection.
it was problem of my user.
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??
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.
Excellent post..Keep them coming π Thanks for sharing.
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]”
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