6 Replies Latest reply on Mar 3, 2012 8:57 AM by davehob

    General approach to reporting

    davehob

      In the solution that I’m working on, the reporting function is very weak, and I’m looking at ways to improve it. The reports currently use a lot of very slow finds, dozens of unstored calcs in the data file itself, and some very inflexible layouts.

       

      A typical reporting requirement is for a month-by-month analysis of activity at our community centre, with various sub-totals, cross-tab presentation, etc.

      One route I’m thinking of going down is having a separate table (in the Interface file) for stuff to be included in the report (records within a certain date range, for example), and doing an import of qualifying records into that table. That way, I can do all the calculations in the “report records” table, keeping things cleaner (and quicker) in the data file, holding only the meaty data in the data table, with all the unstored calcs etc. in the temporary table, doing their stuff only when actually needed. Once the report has run, I can delete the records in the “report records” table.

       

      Are there any pitfalls to this approach that I should be aware of? Or other recommendations for building a reporting sub-system that is robust, flexible and efficient? (The reference books that I use are quite light on this side of things – if anyone can point me to a good resource, that would also be really appreciated.)

       

      Thanks in anticipation,

       

      Dave.

        • 1. Re: General approach to reporting
          comment

          Dave Hobson wrote:


          I can do all the calculations in the “report records” table, keeping things cleaner (and quicker) in the data file, holding only the meaty data in the data table, with all the unstored calcs etc. in the temporary table, doing their stuff only when actually needed. 

           

          Unstored calcs "do their stuff" only when actually needed - regardless of which table they are in. If you only need them for the report, don't place them on any other layout.

          • 2. Re: General approach to reporting
            Stephen Huston

            Hi Dave,

             

            Also take a look at the recent topic:

            Re: Unstored Calculations

             

            in today's postings. A lot of ideas there re Unstored calc issues with a viariety of ideas.

             

            Stephen Huston

            • 3. Re: General approach to reporting
              timwhisenant

              Dave,

               

              I have a similar setup in my reporting file, having done this I have a little food for thought.

               

              1.       Will your reporting be multi-user?

               

              a.       How to keep users from stepping on each-other’s records?

               

              b.      Who imported what record?

               

              2.       Using imports brings along housekeeping.

               

              a.       When do you clean out the file? Old records from previous report runs.

               

              b.      Keeping the report file nimble and quick.

               

              3.       What about multi-use records? Volunteer checks to be willing to perform several tasks and you need a single report listing the tasks a who is willing to perform them.

               

              All of these considerations can be dealt with by planning a variety of techniques. None-the-less they come up, so better deal with them sooner rather than later.

               

              BTW, I like my reporting file. Using it for me approaches the reporting with less clutter.

               

               

               

              My 3cents,

               

              Tim

              • 4. Re: General approach to reporting
                davehob

                Thanks, Michael and Stephen, for your pointers.

                 

                Tim - thanks a lot for your several cents' worth!  I had thought of some of these, but not all, so I appreciate your input.  I wasn't sure, though, what you meant in your point about multi-use records - could you clarify?

                 

                Thanks again,

                 

                Dave.

                • 5. Re: General approach to reporting
                  timwhisenant

                  Sure Dave,

                   

                   

                   

                  Where this came up for me was in the following scenario:

                   

                  I have several reports based on sales by territory. Occasionally we have a sale which is split into two territories, i.e. the commission will be split/shared by two different salespeople. In the sales order table there is only one record of the sale, but two territories selected. So this record needs to occur two places in the report once for each territory. I have reporting fields in the order which hold the territory data (total / territory count). When the records are imported into the reporting table, I read the count field and create the needed number of records in my reporting table. So, 1 sale with 2 territories ends up being 2 records( 1 for each territory) in the reporting table. Multi-use, same sale used in more than one place in the reporting.

                   

                  So this process happens along with the user/account specific processing when records go in.

                   

                   

                   

                  Hope this helps,

                   

                  Tim

                  • 6. Re: General approach to reporting
                    davehob

                    I see, that sounds really useful.  Thanks for sharing this, and I can already see how it'll help in my solution.

                     

                    Dave.