Link to home
Start Free TrialLog in
Avatar of Greg Watkins
Greg Watkins

asked on

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?
Avatar of Ido Millet
Ido Millet
Flag of United States of America image

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

Avatar of Greg Watkins
Greg Watkins

ASKER

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.
The same exact logic applies to a multi-value string parameter.
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
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.
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
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

Link the subreport on MainChCfg.Name
Nothing is forcing you to link it on the new formula.
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.
Delete the subreport parameter.
Create the link again.
Let it delete the old parameter.
Use the newly created parameter to filter the subreport.
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.
ASKER CERTIFIED SOLUTION
Avatar of Ido Millet
Ido Millet
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
That worked Ido. Thanks!
Excellent. Thanks for closing the loop.