1 2 Previous Next 29 Replies Latest reply on Jun 28, 2011 7:48 AM by tomo17

    Summary Report to show top 5 records

    tomo17

      Title

      Summary Report to show top 5 records

      Post

      I am trying to get a report to summarise data based on age and gender then show the top 5 performers for each based on a points score.  I have been able to get the report to do the summary for age and gender but this shows all the records for the database sorted by age and gender.  i have been able to script so that it shows me only 5 records (omitting the rest) but for the whole database.  what i cannot seem to get is these two to work together so that i get records in the following report:

      age: 10  Gender : Male

      Student #1

      Student #2 etc

      Age 10 Gender  FeMale

      Student #1

      Student #2 etc

      Age: 11 Gender Male

      Student #1

      Student #2 etc

      I know i need to script but i am stuck!

      can anyone help or point me in the direction to any links that will help??

      thanks

        • 1. Re: Summary Report to show top 5 records
          TurtleKoala

          I would suggest looking at the "Report" layout type in the New Layout/Report menu. This can create a report that organizes the data by age and gender, but it will show all of the records, not just the top 5 in each category. Then you can set your script to run when you enter the layout. You may need to modify your script to work with this, but without seeing your script it is hard to tell for sure. Can you post your script so I can see how it works and whether you need to modify it?

          • 2. Re: Summary Report to show top 5 records
            tomo17

            Thanks Eric for your reply

            To be honest i just went through the report layout that created a script for me.

            go to layout...

            sort records...

            I then added the following that i found searching elsewhere:

            Go To Record/Request [ without dialog ; by calculation ; 6 ]
            Omit Multiple [ without dialog ; by calculation ; Get ( FoundCount ) - 5 ]

            Without the addition i get ALL the records sorted by age and gender and points (which is close to what i want. i just want the top 5 from this sort for each group).  with the additional script it gives me only 5 records in total

            I am hoping that the script modification is a simple one

            Thanks

            • 3. Re: Summary Report to show top 5 records
              TurtleKoala

              I think I know a way to do this. I'll write it up as a script. Iit will be helpful if you post your table names and field names so I don't have to use generic names.

              • 4. Re: Summary Report to show top 5 records
                TurtleKoala

                Also, are there at least five records for each category, or is there a possibility of there being fewer?

                • 5. Re: Summary Report to show top 5 records
                  tomo17

                  Thanks Eric - that would be much appreciated.

                  The table name is Athlete List

                  The Fields are: Name, Gender, Year

                  The Second table is Event Results

                  The field that I use from this one is Total Points (a summary field) It is this field that i use to sort and from which i want to use to generate the top 5

                  Hope this is enough for what you have in mind

                  Craig

                  • 6. Re: Summary Report to show top 5 records
                    tomo17

                    Sorry just saw the second post - yes always more than 5

                    • 7. Re: Summary Report to show top 5 records
                      TurtleKoala

                      Sorry, I can't work on this right now. Expect me to post something a bit later today. Maybe somebody else will come up with a more elegant way of doing it in the meantime.

                      • 8. Re: Summary Report to show top 5 records
                        TurtleKoala

                        Try this. It may not work perfectly at first, but I think it should be close. In particular there may be an off by one error. Let me no if there are problems.

                        Enter Browse Mode[]
                        Go to Layout["Event Results"(Database)]
                        Sort Records[Restore;No Dialogue]
                        Go to Record/Request/Page[First]
                        Set Variable[$currentGender;Value:Athlete List::gender]
                        Set Variable[$currentAge;Value:Athlete List::age]
                        Set Variable[$counter;1]
                        Loop
                        If[$currentGender=/=Athlete List::gender OR $currentAge=/=Athlete List::age OR $counter=5]
                        Set Variable[$currentGender;Value:Main::gender]
                        Set Variable[$currentAge;Value:Main::age]
                        Set Variable[$counter;1]
                        Else
                        Omit Record
                        Set Variable[$counter;Value:$counter+1]
                        End If
                        Go to Record/Request/Page[Next;Exit after last]
                        End Loop

                        • 9. Re: Summary Report to show top 5 records
                          tomo17

                          WOW - Thanks Eric for the reply and your effort.  Sorry i didnt get back to you sooner but this arrived after i had called it a night.

                          I will try it and get back to you.  Either way i am very appreciative of your time and effort.

                          Feedback on success soon.

                          • 10. Re: Summary Report to show top 5 records
                            tomo17

                            Ok I have had a try to get this working. 

                            Here are a few problems that came up

                            The IF statement where there is =/= i get an error saying that  "A number, text constant, field name or “(” is expected here."

                            The second issue is the Set Variable[$currentGender;Value:Main::gender] - What is the "main" referring to?  Should this be the athlete list table or is it referring to a different table

                            Thanks again

                            Craig

                            • 11. Re: Summary Report to show top 5 records
                              TurtleKoala

                              Yes, Main should be Athlete Table. I wrote most of that up before I saw your table names and I guess I forgot to replace one.

                              With the =/=, are you literally typing two equals signs with a slash between them? If you are I should have been more clear. You should be using a "not equals" sign, which looks like an equals sign with a slash through it. I wrote that up in notepad which as far as I know does not have a not equals sign. If you are using a not equals sign already I'll try to figure out why it is giving you that error.

                              • 12. Re: Summary Report to show top 5 records
                                tomo17

                                The not equal to makes sense - dont know why i didnt think that one through!  I have made the changes and it appears to have partially worked.

                                The records are sorted and then some are omitted (i should end up with 60 records in total after the sort) however i have over 300 still in the sort and some of those that have been omitted are actually in the top 5 for its group.

                                • 13. Re: Summary Report to show top 5 records
                                  TurtleKoala

                                  It is a little difficult for me to debug it without seeing what it is doing. If you can try to figure out why it is omitting the ones that it is omitting that would be helpful. In the meantime I have a database where I can try implementing my script to see what is going wrong.

                                  • 14. Re: Summary Report to show top 5 records
                                    TurtleKoala

                                    Also, if you are using FM Pro Advance there is a debugger which may help you figure out what exactly is going on. I only have the regular version so I can't give much advice about the debugger.

                                    1 2 Previous Next