Sort Report in Order Parameter Values Entered or Asc for All

[kwtx04rm]

Hello Ido:

I found your post below and found it very helpful for a report I am working on. Currently using CR 10 PRO and datasources are DB2, MS SQL Server 2K and Oracle.

The report needs to have ability for users to enter Problem IDs and have the report sorted in the order the Problem IDs were entered. But if users want all Problem IDs (by entering a blank for the parameter selection) the report needs to be sorted by all Problem IDs in ascending order.

Is there a way to modify the formula to allow for this?

Thank you.


Formula Help
Sort Report in the Order of Multi_Valued Parameters
faq767-4506
Posted: 26 Nov 03

Assume your report accepts into a multi-value parameter called {?Customer_List} several customer numbers.
The report should show the customers in the order they were entered into the parameter.

Sort the report on the following formula, which computes for each Customer Code its position in the parameter value list:
------------------------------------------------------
NumberVar i ;
NumberVar N ;

FOR i := 1 TO UBound({?Customer_List}) DO
(
IF {Customer.Customer ID} = {?Customer_List} THEN
(
N := i;
Exit For ;
)
);

N;

------------------------------------------------------
Cheers,
- Ido

 

[IdoMillet]

Assume the formula above is called @Sort.

Create a new formula called @Final_Sort with an expression such as:

IF {?Problem_ID} = "ALL" THEN {Problem_ID} ELSE {@Sort}

Then, sort on @Final_Sort.

Cheers,
- Ido



view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[kwtx04rm]

Really appreciate your response above...

As per your post, I created the @Sort formula but was unable to create the @Final_Sort formula. I get this message:

"A string array is required here" (and {@Sort} is highlighted).

This is what I have for @Sort:
==============================
NumberVar i ;
NumberVar N ;

For i := 1 TO UBound({?ProblemID}) DO
(
If {rootcausem1.ID} = {?ProblemID} Then
(
N := i;
Exit For ;
)
);

N;


Here is a little more info about my report - it's currently grouped by {rootcausem1.ID} in ascending order. Also the Options for parameter {?ProblemID} are set as "Allow multiple values" and "Discrete value(s)".

The parameter {?ProblemID} is used in the report's criteria as follows:

//Parameter to allow users to enter one or many Problem IDs (ticket) numbers
({?ProblemID} = "" Or {rootcausem1.ID} = {?ProblemID});

Regards

 

[IdoMillet]

You probably used

IF {?Problem_ID} = "ALL" THEN {?Problem_ID} ELSE {@Sort}

Instead of

IF {?Problem_ID} = "ALL" THEN {Problem_ID} ELSE {@Sort}

What I meant by {Problem_ID} is your data field containing the Problem ID data.

- Ido

view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[kwtx04rm]

Ido - thank you very much!! Your suggestions were great.

Only had to make one adjustment to the @Final_Sort:

Converted @Sort to string to eliminate check message "A string is required here" (and {@Sort} is highlighted).

Changed it from ==> If {?ProblemID} = "ALL" Then {rootcausem1.ID} Else {@Sort}

To ==> If {?ProblemID} = "ALL" Then {rootcausem1.ID} Else CStr({@Sort})

I ran the updated report and entered 3 problem ids in this order - PM001414, PM001067, PM001306...now the report displays the results with Problem IDs sorted in the order they were entered. And if "ALL" is selected then all Problem IDs are displayed in ascending order.

Your feedback was most valuable! Thank you.

 

[IdoMillet]

Glad I could help.

- Ido

view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com