3 Replies Latest reply on Nov 14, 2012 3:06 PM by philmodjunk

    Creating a "Report Builder"



      Creating a "Report Builder"



           I'm curious about the possibility of creating a "Report Builder" tool in FileMaker.  I have a number of pre-built reports that show "Description", "Make", "Model", "Serial Number", "Location", "Status", etc...  I have been asked if it's possible to create a report builder that would allow the user to select which fields to include in the report.

           The logic behind it makes sense to me.  I could have a temporary "pop-up" page with all the possible fields and a checkbox next to each one.  If a field gets checked then its value is set to 1, if not it remains at 0.  Then you run a bunch of conditional statements to define your report.  The only way I can think of doing the report builder visually though is if you can hide or display items conditionally.  I know you can re-format and change the color of items, but I haven't found a way to actually hide or display them.

           If you could point me in the right direction or let me know up front that this is impossible I would be very appreciative.  Thanks!

        • 1. Re: Creating a "Report Builder"

               Dynamically including/excluding fields--and their associated field labels etc is not for the faint at heart. It can be done if your basic reports all have a similar structure, but it aint a trivial excersize and usually requires a lot of calculation fields that return the contents of a data field if that field is selected for the report and empty if it is not. Then you add another one for the field label set up the same way.

               And often you need to set up a lot of sliding fields to eliminate unused space and that brings in it's own set of issues.

               Often, the only practical way to do this is to set up a lot of report layouts and when your user is selecting report options, they are setting values in some global fields that your scripts use to select the appropriate layout for the options that they want.

          • 2. Re: Creating a "Report Builder"

                 Ok, I hear what you're saying.  It's basically right on par with what I was thinking.  What sort of calculation script would you use to dynamically include or exclude a field?  I might play with it and see if it's worth my time.

                 I had also thought of your second opinion in creating all the possible options in many layouts and then just going to the one that fits the bill, but to me that sounds worse than option 1.  It's not the end of the world if I can't get this done, but I'd like to at least monkey with it for a while.  Thoughts on the calculation stuff?


            • 3. Re: Creating a "Report Builder"

                   Actually the second option can be much easier than the first--especially since you can copy and paste groups of layout objects from one layout to another or duplicate the whole layout as part of the design process for creating your multiple report layouts.

                   Say you have two fields, name and date that you want to include dynamically in your report.

                   Define 4 unstored calculation fields as follows:

                   cNameData: If ( gNameSelect ; Name )
                   cNameLabel If (gNameSelect ; "Name" )
                   cDateData If ( gDateSelect ; Date )
                   cDateLabel If ( gDateSelect ; "Date" )

                   Now you can put these 4 calculation fields on your layout, setting them to slide up and resize enclosing part if such is needed and no script is actually uesd, gNameSelect and gDateSelect would be global number fields formatted with a single value check box that enters a 1 into the field if the check box is clicked. (Resize the field to hide the 1 so that only the check box is visible).

                   Note that the complexity increases rapidly, the more fields and their labels that you need to manage dynamically in such a fashion.