Sorting a group by order of paramater values received

Greg Watkins
Greg Watkins used Ask the Experts™
on
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?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Ido MilletProfessor of MIS at Penn State Erie and Owner, Millet Software

Commented:
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

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.
Ido MilletProfessor of MIS at Penn State Erie and Owner, Millet Software

Commented:
The same exact logic applies to a multi-value string parameter.
Become a CompTIA Certified Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

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;

Open in new window

Capture.PNG
Ido MilletProfessor of MIS at Penn State Erie and Owner, Millet Software

Commented:
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 Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
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;

Open in new window

Ido MilletProfessor of MIS at Penn State Erie and Owner, Millet Software

Commented:
Link the subreport on MainChCfg.Name
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.
Ido MilletProfessor of MIS at Penn State Erie and Owner, Millet Software

Commented:
Delete the subreport parameter.
Create the link again.
Ido MilletProfessor of MIS at Penn State Erie and Owner, Millet Software

Commented:
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 Software
Commented:
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.

Author

Commented:
That worked Ido. Thanks!
Ido MilletProfessor of MIS at Penn State Erie and Owner, Millet Software

Commented:
Excellent. Thanks for closing the loop.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial