5 Replies Latest reply on Jun 9, 2015 5:58 AM by philmodjunk

    Data from Table A needs to be reorganised and placed into Table B

    Stu412

      Title

      Data from Table A needs to be reorganised and placed into Table B

      Post

      It seems odd in a relational DB to have to do what I'm suggesting in the title, so I hope there's no need for this, but needs must.

      Table A has sales data in it, organised by customer, type of sale and year of sale, each on a single record because that's how it's presented at import:

      CustomerID | SaleType | Year | Value

      1010           | Direct      | 2014 | 1000

      1010           | Indirect   | 2014 | 800

      1010           | Direct      | 2013 | 900

      1010           | Indirect    | 2013 |750

      1011           | Direct      | 2014 | 1500

      1011           | Direct      | 2013 | 750

      1011           | Inirect      | 2013 | 500

      As you can see, all customers exist on this table and year on year, a sales type may or may not be present, as is the case with customer ID 2011.  This is quite relevant.

      I need to get this data from Table A into a format on a list report where I can show the a single row for a sales type with multiple columns for the years and the values within each:

      Customer 1010                   2014  |  2013

      ------------------------------------------------------------

      Direct Sales                       1000  |    900

      Indirect Sales                      800   |    750

       

      Customer 1011

      -------------------------------------------------------------

      Direct Sales                       1500   |    750

      Indirect Sales                     NIL     |    500

      If this were Excel, I'd simply pivot it.  It's important to show all the details so that people can see there are no Indirect Sales to Customer 1011 in 2014.

      Thanks

       

        • 1. Re: Data from Table A needs to be reorganised and placed into Table B
          philmodjunk

          You've got the right idea. We usually call this a "cross tab" report. There are several different ways to get the data into the needed columns. The first step, though is to get the rows of data that you need where multiple rows of data are condensed into 1 or 2 rows of data per customer ID. This can be done by setting up a summary report with a subsummary part (when sorted by CustomerID and a second subsummary part when sorted by SaleType. This, combined with sorting by customerID then aby SaleType will produce your customer ID sub header and the one or two rows per customer with the first column of data showing the Sale Type..

          The next part the process can be set up a number of different ways. The most common methods use either a series of (usually filtered) one row portals or a series of calculation fields using ExecuteSQL() to compute and display the needed subtotals by type and year. If using portals, summary fields from the portal's are placed inside the portals to display the needed sub totals. The relationships would be based on a self join that match records at least by customer ID, but which can also match by sales type and year. (or year can be specified in a portal filter so that you don't have to create a different table occurrence for each subsequent column of data.)

          This can be set up so that you specify the year for either the first or last column of data in a global field so that you can adjust what data appears in these columns as time passes or just to look at past historical data.

          • 2. Re: Data from Table A needs to be reorganised and placed into Table B
            Stu412

            Hi Phil,

            Thanks for this - now I know what they're called(!)

            I've managed to get the rows of data to appear on the summary report, per customer.  

            The trick now is to use portals to summarise the data.  I guess I can self join to create further TO's year on year, and pull into a portal (in a column), the relevant data::Value field?

             

             

             

             

            • 3. Re: Data from Table A needs to be reorganised and placed into Table B
              philmodjunk

              With a different portal filter filtering for a different year, you only need one table occurrence, but either approach can be set up to work.

              • 4. Re: Data from Table A needs to be reorganised and placed into Table B
                Stu412

                What I've been able to do is set up calculation fields which create further one year only columns on the data table for values in a particular year.  These split one column of annual data to several:

                RowTotal2014 = If (Year=2014;Value) 

                Then I've done a sub summary report with a group based on the sales type with the sales description and summary values per year across the row:

                sRowTotal2014 = (Summary of RowTotal2014)

                Where I've needed percentages of the row total against an overriding total sales figure, I've wrapped the whole thing up in a GetSummary on the sales type break field:

                RowPerc = GetSummary(sRowTotal2014;SaleType)/TotalSales (a figure derived elsewhere on the table)

                Thanks for your help on this Phil

                 

                • 5. Re: Data from Table A needs to be reorganised and placed into Table B
                  philmodjunk

                  I would not have set it up this way. Each time you want to add a new year, you'll have to redefine calculation fields to include it in the report.