1 Reply Latest reply on Jun 2, 2018 3:47 AM by Samane Ramezani

    Using Tableau Parameters in MDX and Connecting to SQL through Linked Server in Tableau Desktop

    Samane Ramezani

      Hi All

      I used Custom SQL in Tableau Desktop to connect to Cube

      in this Custom SQL I define Linked Server and my MDX Query as like as below:

      Select *

      from openrowset ('***',Data source=localhost;initial catalog=***;Provider=*** integrated Security=***;',

      'with member x as sum([measures].[initbal]*{[BasicDate].[dates].&[20180421]}) select non empty {x} on columns,

      non empty {[Basic Branch].[Parent Branch].children * [Basic Branch].[Branch].children } on rows

      from [DW]

      ')

      it works Correctly

      ********************************************************************************************************************************************************

      But when I want to use Tableau string Parameter(in our cube dates datatype is string) in MDX I get syntax Error  

      I used Parameter as like as below :

       

      Select * from openrowset ('***',Data source=localhost;initial catalog=***;Provider=*** integrated Security=***;',

      'with member x as sum([measures].[initbal]*{[BasicDate].[dates].&[<Parameters.date_param>]}) select non empty {x} on columns,

      non empty {[Basic Branch].[Parent Branch].children * [Basic Branch].[Branch].children } on rows

      from [DW]')

       

      or like this :

       

      Select * from openrowset ('***',Data source=localhost;initial catalog=***;Provider=*** integrated Security=***;',

      'with member x as sum([measures].[initbal]*{strtomember('[BasicDate].[dates].&['+<Parameters.date_param>+']')}) select non empty {x} on columns,

      non empty {[Basic Branch].[Parent Branch].children * [Basic Branch].[Branch].children } on rows

      from [DW]

      ')

       

      how can I use Tableau parameters in this query

      appreciate any help

      thanks

      Samane