1 Reply Latest reply on Dec 19, 2016 6:03 AM by Tom W

    CUSTOM SQL QUERY ISSUE

    MATHEW AUGUSTINE

      Hi,

       

      I am using a Custom SQL query to fetch data from a linked Server. When I give hardcoded values for where condition, the query is getting executed.

       

      Select * from OpenQuery( ESESSMW3498DW,' SELECT NON EMPTY { [Measures].[Unit Price] } ON COLUMNS, NON EMPTY { ([Dim Service Scenario].[Service Scenario Title].[Service Scenario Title].ALLMEMBERS * [Dim Fire Code].[Fire Code].[Fire Code].ALLMEMBERS) } DIMENSION PROPERTIES MEMBER_CAPTION ON ROWS FROM ( SELECT ( Filter( [Dim Service Scenario].[Service Scenario Title].[Service Scenario Title].ALLMEMBERS, Instr( [Dim Service Scenario].[Service Scenario Title].currentmember.Properties( ''Member_Caption'' ), ''AAAAA Scenario'' )  > 0  ) ) ON COLUMNS FROM [CASTDW])' )

       

      I have created a parameter called ScenarioID which has a list of values.

       

      I want to use that parameter in the where condition. But its showing a syntax error.

       

      Select * from OpenQuery( ESESSMW3498DW,' SELECT NON EMPTY { [Measures].[Unit Price] } ON COLUMNS, NON EMPTY { ([Dim Service Scenario].[Service Scenario Title].[Service Scenario Title].ALLMEMBERS * [Dim Fire Code].[Fire Code].[Fire Code].ALLMEMBERS) } DIMENSION PROPERTIES MEMBER_CAPTION ON ROWS FROM ( SELECT ( Filter( [Dim Service Scenario].[Service Scenario Title].[Service Scenario Title].ALLMEMBERS, Instr( [Dim Service Scenario].[Service Scenario Title].currentmember.Properties( ''Member_Caption'' ), ScenarioID )  > 0  ) ) ON COLUMNS FROM [CASTDW])' )

       

       

      If you have any idea on the syntax to apply parameter to this Custom SQL query,please help.

       

      Regards,

      Mathew Augustine