Dynamic Column Export

[RainDog]

Hi Forum

I have a tricky problem I just can't find a good solution for:

I have an ordering database like so:

Inventory_Items (things we sell)

Sales_Orders (customer orders)

Ordered_Items (things sold in orders)

My boss has asked for a report that shows, for a supplied date, the items we sold summarized by name and listed vertically, and the number of each item we sold by customer listed horizontally. The customer list should be able to expand dynamically. The final result needs to be exported to Excel.

Other than hard-coding summary fields by customer, I have no idea how to do this.

Any helpers out there? Please?

[bcooney]

Really can't be done in FM easily. This is a cross-tab report. FM doesn't summarize well horizontally. And it really is an odd request. How useful is a report that spans horizontally for each customer (how many customers are there?!)

A standard sub-summary report from the context of he ordered_items table would be the best way to go.

Otherwise, use FM as a data source in Excel and build a pivot table worksheet.

[RainDog]

Thank you so much for getting back to me! I thought FM might have some trouble with this...I have been working with FM for a couple of years, and this is the first request I have gotten that I had absolutely no clue on how to begin to make it work.

As for the usefulness of this report...well, I hope my boss understands that answer better than I do We currently have about 18 customers to report on, which is manageable in this format, but we could go up to 200 or more which will totally break this report (if I can even pull it off in the first place, that is).

I have already written a whole bunch of FM reports that summarize all kinds of things by customer. They just want to be able to combine the customer data and group it by product in Excel. Can you elaborate a little on using FM as a data source in Excel to build a pivot table worksheet? I have no idea what this is, but it sounds like it's exactly what I'm looking for...and is it an issue that the FM server is hosted remotely?

Thanks again for the advice. It can be a cold, lonely world out there for programmers sometimes.

[bcooney]

All I'm suggesting is using Excel's ability to access a FM database as a data source. Then build your report in Excel.

Picture_1.pdf

[agutleben]

Oops! This showed as a response to bcooney. Was meant as a response to zigzagzilla..

=========

I thought I'd point you to some great examples of how to create a cross tab report with files that you can tear into. I've found them helpful in the past.

Mikhail Edoshin's blog is great, though not updated very often these days. His blog post on creating a cross-tab report is wonderful:

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

Here's another great example posted by John Mark Osborne at his site, http://www.databasepros.com

http://www.filemakerpros.com/SpreadsheetDemoFile.zip

Edited December 3, 2009 by Guest

[bcooney]

Yes, but Aaron, "The final result needs to be exported to Excel." So, the goal is not a cross-tab in FM.