Print Page | Close Window

Is there a method to sort a sum within a group?

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=15568
Printed Date: 04 May 2024 at 5:27pm


Topic: Is there a method to sort a sum within a group?
Posted By: Iain CD
Subject: Is there a method to sort a sum within a group?
Date Posted: 08 Feb 2012 at 1:18am
1) I have a report that is grouped by building.

2) The report also has a grouped #1 sum, which calculates the total minutes worked on jobs (grouped by building) which appears along side the grouped building name.

Question:

Is there a method to sort the report, so that the data is sorted by the highest number of minutes (then descending), as opposed to the current format which sorts the report by building name in alphabetical order?

In the example below the data would sort, as opposed to what is shown:

461.02
308.01
72.00

16 Bloomsbury Street     Total minutes 72.00  
    40,348   16 Bloomsbury Street : Gents toilet blocked.   Minutes   27.00 16 Bloomsbury Street
    41,045   16 bloomsbury street: toilet blocked   Minutes   25.00 16 Bloomsbury Street
    50,864   6 Bloomsbury Street : Gents toilet blocked.   Minutes   20.00 16 Bloomsbury Street
                           
178 Eastern Road     Total minutes 308.01  
    970   Toilet blocked.   Minutes   45.00 178 Eastern Road
    4,235   178 Eastern Road : Both toilets blocked.   Minutes   30.00 178 Eastern Road
    4,672   Kitchen: door handle is loose   Minutes   30.00 178 Eastern Road
    9,944   GF WC: broken toilet seat in   Minutes   45.00 178 Eastern Road
    34,047   178 Eastern Road : Kitchen drawer front needs putting back on and also side panel on work surface.   Minutes   25.00 178 Eastern Road
    34,047   178 Eastern Road : Kitchen drawer front needs putting back on and also side panel on work surface.   Minutes   43.00 178 Eastern Road
    42,105   178 Eastern Road : Tv aerial point broken.   Minutes   32.00 178 Eastern Road
    45,902   178 Eastern Road : Put plug on television as other one is broken.   Minutes   58.00 178 Eastern Road
                           
180 Eastern Road     Total minutes 461.02  
    4,342   180 Eastern Road : Toilet downstairs blocked.   Minutes   45.00 180 Eastern Road
    14,799   180 Eastern Road : Poor reception from tv.   Minutes   2.00 180 Eastern Road
    23,859   Re-paint the front door in black gloss   Minutes   66.00 180 Eastern Road
    23,859   Re-paint the front door in black gloss   Minutes   112.00 180 Eastern Road
    23,859   Re-paint the front door in black gloss   Minutes   84.00 180 Eastern Road
    23,859   Re-paint the front door in black gloss   Minutes   64.00 180 Eastern Road
    36,365   180 Eastern Road : Letter box needs attention as doctors can put their hands through and open door.   Minutes   58.00 180 Eastern Road
    72,704   WATER LEAK THROUGH KITCHEN CEILING THAT HAS BROUGHT PART OF THE TILES DOWN   Minutes   30.00 180 Eastern Road








Replies:
Posted By: lockwelle
Date Posted: 08 Feb 2012 at 3:08am
if the minutes are part of the data, and doesn't need to be manipulated (add some and subtract others or skip some) you can make a formula on the field, something simple like:
sum({table.field}, {group})
 
then you can select the formula to group on, just like you would select a field.
 
if on the other hand you need conditional summing (some rows are skipped or subtracted) then I don't know of a way.
 
HTH


Posted By: DBlank
Date Posted: 08 Feb 2012 at 4:28am

For clarity, As lockwelle states, if this is a simple group aggregate formula....SUM(table.minutes, table.building).... then you can sort on it based ont he result of those values but I think it must be sorted using the group sort expert (as Ido explained in your other post).

Click on the Group Sort Expert button
for this group use: All
based on: select SUM of table.minutes
use ascending or descending as desired.



Print Page | Close Window