# 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?
###### 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.

Professor 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;
``````
Author 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.
Professor of MIS at Penn State Erie and Owner, Millet SoftwareCommented:
The same exact logic applies to a multi-value string parameter.
Author 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;
``````
Capture.PNG
Professor of MIS at Penn State Erie and Owner, Millet SoftwareCommented:
Change
``````IF {?Group} = {?Group}[i] THEN
``````
to
``````IF {The Field you are filtering on} = {?Group}[i]
``````

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.
Senior 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
Author 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;
``````
Professor of MIS at Penn State Erie and Owner, Millet SoftwareCommented:
Nothing is forcing you to link it on the new formula.
Author 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.
Professor of MIS at Penn State Erie and Owner, Millet SoftwareCommented:
Delete the subreport parameter.
Professor 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.
Author 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.
Professor 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