Summaries via Portal / Subsummary Reports

[chikanyc]

I have a complex report that I cannot get to display correctly.

I have a set of records in a Line Items table. Each record contains

- PayerID

- ProductID (based on Product Table)

- Date of Product Purchase

- Quarter of Product Purchase

I have a Payer Table. Each record contains

- PayerID

I want to create a layout in the Payer Table, which displays a portal summarizing Line Items data by Quarter. (How many transactions has the Payer ID made by quarter?)

e.g. Q109, 30 transactions (or 30 Line Items records)

Q208, 20 transactions

The only way I have been able to do this is is by creating a Quarters table with a record for each Quarter that exists in my Line Items table. This table seems like a waste, but I haven't figured out a way around this.

In the Quarters table, each record contains

- Quarter

- cCountLI (calculation that counts all Line Items records by Quarter)

But to give me the summary data in a portal for how many transactions a Payer ID has had by quarter, I had to create a global field for Payers in the Quarters table. When you're toggling through different Payer records in the Payer ID table, I have a script trigger that copies the current Payer ID to the GLOBAL Payer ID in the Quarters table. This allows me to have filtered portal data.

This is probably not the best way to go about this, as I am having difficulty creating a subsummary report showing

1. subsummary-by Payer

2. subsummary-by Quarter with Count of transactions

3. body - list of transactions

Can anyone offer any advice? Thank you so much for reading:)

[comment]

The simple way to do this is to report from the LineItems table, with subsummaries by PayerID and by Quarter.

[chikanyc]

Thanks comment:)

Do you think I need a Quarters table?

[comment]

If you're not going to use a portal for this, then no.

[chikanyc]

thanks comment, it did work...

Do you know if there is a way that I could get an Excel-like view of..

Payer IDs for the rows, and Quarters for the columns, with the cCountLI as the body?

[comment]

There are several ways to do that - none of them too simple, though (unlike a spreadsheet, Filemaker is not flexible in the horizontal direction).

For example, you could define 4 relationships from the Payers table to the LineItems table (one for each quarter, assuming you want 4 columns) and show the summaries in a list view of Payers.

See also:

http://edoshin.skeletonkey.com/2006/12/crosstab_report.html