Reporting data in a grid / pivot type layout

[devices99]

I'm not at all sure how to approach this - whether with multiple portals on a layout, (if it'll give the result), creating a temp static table with the required data, or if there is a better / cleaner way? (FileMaker Pro 11) The data for the report is reasonably simple, key tables:

tblSalesperson (SalespersonID, Name, Location, etc)

tblSale (SaleID, SalespersonID, CustomerID, SaleTitle, Date, Amount etc - sales are flat single high level transactions, no multiline sales items required)

I'd like to report the data grid / pivot table (similar to those in Excel) view, but obviously within Filemaker, not exporting the data out.

Columns (across the top) need to be data driven from tblSalesperson: Salesperson1, Salesperson2, Salesperson3 etc.

Rows, date based from tblSales, e.g: Quarter 1, Quarter 2, Quarter 3 and Quarter 4 (I've already calculated the quarters in a calc field in the tblSales.

Then the data in the "cells" in this grid report will be the actual sales data from tblSales, usually several per person per quarter, only required to show the SaleID and SaleTitle in each "cell".

Any pointers/tips? Many thanks in advance!

[comment]

Filemaker has no mechanism for creating columns dynamically. If you can accept quarters as columns and salespeople as rows, see a possible approach here:

http://fmforums.com/forum/topic/71836-getting-more-out-of-filtered-portals-in-version-11/

Of course, the simplest way is to produce a traditional report in the form of:

Adam

• Q1 100

• Q2 120

• Q3 105

• Q4 115

Betty

• Q1 145

...

or:

Q1

• Adam 100

• Betty 145

...

[bcooney]

Worth a look: CC Pivot from Cleveland Consulting. I haven't used it, it's new.

[devices99]

Thanks to you both - always great info as usual!

As you may have expected comment, I've got similar reports by date and by salesperson as you suggested, love your demo though in the other thread. I'm going to suggest at present they export the data to Excel and create a pivot in that, as they are very keen for the cross-tab view.

If there was more money, I'd definitely look at that plug-in in detail, screen shots look superb bcooney - hope FileMaker Inc buys it and integrates the feature!

[comment]

What Barbara suggested is not a plug-in. It's a method to overcome Filemaker's limitation of fixed columns by drawing the cross-tab in a web viewer. You can do the same thing, using a script or custom function, and show the results in a web viewer or a global field - or even in a dedicated report table.

[devices99]

What Barbara suggested is not a plug-in. It's a method to overcome Filemaker's limitation of fixed columns by drawing the cross-tab in a web viewer. You can do the same thing, using a script or custom function, and show the results in a web viewer or a global field - or even in a dedicated report table.

I downloaded the demo from www.clevelandconsulting.com and it has a FMX plug-in: CC Pivot.fmx ...

I was considering creating a report table, but exporting direct to Excel is far easier and cost effective for this version as the client fees aren't covering enough of my time for these extra features.

[comment]

I downloaded the demo from www.clevelandconsulting.com and it has a FMX plug-in

I stand corrected. But a plug-in is not necessary for this, esp. when you know in advance what data goes where (as opposed to a generic solution).

[Fitch]

FWIW I believe the plugin in ccPivot is used for SQL queries. As you say, not a requirement for building this sort of thing.

That said, I'm really impressed with ccPivot, based on my limited testing. It's a bargain compared to the development time it would take to build something similar on my own.