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?
James0628Connect With a Mentor Commented:
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.

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?
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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

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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.