[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1931
  • Last Modified:

Crystal Reports Sort by Parameter

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?
Please let me know.
Thanks.
0
tegronakron
Asked:
tegronakron
  • 7
  • 2
  • 2
  • +1
3 Solutions
 
zephyr_hex (Megan)DeveloperCommented:
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
 
tegronakronAuthor Commented:
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
 
zephyr_hex (Megan)DeveloperCommented:
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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

 
tegronakronAuthor Commented:
Ok, tried this but it doesnt seem to want to create a group for a formula field?  Can this be done?
0
 
mlmccCommented:
Assuming al the fields are the same data type (in this case numbers)

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

Add a formula
  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
 
PATKIRSCHCommented:
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
 
tegronakronAuthor Commented:
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
 
tegronakronAuthor Commented:
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
 
PATKIRSCHCommented:
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
 
tegronakronAuthor Commented:
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
 
tegronakronAuthor Commented:
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
 
tegronakronAuthor Commented:
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
 
mlmccCommented:
Glad i could help

mlmcc
0

Featured Post

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.

  • 7
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now