3 Replies Latest reply on Nov 16, 2011 10:09 AM by philmodjunk

    reporting year-to-date values and last year-to-date also

    med

      Title

      reporting year-to-date values and last year-to-date also

      Post

      I have a table which keeps the weekly totals of different values of sales and cash and other performance indicators.

      The table has about 95 numeric fields. I need to produce a weekly report showing thes values together with y-t-d value and last year y-t-d also. The first thing I can think of is to create summary fields for each field I have but I feel that this will not acheive my purpose because it might work if I am listing all weeks, but I need only the current week then the y-t-d's, plus the fact that I am increasing the number of table fields to double..

      I am hoping that there is a function or a way to summarize all felds at once into an array and then use its elements to display in the report.

      I also have 2 other question:

      1-does adding calculation fields increase the size of the table if I use the option not to store calculation results?

      2- does adding a summary field increase the size of the table?

        • 1. Re: reporting year-to-date values and last year-to-date also
          philmodjunk

          Questions 1 and 2. I'm sure that they do at least by a small amount, but given that hard drives now come in hundreds of gibabytes in capacity, does it really matter?

          I prefer stored calculations in most cases as they produce shorter response times when performing sorts, finds and when pulling up report layouts that access very large numbers of records. There is a performance hit from this approach when you need to import data into such a table--say from a back up copy or when deploying an updated file--but since such imports are much less common than searches, sorts, etc. I find that the more reasonable trade off.

          With regards to your report, can you post a "mock up" of what it needs to look like? Your basic choices are to either use summary fields in a summary report, Summary fields in filtered portals, or calculation fields with the Sum function and the appropriate relationship to match only to the records that need to be summed. In each case, you add more fields and perhaps more table occurrences to your file.

          Also what fields do you have in your table to identify the: Week, date, and/or year?

          • 2. Re: reporting year-to-date values and last year-to-date also
            med

            here is an image of the report I am trying to recreate I have done that in Visual fospro which was very easy because there was a function to summarize all fields of a range of records into an arrays then parse the array into another temp table

             

            • 3. Re: reporting year-to-date values and last year-to-date also
              philmodjunk

              You appear to have multiple tables involved here and different YTD and previous YTD values you need to pull from each.

              I'm inclined to use a "self join" with a summary field for each YTD and PREV field.

              Here's a simplified outline of the method, using just one set of YTD and PREV values:

              Define two fields in Cash Reciepts: sTotalCash, cYear, cPrevYear

              Define sTotalCash as a summary field computing the "Total of" your CashReceipts field.

              cYear is defined as Year(DateField)

              cPrevYear is defined as Year ( DateField ) - 1

              Make two new occurrences of CashRecipts and link them like this:

              CashReceipts::cYear = CashReceiptsYTD::cYear

              CashReceipts::cPrevYear = CashReceiptsPrevYear::cYear

              On your layout, add CashReceiptsYTD::sTotalCash and CashReceiptsPrevYear::sTotalCash to your layout.