Alpha Video Training
Results 1 to 13 of 13

Thread: High to low sort on calculated field on report

  1. #1
    Member
    Real Name
    D.Chiasson
    Join Date
    Jan 2004
    Location
    West Coast of Canada
    Posts
    632

    Default High to low sort on calculated field on report

    I have a report that has a break on equipment as well as a list of work orders for each equipment which shows labour costs. I have a calculated field that shows the total labour cost of all work orders for each equipment but would like to sort the report to show, the equipment with the maximum work order labour cost first, then the next equipment down to the lowest.

    Any easy way to do this? It's beyond my talents. Thanks.

  2. #2
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,119

    Default Re: High to low sort on calculated field on report

    Only if you can populate a table field with the value on which to sort (likely tablesum() or dbsum()). The Alpha report engine doesn't precalculate values like that. It selects the records according to your filter and groups them and starts printing. The report is done by the time it has reached all the records necessary for computing your max cost.
    There can be only one.

  3. #3
    "Certified" Alphaholic
    Real Name
    Tom Cone Jr
    Join Date
    Apr 2000
    Location
    Florida
    Posts
    23,310

    Default Re: High to low sort on calculated field on report

    D,

    The field Stan mentions could be put in the parent table of your set, and then populated with a saved update operation. The update operation would be based on the parent table of your set and would use an expression that summed the labor costs from your child table. As Stan mentions, tablesum() is a likely candidate for that expression.

  4. #4
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,119

    Default Re: High to low sort on calculated field on report

    Possibly it could also be done with a posting field rule?
    There can be only one.

  5. #5
    Volunteer Moderator
    Real Name
    Alan Buchholz
    Join Date
    Oct 2000
    Location
    Delavan, Wisconsin
    Posts
    9,628

    Default Re: High to low sort on calculated field on report

    Quote Originally Posted by Stan Mathews View Post
    Possibly it could also be done with a posting field rule?
    potentially yes. Practically No. Too unreliable.
    Al Buchholz
    Bookwood Systems, LTD
    Weekly QReportBuilder Webinars Thursday 1 pm CST

    Occam's Razor - KISS
    Normalize till it hurts - De-normalize till it works.
    Advice offered and questions asked in the spirit of learning how to fish is better than someone giving you a fish.
    When we triage a problem it is much easier to read sample systems than to read a mind.

  6. #6
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,119

    Default Re: High to low sort on calculated field on report

    If it's a bug they should fix it.
    There can be only one.

  7. #7
    "Certified" Alphaholic G Gabriel's Avatar
    Real Name
    G. Gabriel
    Join Date
    Oct 2004
    Posts
    7,204

    Default Re: High to low sort on calculated field on report

    Quote Originally Posted by dchiass View Post
    I have a report that has a break on equipment as well as a list of work orders for each equipment which shows labour costs. I have a calculated field that shows the total labour cost of all work orders for each equipment but would like to sort the report to show, the equipment with the maximum work order labour cost first, then the next equipment down to the lowest.

    Any easy way to do this? It's beyond my talents. Thanks.
    Are you saying you have two grouping in the report,the outer one is for Equipment and the inner one for Work Order?
    Assuming so, you could sort the group on whichever order you wish.

  8. #8
    Member
    Real Name
    D.Chiasson
    Join Date
    Jan 2004
    Location
    West Coast of Canada
    Posts
    632

    Default Re: High to low sort on calculated field on report

    Quote Originally Posted by G Gabriel View Post
    Are you saying you have two grouping in the report,the outer one is for Equipment and the inner one for Work Order?
    Assuming so, you could sort the group on whichever order you wish.
    As you see in report attached, there are 2 sections. The report length could be 100 pages & it would be too time consuming to go thru that many pages to determine which are the assets with largest labour costs hence the need for a SORT.

    I would like this report to sort by equipment with the HIGHEST Total Equipment Labour cost first, then next lower labour cost etc. The Tot_Equip field is a calculated value, its basically the total of all the labour charges for all the work order done on that asset.

    The attached report should have equipment #140 first with total labour value of $4,000, then asset # 300 with a labour cost of $1,400 & lastly asset # 191 with labour cost of $1,100
    Attached Files Attached Files

  9. #9
    "Certified" Alphaholic G Gabriel's Avatar
    Real Name
    G. Gabriel
    Join Date
    Oct 2004
    Posts
    7,204

    Default Re: High to low sort on calculated field on report

    Add a group around the equipment group with that calc field, then sort it in a Descending order.
    Or, instead of getting bugged down with how to sort, really, all you are looking for is 2 numbers, the highest and the lowest.
    Just add 2 more calc fields to show these two numbers and you do not have to look through the entire report.
    Use something like dbmax() and dbmin().

  10. #10
    "Certified" Alphaholic
    Real Name
    Tom Cone Jr
    Join Date
    Apr 2000
    Location
    Florida
    Posts
    23,310

    Default Re: High to low sort on calculated field on report

    D,

    You've done a good job at hiding the structure of the set supporting your report.

    If you're working with a two table set, using one-to-many link, I think the ideas in posts 2 & 3 will help. If you're working with a single table then G's idea in post #9 looks more promising.

  11. #11
    Member
    Real Name
    D.Chiasson
    Join Date
    Jan 2004
    Location
    West Coast of Canada
    Posts
    632

    Default Re: High to low sort on calculated field on report

    Quote Originally Posted by Tom Cone Jr View Post
    D,

    You've done a good job at hiding the structure of the set supporting your report.

    If you're working with a two table set, using one-to-many link, I think the ideas in posts 2 & 3 will help. If you're working with a single table then G's idea in post #9 looks more promising.
    No I am not hiding anything, I was not aware it was needed. If you need some more details, please ask & I will do my best to answer as I really appreciate all comments & your time to HELP. See attachment.
    Attached Images Attached Images

  12. #12
    "Certified" Alphaholic
    Real Name
    Tom Cone Jr
    Join Date
    Apr 2000
    Location
    Florida
    Posts
    23,310

    Default Re: High to low sort on calculated field on report

    D,

    Wow. A one-to-one set. I did NOT see that coming.

    I see two solutions:

    a) add a field to your parent table to hold the labor total; then populate it using a saved update operation that sums labor for each piece of equipment. Two step solution: Run the update operation, then run the report.

    or

    b) store the labor totals in a separate table containing one record for each piece of equipment. Create a set joining the new table to your parent table with another one to one link. Move your report to the new set. Once its setup you use the same two steps: Populate the total for each piece of equipment in the new table using an update operation; then run your report.

    tom

  13. #13
    Member
    Real Name
    D.Chiasson
    Join Date
    Jan 2004
    Location
    West Coast of Canada
    Posts
    632

    Default Re: High to low sort on calculated field on report

    Quote Originally Posted by Tom Cone Jr View Post
    D,

    Wow. A one-to-one set. I did NOT see that coming.

    I see two solutions:

    a) add a field to your parent table to hold the labor total; then populate it using a saved update operation that sums labor for each piece of equipment. Two step solution: Run the update operation, then run the report.

    or

    b) store the labor totals in a separate table containing one record for each piece of equipment. Create a set joining the new table to your parent table with another one to one link. Move your report to the new set. Once its setup you use the same two steps: Populate the total for each piece of equipment in the new table using an update operation; then run your report.

    tom
    Thanks. I will let you know how we make out.

Similar Threads

  1. How to sort a calculated field
    By Kzin in forum Application Server Version 11 - Web/Browser Applications
    Replies: 0
    Last Post: 06-12-2012, 08:21 AM
  2. Sort report on calculated field
    By jas102 in forum Alpha Five Version 7
    Replies: 21
    Last Post: 06-03-2010, 02:01 PM
  3. How can you sort a Report on a calculated Total fi
    By Lee Bjornson in forum Alpha Five Version 5
    Replies: 3
    Last Post: 09-19-2005, 01:44 AM
  4. High, middle,low of 3 numbers?
    By Raymond Lyons in forum Alpha Five Version 6
    Replies: 22
    Last Post: 02-19-2005, 10:44 AM
  5. Select transaction dates: low and high
    By Randy Cabell in forum Alpha Five Version 4
    Replies: 1
    Last Post: 04-13-2000, 04:49 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •