6 Replies Latest reply on Feb 5, 2012 1:16 PM by philmodjunk

    3 Summary Reports (in portals?) with Grand Total?

    arronman

      Title

      3 Summary Reports (in portals?) with Grand Total?

      Post

      I have a report which:

      • finds field Kg1 = <0
      • sorts by date (Sub-summary by Date1 (Leading)
      • Body containing several fields
      • (Sub-summary by Date1 (Trailing)
      • Trailing Grand Summary (Kg1 plus other fields)

      Now I need a report which does the same but includes data from Kg2 (by Date2) and Kg3 (by Date3) gathered from same records.

      Finally I need to display Grand Total of Grand Summaries (Kg1 + Kg2 + Kg3) and same with a couple of other fields.

      How do I do this?

      Do I use portals? Also just saw mention on New Window in the scripts menu. ?

        • 1. Re: 3 Summary Reports (in portals?) with Grand Total?
          philmodjunk

          Do you want your data in 3 groups in the same report?

          Grouped by Date 1, Grouped by Date 2 and Grouped by Date 3?

          You might need to restructure your table as you can't list the same records three times in the same report.

          If so, portals are an option with the appropriate relationships or filters, but doing so can include limitations to your report that are best avoided.

          Popping up a new window does not look like it would aid in producing a solution here.

          Can you describe your data in greater deatail? Why do you have 3 date and three Kg fields in the same table?

          • 2. Re: 3 Summary Reports (in portals?) with Grand Total?
            arronman

            !!!!!! I just lost my almost-completed post! What happened? Is there a timeout with the draft of a post?

            Starting again:

            My records are shops which are visited regularly (Date1, Date2, etc). On some of the visits purchases are made (Kg1, Kg2, with associated Paid1, Paid2 currency amounts.

            I need to produce reports which can display the various visits (in which purchases were made (kg >0)) with date period ranges:

            • minimum of 12 month
            • Quarterley
            • monthly
            • arbitrary periods

            These reports need to display the date, kg & paid data, as well as shop name and location, sorted by date, with daily totals and grand totals .

            Some of the reports need to include more than one visit (Date1/Kg1/Paid1 and Date2/Kg2/Paid2, etc)

            All fields (about 50 at present) are in 1 table.  With one date it woks very nicely. Looks like I need to restructure, considering that I failed miserably with my first attempt of including more than one date. As you said, doesn't work!

            Another future challenge will be organising the data to display the full shop record including - potentially - many visits.

            Some suggestions on restructuring would be greatly appreciated.

            • 3. Re: 3 Summary Reports (in portals?) with Grand Total?
              arronman

              Regarding Date1, Date2, Date3, the most important factor is chronological, which means there might be a mixture of Date1 & Date2 in the same range of dates.

              Tricky.

              • 4. Re: 3 Summary Reports (in portals?) with Grand Total?
                philmodjunk

                But why record three visits and purchases in one record?

                If Date1, Date2 and Date3 are three separate records using the same date field for recording the visit dates, this report becomes much simpler to create.

                • 5. Re: 3 Summary Reports (in portals?) with Grand Total?
                  arronman

                  Ok. I will create separate record for each date. Easy. My present report structure will work for that.

                  So now the question is:

                  How do I neatly display, in one layout, the accumulated visit data for each shop, as there will be numerous records associated with each?

                  • 6. Re: 3 Summary Reports (in portals?) with Grand Total?
                    philmodjunk

                    If you only want to see visit records for a given shop, you can perform a find for just those records. If you want to see visit records for many shops at the same time, you can add a sub summary part "when sorted by" a field that identifies the shop and then sort your records first by shop, then by date.

                    You can also set up a related table of shops with one record for each shop, then a portal to your visits table will list only the visits records for the current shop record. This can be a convenient what to add new records to your visits table while still using a summary report based on the visits table for generating reports of the resulting visits data.