SSRS combining MDX and T-SQL +

Question

Hello, I have request to create simple REPORT ACTION on my cube, where will be shown ORDER DETAILS. In excel will be shown cumulated values, and if there will be low customer service rate our managers needs to dig into detail, which is very simple. But they want to see a lot of attributes of sold materials which are not in cube as I didnt want put everything inside. For example Product has approximately 20 attributes and for only one analysis I need to report them about 60, which are in my eyes not good adepts for cubing. Is it possible to create report where will be combined result of cube query + result of T-SQL query ?

 

thanks

Answer 1

Hi Volyn

You can achive this by Hybrid SQL (SQL+MDX). See the below query Get the MDX Result in ##TestTemp table then after that you can use this temp table result in SQL Query or join the result with other table.

It's totally depend's on your requirement you can select Id from MDX query to Join with SQL Table.

EXEC sp_addlinkedserver
@server='TestTest',
@srvproduct='',
@provider='MSOLAP',
@datasrc='localhost',
@catalog='CapitalManager' 

Declare @MDXExpression as Varchar(MAX)
Select @MDXExpression = '
      SELECT
      NON EMPTY
       {
       [Measures].[Economic Capital Amount],
       [Measures].[Diversified Capital Amount],
       [Measures].[Standalone Capital Amount],
       [Measures].[Diversification Benefit]
       } ON COLUMNS,
      NON EMPTY
       {
       [Time].[Time Hierarchy].[Day of Month]
       } ON ROWS
      FROM
       [Summary]
      Where
         (
         [Scenarios].[Scenarios Hierarchy].[Base Scenario] 
         )
      ';      
Exec ('SELECT * INTO ##TestTemp FROM OpenQuery(TestTest,''' + @MDXExpression + ''')')

SELECT
 CONVERT(varchar,t."[Time].[Time Hierarchy].[Day of Month].[MEMBER_CAPTION]") AS [Date],
 (CONVERT(nvarchar,t."[Measures].[Economic Capital Amount]")) AS [Economic Capital Amount],
 (CONVERT(nvarchar,t."[Measures].[Diversified Capital Amount]")) AS [Diversified Capital Amount],
 (CONVERT(nvarchar,t."[Measures].[Standalone Capital Amount]")) AS [Standalone Capital Amount],
 (CONVERT(nvarchar,t."[Measures].[Diversification Benefit]")) AS [Diversification Benefit]
from
 ##TestTemp t

Drop table ##TestTemp;

Suhas | MCP | MCTS

---

Mark as Answer if this resolves your problem or "Vote as Helpful" if you find it helpful.

Answer 2

Hi Volyn,

Besides Suhas R K's advise, you can also consider Reporting Action, which enables information consumers to fire a SQL Server Report when a specific condition is met while browsing the cube, you can create the report with a parameter using T-SQL, for detail steps please refer to this link http://bennyaustin.wordpress.com/2009/07/19/reporting-action/ 

Thanks,
Challen Fu

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 3

SSRS allows you to select data from different sources, for example sql server and ssas. It is possible to "combine" two datasets in SSRS 2008 R2. By using new lookup functions you can lookup values from a different dataset. If this is not, what you need, you could use OPENQUERY + linked server. But then your MDX query will be limited to 8000 char.

Answer 4

Thank you all, I will try also lookup function and openquery.

 

Thanks again