4 Replies Latest reply on Jun 2, 2014 9:00 AM by BryanSantos

    Dynamic Reports - Horizontal

    BryanSantos

      Title

      Dynamic Reports - Horizontal

      Post


           Guys,

           I need some help again.

           I am trying to do a report that will expand horizontally depending on the ranges that are set (via global fields).

           For example:

           - I set January 2013 to July 2013 to the global fields, the report should show columns January 2013 to July 2013

           - The number of rows is fixed, depending on the number of customers I have

            

           Do you have ideas on how I can accomplish this?

            

           Attached is a sample of the report that I want to do created manually.

      Income_Statement_sample_for_Sir_Bryan.png

        • 1. Re: Dynamic Reports - Horizontal
          philmodjunk

               Adding columns dynamically requires adding fields dynamically and this cannot be done with FileMaker.

               Closest way to come to what you describe is to set up multiple cross tab report layouts and then the script you run after setting the date range in the global fields would select the layout designed for that number of columns. The columns can be one row filtered portals so that two 5 column reports for different date ranges can use the same layout.

          • 2. Re: Dynamic Reports - Horizontal
            gregdc

                 This looks like the next project on my list.   Fixed number of Rows but variable columns (and my last column needs to be a data entry for new data)   I still am trying to think of a "simple" design that will work.

            • 3. Re: Dynamic Reports - Horizontal
              philmodjunk

                   Let me know if you do find such a simple method. For "simple" it's better, if possible, to swap rows for columns so that you can dynamically add rows (records or groups of records in your found set) instead of trying to dynamically add columns.

                   Such columns in FileMaker are fixed, the only way to vary the number of columns is to either leave the right hand, unused columns empty or to use multiple layouts with different numbers of columns.

                   There are three typical methods used to get data from different records (or summary data based on multiple records) into such columns:

                   Calculation fields that refer to related records where the relationship controls what records are used to compute a value

                   Filtered portals

                   Calculation fields that use ExecuteSQL to access the needed data. This data can be a single value or table like list of data similar to a "read only portal"

              • 4. Re: Dynamic Reports - Horizontal
                BryanSantos

                     Thanks for the inputs!

                     I'm currently looking at CrossTab reports, but will check out the typical methods you suggested.