4 Replies Latest reply on Mar 2, 2017 2:09 AM by Shuo Yan

    How to generate worksheets in batch

    Shuo Yan

      Hello everyone,

       

      I just transferred from Crystal Report + VS to Tableau, and I wonder how can I generate a batch of charts / worksheets as in Crystal Report.

       

      This idea is that if we have MS SQL Server as data source, and we need to do analysis on multiple databases at the same time. In Crystal report, I could design a report so that one Bar Chart + one table will be generated for each database. This process is automatic as the report template is there. However, I don't find the same function in Tableau. It seems that I have to create sheet after sheet. Then, should I have 20 databases to analyze, do I have to manually create a worksheet for each?

       

      Thanks for all the help in advance.

        • 1. Re: How to generate worksheets in batch
          Chris McClellan

          Do you want to (A) have access to all the databases in a single workbook, or (B) have a workbook for each database ?

           

          (A) is probably handled best with some calculated fields to reference each database

           

          (B) possibly handled best by creating a "master workbook" and then making copies and editting the XML to change the connection.  The TWB file is simply an XML so make a backup and you should be able to figure out where to change the connection string to another database.

           

          If it's not either of these two solutions, can you provide more information so we get a better understanding of the environment please ?

          • 2. Re: How to generate worksheets in batch
            Shuo Yan

            Hi Chris,

             

            Thank you for your response. My situation is more like the option A you mentioned.

             

            The basic workflow is that:

             

            1. Connect to MS SQL Server hosting multiple databases;

            2. Customized query on multiple databases;

            3. Then I have a output of the query, which is a data-set X containing data extracted from all related databases;

            4. Suppose the data-set X is extracted from 10 databases hosted in the SQL server, I want to achieve the following task:

             

                Generate a sheet (bar chart + table) of the data-set portion from database 1; then

                do the same to databse2...

                 .

                 .

                 .

                 Until database10.

             

            Actually, the basic idea is to repeat the same design of sheet to 10 different portions of my data-set. It is easy to achieve with Crystal Report + Visual Studio or even in R, Matlab, etc. However, I am not sure if I could do the same thing in Tableau. Especially, should I have 100 databases to analyze, how can I automatize this process in Tableau.

             

            Many thanks.

            • 3. Re: How to generate worksheets in batch
              Chris McClellan

              Hi, I know you said option A, but it sounds like it would be a lot more like Option B.

               

              Basically .... build a workbook that only looks like Database A and get everything working as required.

               

              Do a "save as" on the workbook to save another copy, then edit that copy to look only at Database B

              Rinse and repeat for C, D, E, F, etc...

               

              In the end you do have 10 workbooks, but they all reference different databases.

               

               

               

              If you want to continue with Option A, it might be an idea to create a view that references all the databases and unions the data together (along with creating another column so you can tell which database if required) then use only that view in Tableau.

              • 4. Re: How to generate worksheets in batch
                Shuo Yan

                Hi Chris,

                 

                I've considered your solution. Logically it works indeed, but it will not meet the industrial requirement. As in the productive environment, I would like to explore a automatized way to do this. Otherwise, there is not possible for the average users to carry out their tasks.