Group Sort on a Formula

[Theadmans]

Hi,
I am using Crystal Reports V10 against an SQL database.

I have very simple report which calculates a percentage in a formula at a Group Footer 1 as follows:-

MyPerc = 100 * SUM({Sales.TotalA}, {Staff.Name}) / SUM({Sales.TotalB}, {Staff.Name})

Where Group #1 is Grouped on Staff.Name

The formula calculates correctly but now I want to sort Group #1 by the result of MyPerc. This will give me a report showing Staff Names sorted in sequence of MyPerc. However, the formula is not availale for selection in Group Sort. Can anyone think of way round this problem please.

 

[Bloke152]

i think you could create a new group on your formula and surpress the original group?

 

[Theadmans]

Thanks for your reply - If I try to create a Group # 2 then my formula @MyPerc is not available for selection as this group??

 

[monkeybarfan]

Try using the Group Sort expert

 

[Theadmans]

I am already trying to use the Group Sort Expert.

My problem is that my percentage calculation is placed directly in the Group Footer (it is not a Summary of any kind) hence it is not available as a Sort option in the Group Sort Expert.

 

[Theadmans]

Any more ideas on my problem guys ?

 

[Madawc]

The way Crystal works, you can only sort records by some feature of the individual record. I don't think there is a way round.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[lbass]

You could do this by using a command as your datasource:

select sum(Sales.`TotalA`) as A, sum(Sales.`TotalB`) as B, Staff.`Name`
From Sales inner join Staff on
Sales.`ID` = Staff.`ID`
Group by Staff.`Name`

Not sure how you are joining the two tables, but this would be the general format. Then in the main report create a formula {@percent}:

{Command.A} % Command.B}

Place this in the detail section ( and click on the % icon in the toolbar), along with the staff name and then do a record sort (report->sort records) on {@percent}.

-LB

 

[Theadmans]

Thanks lBass - I tried something like this the other day. Unfortunately I am using Formulae for FromDate and ToDate in the Record Selection formula of the Report. This allows me to run the report for a selected date range. How would I quote the formlae for FromDate and ToDate in the Command that I setup as the new datasource ?

 

[monkeybarfan]

Can't you perform the % calculation in a Stored Procedure?

 

[lbass]

What are the contents of your date formulas?

-LB

 

[Theadmans]

Basically my calling program requests the Start Date and End Date for the report and sets the formulas @FromDate (the Start Date) and @ToDate (the End Date) in the Crystal Report for use in the Record Selection criteria. I.e both @Fromate and @ToDate are dates.

 

[lbass]

Again, what are the contents of the formulas, not a description?

-LB

 

[Theadmans]

Sorry lbass - @FromDate = DATE(2004, 01, 01)
andd @ToDate = DATE(2004, 06, 30) as hardcoded in the Crystal Reports Formulae section.

The Calling Program requests these dates and passes them into the report. In the Crystal Reports Record Selection I have the following formula:-
{MyTable.Date} in {@FromDate} to {@ToDate}

 

[lbass]

I'm not familiar with how a calling program passes dates to a report. You would need to build the dates into the command, and the command accesses the database directly. You can certainly build hard dates into the command or create parameters within the command and add them in, as in:

select sum(Sales.`TotalA`) as A, sum(Sales.`TotalB`) as B, Staff.`Name`
From Sales inner join Staff on
Sales.`ID` = Staff.`ID`
where Sales.`Date` >= {?start} and
Sales.`Date` < {?End}+1
Group by Staff.`Name`