I thought that it would be a fun experiment to play with a combination of ExecuteSQL and a WebViewer to see what potential exists for quick implementation of simple cross tab style reports.
The motivation for this was a thread on this forum which called attention to the lack of something akin to a horizontal portal, and the development overhead that has to go into working around this situation when you really want to have that sort of functionality, for instance, for creating a cross tab style report.
To me, the WebViewer is (among various other things), liberation to dynamically position objects on the screen. Somewhat similarly, ExecuteSQL offers the liberation to query FM without being tethered to a layout or a relationship. Combining these two features as an attempt to address the cross tab puzzle was too tempting for me not to play around with the idea.
I figured that other folks might be interested, might have better or alternative ideas to offer, might be inspired in some way or another, etc..
Thus I've created a demo file to illustrate my thoughts. It should be attached to this post.
I've removed the file so as to minimize wasting other folks' time. Already I can see a better way to do this, and with more learning about working with SQL and FileMaker together, another even better way will probably become apparent.
[Edited 23 June]
I've only just begun to work with ExecuteSQL; I can't wait to watch the tutorial videos, but I have not done so yet -- I won't be surprised if my understanding is lacking and/or the techniques used here are not best practice. Hopefully what I did won't offend anyone's sensibilities. If so, my apologies in advance...
This is a work in progress. I haven't pounded on it with tons of data, and there are methodology/integrity issues that I need to follow up on. Also, it's missing a key feature of having row and column totals, not to mention number formatting, etc....
Lay of the Land:
There are layouts and tables for defining the following entities/properties:
There is a layout for entering an allocation. A record in the Allocation table has a foreign key for each of the above entities, as well as a Quantity value which is being summed.
The heart of this is a custom function that takes parameters of field and table names, and based on this input, it calculates aggregate values, and arranges them into an HTML table which is displayed in a WebViewer.
Note that the custom function only considers two entities at a time. Example: If the function has been configured to create a grid based on Item and Store, then it will be calculating aggregates based on the keys for these entities. The potentially confusing part is that this demo includes flags for inclusion/exclusion of each entity. Please beware that this inclusion/exclusion flag is only considered for the two properties being fed to the custom function. All other inclusion flags will be treated as though they are set to true. Certainly, the function could be revised to take more flags into account, but, for the time being, it does not.
The custom function assumes that keys exist for all records, are numeric, and are unique. (It shouldn't be hard to upgrade the calculation to handle non-numeric keys.)
After all the bugs are worked out, the real questions in my mind are:
- Will this technique withstand crunching against large amounts of data?
- If not, in what ways might this be optimized to try to reach the goal of having it perform well over large sets of data?
If it can perform well, it could be a real treat.
I hope you find this of interest.
Sincere and best regards,