Solved

# Crystal Reports Sort by Parameter

Posted on 2006-05-04
1,925 Views
I have a report that has a paramter called Event Type that is passed in.  Depending on the event type, I want to sort the records by different columns.  The Columns are Occurrences, Duration, Average Duration.   For example, a Work Event should be sorted by Average Duration, a Downtime Event should be sorted by Duration, and an Exception Event should be sorted by Occurrences.
Is there a way to do this inside Crystal Reports or with a formula?
Thanks.
0
Question by:tegronakron

LVL 42

Expert Comment

yes, it is possible.
here is one way to do it:
create a formula in the Occurences column.
if event_type = "Exception Event" then event_type  (or whatever other field in the record you want to display)

you will create a formula in each of the other columns that is similar to the above.
0

Author Comment

Well I want to display all the columns, duration, average duration, and occurrences.  I just want to sort the records differently based on the parameter passed in.  If exception event, then sort report by Occurrence.
If Downtime Event, then sort report by Duration, If worktime event sort by Average Duration.
0

LVL 42

Assisted Solution

oooh.. ok
i think you can achieve what you are looking for by creating a formula called "sort".  in that formula, have the following comparisons:
if event_type = "Exception Event" then "Occurrence"  (you will have one comparison for each event_type)
then create a group on that formula.

the report will then group/sort based on the results of the paramenter field.

0

Author Comment

Ok, tried this but it doesnt seem to want to create a group for a formula field?  Can this be done?
0

LVL 100

Accepted Solution

Assuming al the fields are the same data type (in this case numbers)

Open the report
Display the field insertion popup
Name - SortField
Type - String

Name SortBy
Formula
if {?SortField} = 'Occurrence' then
{OccurrenceField}
else if {?SortField} = "Duration" then
{DurationField}
else if {?SortField} = "Average" then
{AverageOccurenceField}

Use the sort expert to select the SortBy formula as the sort field

mlmcc
0

LVL 8

Expert Comment

and if some of the "else" fields are the incorrect data types, you can convert them to all be the same (e.g. using totext function, etc) so that you can still use the formula...

you can also "out smart" the group sorting by:
1) adding a sort # to the beginning of the resulting filed (for example, if you're converting everything to text, then under certain conditions, the result would be "01:" plus your resulting text...this will force your groups to sort the way that you wish without using "specified group" sorting (which cannot fluctuate based upon a parameter... Note the added colon (or equivalent symbol of your choice)- you'll see why in the next step...

2) presumably, you won't want to "see" the "01:", and thus you will use the grouping options (via <right click><change group> and then go to the 2nd tab) to modify the displayed group name, which will be based upon a formula similar to the following:

mid({@group01formula},instr({@group01formula},":")+1)
which will search to find the colon, and then only present the group name beginning 1 space to the right of the colon

hope this helps :) Pat K

0

Author Comment

Ok, so in regards to your post mlmcc, I created a parameter SortField and a formula SortBy but when I go to Report -> Record Sort Expert, it does not have the SortBy formula I created in the list, or the parameter SortField, only report fields can be sorted.  Am I going into the right option here?
I should mention I am using Crystal Reports Version 9 if that is of any relevance.
0

Author Comment

So I finally refreshed the report and now I can select sortby as a formula field to sort by.   If I want to sort these field, they will most likely need to be converted to time or dates correct?  If I try to sort them as string, it will not sort correctly.
0

LVL 8

Assisted Solution

are all of your possible parameter/sorting fields dates? if so, you can sort by them without converting to text (which presents additional sorting ocnsiderations for dates)

However, grouping by dates becomes another issue, unless once again, all of the "sub groups" (e.g. weeks, months, days, etc) are the same...

so, could you possibly elaborate a bit further?
0

Author Comment

Well let's just pretend there are 3 different reports for a moment to simplify this.
For the downtime report, I want to sort by downtime. Right now this field is a string in the format
DD:HH:MM:SS for days, hours, mins, secs.  How can I sort this?
0

Author Comment

Ok got the downtime report working.  For the worktime report, here is my question.  I have a column that is calculated for average worktime.
Is there a way to sort this?  The records are grouped by station, and the average worktime per station is calculated which is the Total Seconds for that Station divided by the Total Occurrences.
0

Author Comment

Ok figured this out after a long struggle.  I had to create a summary field. Insert -> Summary, for the field I wanted to sort by, Sum (Total Seconds).  Then I had to go into the group sort feature, and sort by this new summary field.
0

LVL 100

Expert Comment

mlmcc
0

## Featured Post

### Suggested Solutions

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
This video discusses moving either the default database or any database to a new volume.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…