4 Replies Latest reply on May 29, 2014 12:42 PM by Stephen Huston

    How to get ranking list at Sub-Summary

    egbaumier

      At Sub Summary I have:

       

      Customer , Sales , % Performance

      -----------------------------------------------

      Customer 1 100kUSD 20%

      Customer 2 200kUSD 40%

      Customer 3 110kUSD 22%

       

      I'd like to include at Sub-Summary a Ranking List when sorted by Sales or Performance, such as:

       

      Rank (), Customer , Sales , % Performance

      --------------------------------------------------------

      (3) Customer 1 100kUSD 20%

      (1) Customer 2 200kUSD 40%

      (2) Customer 3 110kUSD 22%

       

      Is there any suggestion how to do it?

       

      Regards

       

      Edison

       

      FM13 Pro Advanced

        • 1. Re: How to get ranking list at Sub-Summary
          erolst

          You could always use a script to sort, loop, set an index field, then re-sort, but here's a static solution:

           

          Let ( [

            ~sql = "

            SELECT kf_horseID, SUM ( amountCharged ) AS s

            FROM Events WHERE kp_eventID In (" & Substitute ( Events::sListOfIDs ; ¶ ; "," ) & ")

            GROUP BY kf_horseID

            ORDER BY s DESC " ;

           

            resultList = ExecuteSQL ( ~sql ; "|" ; "" ) ;

           

            me = Events::kf_horseID & "|" & GetSummary ( Events::sTotalOfAmount ; Events::kf_horseID )

            ] ;

          ValueCount ( Left ( resultList ; Position ( ¶ & resultList ; ¶ & me ; 1 ; 1 ) ) )

          )

           

          Note that the field in bold must be the sort field (or one of them) for the report, so GetSummary calculates correctly (and returns the same results as the SQL query …)

           

          You need to define a summary field with "List of " (yourPrimaryKey) – a great new feature in FM13! – , so the SQL query can be constrained to the found set. Not sure how this scales to very large sets. Seems like a bit of a hack anyhow …

           

          As you can see, this is from a sample file for a different business, but then again: horses, clients – what's the big difference?

          1 of 1 people found this helpful
          • 2. Re: How to get ranking list at Sub-Summary
            Stephen Huston

            You can also add sub-sum parts to the layout which rely on sorting  (descending based on the specific sub sum value), then place sorting buttons in the header to resort the list appropriately for the different options.

            1 of 1 people found this helpful
            • 3. Re: How to get ranking list at Sub-Summary
              egbaumier

              Stephen

               

              Thank you for your help.

               

              Issue is there are 9000 registers at this database to sum by Customer,  that results close to 2000 Customers Rank.

               

              Performance is poor with SQL... there so many registers to handle it.

               

              I need to think something different to show this cosmetic information 8-).

               

              Regards,

               

              Edison

              • 4. Re: How to get ranking list at Sub-Summary
                Stephen Huston

                This is very similar to a situation I inherited where 600 customers had to have status updates showing last invoice date, total invoices for year, and a bunch of other stuff, and it was being accessed by sales people relying on wifi connections on the road.

                 

                We ran a daily status update which looped through the customer records during off hours (you can script it as a server operation for 3am), and set the results into actual stored date and number fields in the customer record, along with the date-timestamp of the last update.

                 

                This way the info required to calcs nor related values being summarized in order to see it as of the last update.

                 

                I also built a non-looping version of the same updater script so that a user could, if they wanted, update any single record to the current moment if they felt the 3am data was too old for their uses.

                 

                This turned the table into a lightening-fast reporting tool for status information, even for WAN users.

                 

                If you captured and stored the compiled data to a few stored fields in the customer record, sorting by ranking could be done right in the customer table, no sub-sum part or totals relying on other data would be required.