Combining MDX and SQL datasets into one for a drill-through report??

Question

Hello,

Wondering if I could get some advice and direction on this following requirement:

Need to Create a SSRS report with two datasets, one MDX and one SQL. I then need to join those two datasets to create a third dataset which is to be used by a drill though report.

How can I combine those datasets into one and use that as a Dataset for a drill-through report?

Thanks!!

Answer 1

You can use a T-SQL stored procedure to combine the two datasets. It can't be done in the report itself. Call the stored procedure from the report to get the third result set that you need for the drillthrough report. Inside the stored procedure, you can use call an MDX query by using the OPENQUERY function. You'll have to set up a linked server on the SQL Server box that uses an Analysis Services provider, like this:

EXEC master.dbo.sp_addlinkedserver @server = N'AW2008', @srvproduct=N'SSAS', @provider=N'MSOLAP', @datasrc=N'', @catalog=N'Adventure Works 2008'

You can then wrap an MDX query in an OPENQUERY function, and select "columnns" from the function by referencing the column in double-quotes - like this:

select "[Measures].[Sales Amount]" from OPENQUERY(AW2008, 'select [Measures].[Sales Amount] on columns from [Adventure Works]')