Pivot Table anybody?
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.