1 2 Previous Next 15 Replies Latest reply on Nov 26, 2016 8:14 AM by pademo57

    Summing Two Separate Parts on a Report

    pademo57

      I set up a program that tracks the events in a hockey game by pressing a button for each event and puts it into a portal which also shows the time of the event.

      My Tables: Hockey_Game::__pk_HID->Hockey_Action::_fk_HID

       

      An example would be in the Hockey_Action Table:

      Period   Team    Event Time

      Period 1 Team 1 Shot   01:54

      Period 1 Team 2 Goal   17:14

      Period 2 Team 1 Offside 03:01

      etc.

       

      Because I separated the events Shot and Goal when it comes time to summarize the events by period it separates Goals from Shots.

       

      So my question is when creating a report is there a way to combine or add two events and show them as one?

       

      In other words, if I have 5 shots and 1 Goal in Period 1, how would I show Shots + Goals for a particular Period, and then at the end of the report also have a combined Shots + Goals? Or is there a better way to do this?

       

      I'm sure this is easy, but for a newbie like myself I can't seem to wrap my head around on how to do it.

        • 1. Re: Summing Two Separate Parts on a Report
          erolst

          Try a calculation like

           

          GetSummary ( sCountOfShots ; period ) + GetSummary ( sCountOfGoals ; period )

           

          where sCountOf... are the existing summary fields.

           

          Make sure to check the "Do not store ..." storage option.

          • 2. Re: Summing Two Separate Parts on a Report
            philmodjunk

            In a summary report, you could easily show these stats like this:

            Period 1

                Shots  5

                Goals 1

            Period 1 Total 6

             

            Period 2

            and so forth...

             

            This is done with a summary field to compute the totals, a sub summary report "when sorted by event" and two more sub summary parts "when sorted by Period".

            • 3. Re: Summing Two Separate Parts on a Report
              erolst

              philmodjunk wrote:

              In a summary report, you could easily show these stats like this:

              Period 1

              Shots 5

              Goals 1

              Period 1 Total 6

              These seem to be different fields and different summary fields:

              pademo57 wrote:

              Because I separated the events Shot and Goal when it comes time to summarize the events by period it separates Goals from Shots.

              • 4. Re: Summing Two Separate Parts on a Report
                philmodjunk

                Nope. Just one summary field can show all three totals. The trick is in setting up the sub summary parts, and the sort order to get those results.

                 

                And to keep the individual events from each having their own row in the report, you remove the body part from the layout--a step I wouldn't do here until you get the other parts of the report working as those details can confirm for you that the records are correctly grouped and sub-totaled.

                • 5. Re: Summing Two Separate Parts on a Report
                  erolst

                  philmodjunk wrote:

                  Nope. Just one summary field can show all three totals.

                  Yes, that works if there is one record per event per team per period with a "generic" count.

                   

                  Is there - or is there one record per team per period, with fields for shot and goal count, as I understood it to be?

                  • 6. Re: Summing Two Separate Parts on a Report
                    philmodjunk

                    No, it works with multiple events per period.

                    • 7. Re: Summing Two Separate Parts on a Report
                      philmodjunk

                      I read this as one record per event with a single number field and a second field that identifies the event.

                      • 8. Re: Summing Two Separate Parts on a Report
                        pademo57

                        Thanks to both of you for trying to help a newbie but I still don't understand and maybe its because of the way I explained things. I will try to make it a little more clear I hope!

                        When someone presses the button say for "Shot" it uses a script that puts into the Event field "Shot" which is the script parameter.

                         

                        If you press the button that says "Goal" it uses the same script but with the script parameter "Goal".

                         

                        This script also puts in the Period, Team Name and Time it occurred in those respective fields.

                         

                        I'm not sure how to use a Summary field to calculate the total of the events adding both the Shot and Goal event AND for a particular Period.

                         

                        I think I am missing something critical here.

                        • 9. Re: Summing Two Separate Parts on a Report
                          philmodjunk

                          First, describe how this works in terms of your table and records when you press that button.

                           

                          Do you create a new record each time or are you incrementing a number field each time?

                           

                          Does your data look like this?

                           

                          Period 1 Team 1 Shot 1

                          Period 1 Team 1 Shot 1

                          Period 1 Team 2 Shot 1

                          Period 1 Team 1 Goal 1

                          Period 1 Team 2 Shot 1

                          Perod 2 Team 2 Goal 1

                          and so forth...

                          • 10. Re: Summing Two Separate Parts on a Report
                            pademo57

                            When you press the "Shot" button for the Home Team, (which is on the Hockey_Game layout), the script adds a record to the Hockey_Action table portal  which has the fields:

                            Period, Team, Action, Time

                            e.g. Period 1, Team 1, Shot, 1:03

                            it also increments the Hockey_Game::ShotHome number field which is displayed on the Hockey_Game Layout as Home SOG (Shots on Goal).

                             

                            If the "Goal" button is pressed it does the same as above (adding a shot) but also increments the Hockey_Game::GoalHome number field. Which is also displayed on the Hockey_Game layout.

                             

                            But when I make a summary report using the Hockey_Action table it will show Shots on a separate line and it will show Goals on a separate line, which doesn't give me the correct total number of Shots because Shots and Goals in the Hockey_Action are seen as two different actions.

                             

                            Should I make a sub-summary field which would include the Shots from the Hockey_Game table and omit the records in the Hockey_Action table which only show shots? If so, I don't know how to do this.

                             

                            I hope this is what you asked for.

                            • 11. Re: Summing Two Separate Parts on a Report
                              philmodjunk

                              In your example, I don't see a number field, just the type of event (shot or goal). So in that case, you use "count of" summary fields.

                               

                              If you don't want total shots + total goals for a period, I don't see a use for that row in your report at all. It can just look like this:

                               

                              Period1

                                Team 1

                                       Shots 5

                                       Goals 1

                               

                                Team 2

                                        Shots 8

                                        Goals 2

                               

                              Period 2

                               

                              but you'll need a way to discriminate between shots and goals if you want total shots and total goals for the entire game on your report. A relationship that matches to only specific events for a specfiic team may be set up, A script can find, total and then set a variable or field to each total, or ExecuteSQL can be used to produce such totals.

                               

                              Does that look like we are headed in the right direction?

                              • 12. Re: Summing Two Separate Parts on a Report
                                pademo57

                                Yes, I think we are headed in the right direction, but I still don't know how to get one  field to add to another field in a summary report.  In other words, count all the "Shots" and count all the "Goals", now put the two totals together on a line of a summary report.

                                 

                                Instead what I came up with, (and not at all an elegant solution), was to loop through the records setting variables for each period and each shot and goal. Then add the variables together for the period.

                                e.g. $Per1HomeTeamTotal = $HomeTeamShot + $HomeTeamGoal

                                Then I put the variable totals at the bottom of the report in the footer line:

                                 

                                Home Team                       Visitor Team

                                Period 1 Total Shots  6       Period 1 Total Shots   4

                                Period 2 Total Shots 11      Period 2 Total Shots   9

                                Period 3 Total Shots  5       Period 3 Total Shots   8

                                Totals for Game       22       Totals for Game       21

                                 

                                So although this works my question still is:

                                 

                                How do you add to one event (field) to another event (field) in a summary field?

                                • 13. Re: Summing Two Separate Parts on a Report
                                  erolst

                                  pademo57 wrote:

                                  How do you add to one event (field) to another event (field) in a summary field?

                                  You either add them in a calculation field and summarize that calc field, or you create two summary fields - one per data field - and use a calculation with GetSummary to add the summaries.

                                   

                                  Since you have these individual summary fields anyway, take the latter route. (And if you read back to the beginning of the thread, you'll see that my first post suggested just that.)

                                   

                                  It would be easier if you just had a generic Events table with a flag that says either shot or goal.

                                  • 14. Re: Summing Two Separate Parts on a Report
                                    philmodjunk

                                    We are moving from a summary report to something called a "cross tab" report where data from different records get's displayed in the same horizontal row. This is a more complex approach, but one that has numerous discussions here in the forum so you might research a few of them in order to learn more about that approach.

                                    1 2 Previous Next