3 Replies Latest reply on Aug 31, 2016 4:42 AM by mohamedferozkhan.m

    Cross Tab Report from column Values

    mohamedferozkhan.m

      Hi,

       

      I am having data like below, from this need to create the following Report. Please suggest.

       

      Data

       

       

      Note : For Understanding have given column name as "Current Account_CQ_ACC" etc but from the table structure it will be different like cur_acc1.

       

       

      Report Format

       

      Have to convert Current Account and Margin account in to Dimension in Report. Please suggest.

        • 1. Re: Cross Tab Report from column Values
          Mason Forando

          Hi Mohamed,

           

          Based on your screenshots I am assuming your data source is an excel file.  With excel files, you can use custom SQL on the way in to accomplish your goals.  See this article for a full explanation:

           

          https://www.interworks.com/blog/kfontenot/2014/07/24/custom-sql-tableau-82

           

          In order to use Custom SQL, you would need to select the 'Use Legacy Connection' option when opening the excel file initially in tableau.  Then, there should be an option under Data that says 'Convert to custom SQL.'  Click that.

           

          The custom SQL should look something like this:

           

          SELECT

            [Sheet1$].[CurrentAccount A] AS [Current Account A],

            [Sheet1$].[CurrentAccount B] AS [Current Account B],

            [Sheet1$].[CurrentAccount C] AS [Current Account C],

            [Sheet1$].[Entity] AS [Entity],

            [Sheet1$].[MarginAccount A] AS [Margin Account A],

            [Sheet1$].[MarginAccount B] AS [Margin Account B],

            [Sheet1$].[MarginAccount C] AS [Margin Account C]

          FROM [Sheet1$]

           

          You can rewrite this to be something like this:

           

          SELECT
            'Current' as Account_Type,
            [Sheet1$].[Current Account A] AS [Account A],
            [Sheet1$].[Current Account B] AS [Account B],
            [Sheet1$].[Current Account C] AS [Account C],
            [Sheet1$].[Entity] AS [Entity]
          FROM [Sheet1$]

          UNION

          SELECT
            'Margin' as Account_Type,
            [Sheet1$].[Margin Account A] AS [Account A],
            [Sheet1$].[Margin Account B] AS [Account B],
            [Sheet1$].[Margin Account C] AS [Account C],
            [Sheet1$].[Entity] AS [Entity]
          FROM [Sheet1$]

           

          A sample workbook is here:

           

          https://public.tableau.com/static/images/Cu/CustomSQLExample/Story1/1.png

           

          Thanks,

          Mason

          • 2. Re: Cross Tab Report from column Values
            Mason Forando

            Also, another option available is the 'Pivot' functionality in Tableau (also assumes an excel data source). 

             

            Pivot Data from Columns to Rows

             

            This would put the table in a long format, and you could parse out the type of account (Margin vs. Current) as well as the IDs (i.e. PQ_ACC) using a basic calculated field.

            • 3. Re: Cross Tab Report from column Values
              mohamedferozkhan.m

              Thanks Mason.

               

              I have given example as Excel but actual source is a SQL Server. I hope  "View" can be created instead of Custom SQL.  I checked in Excel as source type it is working.

               

              Could you please explain in detail on Pivot option?