I have a large number of reports that I want to add to my InBizness CRM framework product. That seems to work out well from a productivity standpoint with the blog that I have called FileMaker Reporting Explored. I am purposely trying to create crossfire efforts as I populate content in both areas.
So with that in mind, back on August 26, 2008, I created a blog posting for a cross tab example report for customer aging. A crosstab report is a report that summarizes values in both rows and columns. A week later, I did a report called Invoice Sales By Fiscal Year Quarter. So now it is time to take the next obvious step and blend the two to get a Cross Tab Fiscal Year Report.
Now I used a custom function to get my fiscal report quarter information data. Here is a copy of that calculation FYQ ( invoice_m::g_startmonthFiscalYear ; INVOICE::date_ordered ). The g_startmonthFiscalYear indicates what month of the year the fiscal year starts. In my InBizness solution, the g_startmonthFiscalYear field can be found in a preferences tab area from the main screen.
So when the calculation kicks in, you get data strings such as 4Q08, 3Q08 and 1Q07. The breakdown is the first two characters are the calculated quarter for the even and the last two characters are the year. In a crosstab report, I will need to calculate each quarter separately. So I will need to read just the first two characters of that result to get the quarter information.
I will need 4 calculation fields and 4 summary fields for those calculations. That gives me one summary total for each possible quarter. The calculation for the first quarter is Case(Left(FiscalYearQuarterSold; 2) = "1Q"; c_grand_total; ""). The c_grand_total field is the total of the invoice including line items, shipping and tax charges.
Again, there are a number of ways to create a crosstab report and the method I'm using is the least intense from a programming aspect but does create more code overall (particularly more fields in the implementation).
After that, it is just a matter of implementing the steps I’ve outlined before ...
- find a layout that closely resembles my needs
- duplicate that layout and rewire it for my report
- move the new report to the area in belongs in the overall layout list
- find a script or set of script steps that closely resembles my needs
- duplicate that coding and rewire it for my report
- add the additional report to my array of custom menus
- test the new report for quality assurance needs
- document the new report feature
© 2008 - Dwayne Wright - dwaynewright.com
The material on this document is offered AS IS. There is NO REPRESENTATION OR WARRANTY, expressed or implied, nor does any other contributor to this document. WARRANTIES OF MERCHANT ABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE EXPRESSLY DISCLAIMED. Consequential and incidental damages are expressly excluded. FileMaker Pro is the registered trademark of FileMaker Inc