Crystal reports group sort


I have 3 groups:
Project Type
Project Number
Project Hours

I'm trying to use the group sort order for the project numbers according to a column in my report, The "difference" column, which is obtained by the subtraction of "Current Hours" and "Budgeted hours" per project. I'd like to sort the report by the biggest difference to the smallest.

My columns:
Current Hours
Budgeted hours

The problem is that my "Current Hours" are obtained by a summary of the details section :
Sum ({timesheet.hours}, {projects.prjNumber}) + Sum ({timesheet.hoursOvertime}, {projects.prjNumber})

I know the group sort order function only works on summaries, in this case I'm using a formula and a summary to obtain the difference which I'd like to sort on; how I can sort my projects according to this?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

I don't think you can since the formula isn't a summary

I don't think you canuse formulas even if it a just a summary like

FCapoAuthor Commented:
Is there a way to extract just the value of the formula and discard the "history" of how the result was obtained, so that crystal reports treats it just as a number and not a summary?
Not sure what you mean.  If it was just a number then you couldn't sort the groups based on it.

You could calculate it in a stored procedure or Crystal command

I assume every record has the budgeted hours value

Introduction to Web Design

Develop a strong foundation and understanding of web design by learning HTML, CSS, and additional tools to help you develop your own website.

Is "Budgeted hours" a detail level number (so each record has its own "Budgeted hours" figure), or is that for a group, like a "Project Number" (so the same "Budgeted hours" number is repeated in every record in the group)?

 If it's a detail level number, then you may be able to do your sort by creating a formula like the following and then creating a summary for that formula for the group, and sorting on that summary.

{timesheet.hours} + {timesheet.hoursOvertime} - {your Budgeted Hours field}

 If you're calculating the difference the other way around (by subtracting current from budget), then switch things around accordingly.

FCapoAuthor Commented:
No sadly the budget is a total for that group "Project Number" so I can't really add it to the details section
Oh well.  That was my assumption at first, but then it occurred to me that it might not be a group total, so I thought I'd check.  The problem is that the group sort options use group summaries, and you can't use a CR summary function on a summary.  So, it looks like you'll need to do something to manipulate the data before it gets to the report (eg. use a stored procedure to do some calculating).

 A SQL Expression might be another option.  I'm not sure, because I never used them (almost all of my reports used stored procedures, and you can't use SQL Expressions with stored procedures).  For example, you may be able to use a SQL Expression to calculate the total "Current Hours", or Difference, for each project.

 And to answer your earlier question about "extracting the value" for a formula, the basic problem is that CR run reports in passes, so the summaries are calculated in a certain pass, and even if you could somehow ignore the fact that they're summaries, the values simply won't be available until CR finishes that pass through the report.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.