none
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

    Wednesday, November 09, 2011 10:12 AM

Answers

  • 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.
    • Proposed as answer by Suhas Kudekar Friday, November 11, 2011 12:28 PM
    • Marked as answer by volyn Saturday, November 12, 2011 12:40 PM
    Wednesday, November 09, 2011 2:54 PM
  • 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.
    • Marked as answer by volyn Saturday, November 12, 2011 12:40 PM
    Friday, November 11, 2011 12:22 PM

All replies

  • 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.
    • Proposed as answer by Suhas Kudekar Friday, November 11, 2011 12:28 PM
    • Marked as answer by volyn Saturday, November 12, 2011 12:40 PM
    Wednesday, November 09, 2011 2:54 PM
  • 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.

    Friday, November 11, 2011 7:24 AM
    Moderator
  • 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.
    • Marked as answer by volyn Saturday, November 12, 2011 12:40 PM
    Friday, November 11, 2011 12:22 PM
  • Thank you all, I will try also lookup function and openquery.

     

    Thanks again

    Saturday, November 12, 2011 12:41 PM