Hi all....have been cracking my head to develop a excel pivot table like report for my food and beverage invoicing system. The key is i need a report which user can select start and end date and also whether drink or food so that they can view the summaried invoice lines transactions within the selected dates and by food or drink category.

I'm wondering anyone can help other than using custom dialog to capture inputs and perform a script to that.

thanks,

Heman

The reason you're cracking your head is that FileMaker does not have a "pivot table" command, or anything like it. It is a database, not a spreadsheet. The "columns" are the fields, the rows are the records. A "pivot table" assumes that you want to use the values from a field, from all (or a found set of) records, as the "columns," or some other set of values which could produce summary values from the data (months as columns for example, with values summarized using a date field).

If you think about this in terms of the logic of a database you will see that it is not so simple. What field would be the "columns"? You cannot just plop arbitrary values down as on a spreadsheet and say "these are the columns." Spreadsheets have "location on the screen," much like a piece of paper. Something either is or is not at a certain place. A database does not rely on "location on screen." Otherwise, well, it would be a spreadsheet, with all of its limitations.

That is not to say that a "pivot table" or "cross-tab" report cannot be built. But it is not easy, and requires specific knowledge of what data you've got, what "columns" you want, and what is the correlation of the two.

The "food or drink" is kind of a separate problem. Which I don't really understand what you're asking. Do you only want to see one or the other at a time? Or see both, but in different "sections"?

If you do not "need" a cross-tab report, but can live with a standard FileMaker subsummary report, you will save yourself a lot of work.

Hi Fenton,

Thanks..I have use subsummary per your advise..thx, Heman