6 Replies Latest reply on Aug 30, 2015 12:23 PM by skashanchi

    Need help with getting summary count in a report

    skashanchi

      I have a report that gathers all trips taken by passengers for a given date range, and summarizes the trips by combining all trips for individual passengers, and then shows totals for all trips, etc.

       

      So if I have passengers A, B, C, D, and F, I use a Sub-summary part on the report that combines all trips taken (rather than showing individual trips) and reports the total trips taken by each passenger. And then I have a Trailing Grand Summary that gives me a total of ALL trips and costs etc.

       

      All is working fine, except that I need to display the total count of how many DIFFERENT passengers we served for the report period at the bottom of the report. So if I have passengers A, B, C, D, and F, each taking 10 trips, I need to show we performed 50 trips for a total of $500 for 5 DIFFERENT passengers. I have set up a Total Passengers field and have tried various summary settings, using the passengerID field which is unique but can not get this to work.


      Can someone give me suggestions as to how this can be done?


      Thank you.

        • 1. Re: Need help with getting summary count in a report
          erolst

          If you have FM≥13, you can use one these two methods:

           

          1. by Value List & “List of”

           

          • create a ValueList of Passenger::personID (the foreign key that identifies a person, not the primary key)

          • create a summary field “List of” for the same field

           

          The number of distinct passengers in the found set is

           

          ValueCount (

            FilterValues (

              ValueListItems ( "" ; "PersonIDs" ) ;

              sListOPassengerID

            )

          )

           

          2. by “List of” & ExecuteSQL


          • create a summary field “List of” for the primary key

           

          The number of distinct passengers in the found set is

           

          Execute SQL (

            SELECT DISTINCT idPerson // the foreign key

            FROM Passenger

            WHERE id // the primary key

            IN (" & Substitute ( sListOPrimaryKey ; ¶ ; "," ) & ")

            " ; "" ; ""

          )

           

          If you're using textual IDs, you need to single-quote the individual values in the test set:

           

          IN ('" & Substitute ( sListOPrimaryKey ; ¶ ; "','" ) & "')

           

          But if you use ExecuteSQL, you should anyway either already be familiar with its peculiarities, or become so.

          • 2. Re: Need help with getting summary count in a report
            skashanchi

            Hmmm... thanks. I don't think I would have ever figured that out by myself at my current experience level.

             

            I think I will try the first method as I have zero experience with ExecuteSQL, but I am still a little unclear about this. Sorry if this should be clear. I am just getting my feet wet here:

             

            1. "• create a ValueList of Passenger::personID (the foreign key that identifies a person, not the primary key)"


            Not sure I understand. The Passenger::_pkPassenerID is my primary key and it is the unique value for each passenger. I do not have a foreign key in the Passenger table. My Trips table is where I associate trips with Passengers and that is where I have a foreign key Trips::_fkPassengerID. So are you saying my ValueList should be populated by Trips::_fkPassengerID?



            2. As to the calculation of ValueCount as you have written, where am I performing this calculation? In a layout script? If the target field is a Summary field I don't believe I have any method of attaching a calculation to it, do I?

             


            • 3. Re: Need help with getting summary count in a report
              erolst

              skashanchi wrote:

              My Trips table is where I associate trips with Passengers and that is where I have a foreign key Trips::_fkPassengerID.

               

              1. OK, this is a unclear to me; for what your solution manages, I would expect a three-table structure model like

               

              Trip --< Passenger >-- Customer

               

              Where in your two-table description do you store the individual trips?

               

              Well, be that as it may, from your description it appears you need to create the value list and the summary field for Trips::_fkPassengerID.

               

              You could create a calculation field to get the result of the calculation, but if you only need it when creating a report, you could instead perform the calculation within a $$globalVariable and put that variable as “merge variable” onto your report layout.

              • 4. Re: Need help with getting summary count in a report
                skashanchi

                Just so you see the whole picture: This solution is basically for a taxi company that offers rides to the elderly and disabled under social security. So we have a known list of "Passengers" who use the services to take "trips" to the doctors office, etc.


                - In terms of trips I only have 2 tables: Passengers and Trips.

                 

                - Trips are created and stored in the Trips table and are associated with the records in the Passenger table using the PassengerID. So the reservationist creates a trip and links the trip to the Passenger using a PassengerID (I am using a ValueList for this which allows the reservationist to type the Passenger Name, and I find the proper record and insert the PassengerID).

                 

                I do have a second occurrence of the Passenger table that I use for creating relationships with the Trips table.

                 

                I hope that is clear.

                 

                I suspected that using variables would be the way to g, but wanted to check in case I was missing something. Yes, this is only used for our weekly reporting where we bill the SS office for the trips offered and collect our monies from them (passengers do not pay us directly).

                 

                I will give that a shot.

                 

                Thank you.

                • 5. Re: Need help with getting summary count in a report
                  erolst

                  OK, that makes much more sense.

                   

                  So Trips is the table where you add the ingredients, the report script is where you cook up the result, and the report layout is where you serve it.

                   

                  Bon appetit!

                  • 6. Re: Need help with getting summary count in a report
                    skashanchi

                    Yes. Sounds like you are smelling what the rock is cooking!

                    Now lets see if I can actually produce something that is edible.