1 Reply Latest reply on Mar 19, 2015 7:09 AM by philmodjunk

    Pivot Table anybody?



      Pivot Table anybody?


      Hi there

      I need to import a lot of data and have this displayed in a relatively small space.  Previously this has taken 5 or 6 Excel sheets to display.

      The data consists of tasks and fees for the tasks.  Each tasks belongs to a group.  There are around 10 groups, and 100 tasks.

      The easiest way to set the table up would be to have the following, which is simple and great for imports:

      CustomerID | GroupName | TaskName | Fee

      This set up lends itself naturally to a list of results, but I really need to display it onscreen differently.  I can create portals, filtered by GroupName.  Within these portals however, I need the TaskNames, the fees and the CustomerID's moving left to right as column headers, not row headers as the table set up would lead to.

      Each customer then has their own row, with the data per fee transposed into the relevant column.

      It's basically an Excel pivot chart that I want FM to do.

      The only alternative I can think of to make this work directly from the table would be to set the table fields to represent the unique tasks, but how can I then take x number of tasks and put them in their group where the group is absent from this setup?

      Rock and a hard place here.  Any help appreciated.





        • 1. Re: Pivot Table anybody?

          The main layout issue that I see is that you indicate that you have 100 tasks. that would be 100 columns of data--something that wouldn't fit on a computer screen or printed page very well. A matrix of 100 rows, one per task and a column for each group, on the other hand, would be manageable. And if you only viewed data for a few tasks at a time and hand controls above each column for specifying the task, you could select different tasks to view in the columns of such a report.

          Either way, the most common approach is to set up a series of filtered portals. Example:

          Each row could be one customer or one group. A series of portals produce the columns of data. Typically one relationship works for all the portals, in this case to list all tasks for that customer or group. A different portal filter is then set up on each column of one row portals to filter for only records for a particular task. A summary field in the task table can then placed in the single row portal to show an aggregate value for all tasks of a specified type for the given customer.

          There are many variations of that basic approach. Sometimes the row is a sub summary layout part in order to work from groups of records.

          And ExecueSQL calculation fields are now sometimes installed in place of portals to list such data.