2 Replies Latest reply on Oct 11, 2018 2:44 AM by nicolaesse

    Form where user insert parameter to SQL Server Stored Procedure

    nicolaesse

      I would like to make a very simple report in Tableau Desktop to publish on Tableau Server, that is a table of the output of the following Microsoft SQL Server Stored Procedure:

       

      CREATE PROCEDURE [dbo].[ZZZ_SPTestTableau_01]

      @ThisDay AS varchar(10)

       

      AS

      BEGIN

      SET NOCOUNT ON;

       

      SELECT [Col1],Col2],[DateCol],[Temp]

      FROM [MyDB].[dbo].[MyTable]

      WHERE [DateCol]=@ThisDay

      END

       

      @ThisDay can assume values like '2018-10-10', '2018-10-09',...

       

      When I select the SP as data source in Tableau Desktop, I have to choose the exact value of the parameter @ThisDay.

       

      My objective is this:

      1. User open the workbook in Tableau Server;
      2. Tableau Server show a form the the user can choose the value of the parameter @ThisDay (ideally from a list);
      3. The SP is executed with the parameter selected at point 2;
      4. Tableau Server show the results of the SP.

       

      Is this possibile with Tableau?

       

      PS: I have already read this thread, but it doen't help at all. The selected answer is just a link to the page where are explaned the basics of the usage of SP in Tableau, it doen't tell anything about how to pass a parameter selected by the user.

        • 1. Re: Form where user insert parameter to SQL Server Stored Procedure
          Sankarmagesh Rajan

          Hi

          once sp connected then select convert to custom sql from data menu and add parameter in custom sql window

          Insert Parameter

           

          Thanks

          sankar

          1 of 1 people found this helpful
          • 2. Re: Form where user insert parameter to SQL Server Stored Procedure
            nicolaesse

            Thanks Sankarmagesh for your help.

            If I understand, custom SQL is available only with tables (not with stored procedure) so, in this way I should follow this steps.

            • Edit the SP like the following

            ALTER PROCEDURE [dbo].[ZZZ_SPTestTableau_01]

            @ThisDay AS varchar(10)

             

            AS

            BEGIN

            SET NOCOUNT ON;

             

            INSERT INTO [OutputTable]([Col1],Col2],[DateCol],[Temp])

            SELECT [Col1],Col2],[DateCol],[Temp]

            FROM [MyDB].[dbo].[MyTable]

            WHERE [DateCol]=@ThisDay

            END.

             

            • Add as data source SP [ZZZ_SPTestTableau_01] as datasource with fixed value of @ThisDay
            • Add as data source table [OutputTable]
            • Customize the SQL code of the select from [OutputTable], but this is not necessary as the SP run only with fix value of @ThisDay, so I don't have to filter anything.

            Is it true? so I am not passing any variable parameter to the SP?

             

            Thank you very much for you help!