Sorting a group by order of paramater values received

I have a report with a group that passes a parameter value to a sub report. That group value can have multiple values based upon what the user selects and if there are more than one then a subreport is generated for each value. That all works fine. The problem that I have is that the user wants each subreport to be displayed based upon the order of the group value that they entered instead of ascending or descending. For Example, if the selected B , C, and A  for the group values in that order then the first subreport would be for group value B, the next one would be for group value C and the last one for group value A. If I choose ascending or descending for the group sort order then the order of the subreport changes accordingly but when I tried using "original sort order" it still sorted the subreports in ascending order. How can I sort the group based upon the order of the parameter values?
Greg WatkinsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ido MilletProfessor of MIS at Penn State Erie and Owner, Millet SoftwareCommented:
Assuming you have a multi-value numeric parameter called {?Customer_List}.  
Sort the report on the following formula, which computes for each Customer ID 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}[i] THEN  
    (
    N := i;
    Exit For ;
    )
);

N;

Open in new window

Greg WatkinsAuthor Commented:
Thanks for the quick reply Ido but unfortunately the parameter type is a string value. The name of the parameter is QueueName and an example of how the values might might be ordered are Tier1, Tier2, CIT, Mgmt. If I set the sort method for the group to ascending or original order then I get CIT, Mgmt, Tier1, Tier2. If i set the sort order to descending then I get Tier2, Tier1, Mgmt, CIT.  I want the same order as how they are sent,  Tier1, Tier2, CIT, Mgmt.
Ido MilletProfessor of MIS at Penn State Erie and Owner, Millet SoftwareCommented:
The same exact logic applies to a multi-value string parameter.
Starting with Angular 5

Learn the essential features and functions of the popular JavaScript framework for building mobile, desktop and web applications.

Greg WatkinsAuthor Commented:
I tried using your formula but I must have something wrong. It did not sort preopery. Here is my formula. The parameter name I want to sort on is Group. The one difference I see between yours and mine is that yours references the {Customer.Customer ID} but when I created the formula in the sort by function I don't get any database fields to use. All I get is the parameter fields. See the attached screen shot of the formula editor.

NumberVar i ;
NumberVar N ;

FOR i := 1 TO UBound({?Group}) DO
(
IF {?Group} = {?Group}[i] THEN  
    (
    N := i;
    Exit For ;
    )
);

N;

Open in new window

Capture.PNG
Ido MilletProfessor of MIS at Penn State Erie and Owner, Millet SoftwareCommented:
Change
IF {?Group} = {?Group}[i] THEN

Open in new window

to
IF {The Field you are filtering on} = {?Group}[i]

Open in new window


The logic is that if the field matched the nth value in the parameter list, the formula returns N.
That way, each detail row knows its position in the list of parameters.
You can then simply Group the report on that position.
Mike McCrackenSenior ConsultantCommented:
You created a formula to choose the order of sorting.

Create a regular report formula with the changes Ido suggested and use it as the field for the group.  

mlmcc
Greg WatkinsAuthor Commented:
Ok, I guess I'm missing something.  I created a formula named GroupName as shown below to filter on the field MainChCfg.Name which is being passed as the ?Group parameter. I changed the group on the report to change based upon the GroupName formula instead of the MainChCfg.Name field.  I have subreport that was linked to the MainChCfg.Name group but that group is now the GroupName formula which passes a numeric value N so the sub report does not get the MainChCfg.Name. I need to pass the MainChCfg.Name value to the sub report.

NumberVar i ;
NumberVar N ;

FOR i := 1 TO UBound({?Group}) DO
(
IF {MainChCfg.Name} = {?Group}[i] THEN  
    (
    N := i;
    Exit For ;
    )
);

N;

Open in new window

Ido MilletProfessor of MIS at Penn State Erie and Owner, Millet SoftwareCommented:
Link the subreport on MainChCfg.Name
Nothing is forcing you to link it on the new formula.
Greg WatkinsAuthor Commented:
I tried doing that but when I choose the MainChCfg.Name field where you setup the sub-report links it won't let me link that to the ?Group parameter that is defined in the sub-report. It wants to create a new parameter.
Ido MilletProfessor of MIS at Penn State Erie and Owner, Millet SoftwareCommented:
Delete the subreport parameter.
Create the link again.
Ido MilletProfessor of MIS at Penn State Erie and Owner, Millet SoftwareCommented:
Let it delete the old parameter.
Use the newly created parameter to filter the subreport.
Greg WatkinsAuthor Commented:
Unfortunately that is a problem as well because the parameters in the sub-report come from a stored procedure in SQL so if I delete that parameter then the stored procedure won't work.
Ido MilletProfessor of MIS at Penn State Erie and Owner, Millet SoftwareCommented:
Then connect that parameter to the link you are passing it,
Assuming what you are passing in as a subreport link has the same data type as the subreport parameter, it should work.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Greg WatkinsAuthor Commented:
That worked Ido. Thanks!
Ido MilletProfessor of MIS at Penn State Erie and Owner, Millet SoftwareCommented:
Excellent. Thanks for closing the loop.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.