Use a formula to sort detail records

[elsenorjose]

Hello all,

I've been asked to generate a report that would print the details in a specified order based on a date value. The report lists different drug products along with projects for each and associated tasks for each project. I have grouped on product and project, and the tasks are my details. The request is to change the order that the details are printed depending on the end dates of the tasks. Specifically, if a task named 'QC' has the same end date as a task named 'Release', we want to print 'QC' first. Right now, the details are printing 'Release' first, and 'QC' second.

Is there a formula I can use to force the report to evaluate the end dates and task and based on this criteria, sort the details accordingly?

I am using Crystal Reports XI on Windows XP and a SQL Server 2005 backend.

Thank you.

 

[IanWaterman]

Just add a sort based on End date to the report.

However, this will only sort the data within a project.

Ian

 

[elsenorjose]

Thanks Ian but it's not that simple (I wish it was). If two tasks have the same end date, I then need to sort by the task name so that the QC task comes before the Release task.

 

[IanWaterman]

How many tasks can there for each project.

What you want to do is very difficult if not impossible as you are seeking to sort data based upon information in preceding or following recordsets.

YOu could subgroup by Enddate and then sort by task? Will that achieve what you want?

Ian

 

[elsenorjose]

There can be many tasks for each project.

I figured this would be very difficult or impossible but I was still holding out hope. I was trying to create a formula using PREVIOUS. Something like:

If Previous ({taskname}) = 'QC' and Previous ({enddate}) = ({enddate}) Then...

And that's where I got stuck. My sort isn't really ASC or DESC, it's specified based on the result of the condition.

Oh well, I hate telling users something can't be done, but it looks like I'll have to.

 

[MCuthill]

An idea (not 100% certain on it)...

Create a formula field to evaluate the TaskName and set a numeric value (smallest for those you wish sorted first).

@FormulaField:
IF {Table.TaskName} = "QC" THEN 0 ELSE 1

this could be expanded to:
@FormulaField:
IF {Table.TaskName} = "QC" THEN 0 ELSE
IF {Table.TaskName} = "xx" THEN 1 ELSE 2
so on, and so forth.

You should then be able to sort (Ascending) on the Formula Field and change the sort order with the IF statement as needed. Perhaps a little cumbersome, but it should work. I am using an older version of Crystal Reports though, so perhaps things have changed.

Hope this helps!

Mike
--------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure, in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."
Author R.A. Salvatore via "Drizz't"

 

[elsenorjose]

Thanks Mike,

That was a route I was thinking of as well. The only caveat is that there may be more tasks in future projects so I'd have to keep updating the list. But it IS worth a try since tasks aren't added very often.

 

[MCuthill]

elsenorjose:

Another idea is (if possible) an a field be added in the database (if there is a (lookup) table for tasks)? Could ask if sort-order or priority order could be added there and the reports sorted on it...

But I am sure that has also come to mind, and not always are we reporting folk able to modify the DB structure.

Cheers!

Mike
--------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure, in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."
Author R.A. Salvatore via "Drizz't"

 

[elsenorjose]

Actually, I have authority to make changes to it; I created the view the report is based on. I'll look into that. Thanks Mike.

 

[lbass]

Why not just sort on enddate first and then on task name?

-LB

 

[elsenorjose]

Hi LB,

There are other tasks as well and sorting by end date then task name might through those other tasks out of order. These two tasks are the only ones that need to be sorted according to their end dates being the same.

 

[lbass]

Why don't you show us a sample (multiple rows) that illustrates what the data looks like at the detail level.

-LB

 

[elsenorjose]

Thanks everyone for your input. The report is changing again. The user has requested different grouping now so this will throw off the date sort even more. Once I finalize the new grouping and data retrieval, I'll start a new thread if the date sorting issue is still open.