EXAMPLE: Creating A Popular Crosstab Report

From Dwayne Wright - Certified FileMaker 9 Developer
WEB: www.dwaynewright.com
EMAIL: info@dwaynewright.com
TWITTER: dwaynewright

I do want to stress this isn’t the only way to create such a report but a very popular one and one that is so basic a developer of any level can implement.

One of the most popular crosstab reports is the one that shows accounts receivable broken down into ranges between the current date and the original date due. So we want the report to list each client that has one of more invoices with payment needed and we want to see it categorized by 0-30 days, 30 + days, 60 + days and 90 + days. So you can see the dollar amount for a client across the board.

For this example, I created calculation fields that determine if a value is needed for the 0-30 days, 30 + days, 60 + days and 90 + day options. After that, I create similar summary fields for each. So we have 8 new fields when this is done.

So on the main layout, you can see just the basic invoice fields I need to show of the aging report. You can experiment in changing the invoice date and see the different aging calculations update.

Here you can see the basic layout.

Now we have our report that we run. Any of you that have seen my reports are familiar with the style. This is because I usually copy the script steps from an existing report (a FileMaker Advanced feature), paste it for my new script and tweak it as necessary. So this script ...

- opens up in new window
- finds invoices with a balance due
- runs an error sub routine if no records are found
- goes to the report layout and sorts the records
- adjusts the window to fit the needs of the report

An example file can be downloaded at ...
http://www.dwaynewright.com/blogfiles08/crossTabAging.zip


There is a related movie on this topic! CLICK HERE!


=
More info about the author and FileMaker in general, contact me at info@dwaynewright.com.

© 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.

====================== ADVERTISEMENT ==============================
Check out the FileMaker Term Of The Day at http://filemakerterm.blogspot.com/ or subscribe via your news reader. ===================================================================

FILEMAKER: Duplicating A Sub-Summary Report

From Dwayne Wright - Certified FileMaker 9 Developer
WEB: www.dwaynewright.com
EMAIL: info@dwaynewright.com
TWITTER: dwaynewright

I have to admit, I almost never use the report assistant to create my reports. I tend to create them from blank layouts or duplicate/tweak an existing report for my needs. Duplicating an existing report can be quite a time saver and I do it all the time when I add new reports to my InBizness SOHO product.

For example, today I added a client marketing crosstab report and it took just a few minutes and I didn’t even have an existing crosstab report to duplicate. A crosstab report is a report that summarizes values in both rows and columns. My new report can be used my the marketing department to get an overall idea of client activity ranging from campaigns, proposals, quotes, sales orders, invoices and invoice totals.

To create this report, I went to an existing client subsummary report and duplicated it. I didn’t need the subsummary layout parts, so I deleted all but one of them and converted the other to a body layout part. I then remapped the fields on the layout to be the ones I wanted and the report layout was done. Well, it we done after I gave the layout its new name and moved it into position in the overall layout order.

Next, I have one main report that I use for all my client module reports and each report is separated by a script parameter. I copied and pasted a group of script steps that mostly resembled the ones I would need. I then updated that set of script steps and gave it a new script parameter trigger. So now my script for the new report was done.

Next, I went to my custom menu and I have a custom menu for all my client reports. I duplicated one, tweaked it to use my new script parameter and my custom menu was done.

Next I ran the report and saw one thing I wanted to tweak from the layout and one for the script. A couple minutes later, my entire new report was done!

Here are some links to other posts that might be of interest in regards to this topic...
Brief Introduction To CrossTab Reports
The Get(ScriptParameter) Function
Modularizing Scripts
Creating A Sub-Summary Report For InBizness SOHO
A READER ASKS: Combining Scripts Using Parameters
=
More info about the author and FileMaker in general, contact me at info@dwaynewright.com.

© 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.

====================== ADVERTISEMENT ==============================
Check out the FileMaker Term Of The Day at http://filemakerterm.blogspot.com/ or subscribe via your news reader. ===================================================================

FILEMAKER: Brief Introduction To CrossTab Reports

From Dwayne Wright - Certified FileMaker 9 Developer
WEB: www.dwaynewright.com
EMAIL: info@dwaynewright.com
TWITTER: dwaynewright

A crosstab report is a report that summarizes values in both rows and columns. For example, I just added a crosstab report for the revision of InBizness SOHO that I’m working on. This report can be used my the marketing department to get an overall idea of client activity ranging from campaigns, proposals, quotes, sales orders, invoices and invoice totals.

Here you can see a small slice of example data in this new crosstab report.

There is no built in assistant within FileMaker to build crosstab reports but there are multiple ways in which to design them using your skills as a FileMaker developer. Each method will have its advantages and disadvantages in key areas such as ...

- the amount of time to create the report
- how complex the report is to create
- how flexible the report is for recycling for other needs
- how large of a schema footprint it leaves (extra fields, layouts, scripts)
- how well it can handle extremely large sets of data

Crosstab reports can be very handy and it is something you will want to have in your overall FileMaker reporting skills portfolio.


=
More info about the author and FileMaker in general, contact me at info@dwaynewright.com.

© 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.

====================== ADVERTISEMENT ==============================
For more information on the InBizness SOHO and other quality FileMaker framework solutions, please visit http://www.dwaynewright.com/solutions.html

FILEMAKER: Using Append To PDF In Reporting

From Dwayne Wright - Certified FileMaker 9 Developer
WEB: www.dwaynewright.com
EMAIL: info@dwaynewright.com
TWITTER: dwaynewright

I once wrote a draft of an article for FileMaker Advisor that was never picked up. The idea was to build a FileMaker file used only for reporting. The idea was to import data into the file and then use it to process the reports and reduce the load of a database system that might be on the edge of having speed issues. Now with all the talk of the separation model, I might revisit that. But I digress before even getting started. That is a first for even me!

In that draft article, I mentioned that a developer could lighten the productivity load from a database by not allowing users to run reports. Many times, users will want to run reports that have already be run, again and again. In fact, back in my Apple days, I could see some of my databases take a noticeable hit right before department head status meetings because managers would be re-running last months reports, which they had already been given. That had given me an idea to run reports and save them as pdf or excel files and post them to the manager server. Yes, we had a file server dedicated to managers. This ended up being a big hit with the managers and I ended up with the task of managing their dedicated manager server (no good deed goes unpunished).

When I saw the new feature of Append To PDF, that triggered these memories. In the situation above, you could have a daily updated monthly report available for download or viewing. So you could have, in effect, living reports!

Here are some links to other posts that might be of interest in regards to this topic...
Save Records As PDF Script Step
FileMaker Save As PDF Script Step Crashing In Leopard
Save Records As PDF
=
More info about the author and FileMaker in general, contact me at info@dwaynewright.com.

© 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.

====================== ADVERTISEMENT ==============================
For more information on the InBizness SOHO and other quality FileMaker framework solutions, please visit http://www.dwaynewright.com/solutions.html