No Sorting required 2

[rrgkanth]

Hi,

Using CR XI. Having a report, which generated mailing labels for member Ids entered by the user.

I have a number-static-allow custom values-allow multiple values parameter called member id.

all i have to do is generate mailing labels for those member ids which is no big deal at all.

the problem is that i need these to come up in the same order in which the parameter values are entered, but the report sorts it by member Id on it's own.

how do i get rid of that?

any help will be appreciated.

thanks
rrgkanth.

 

[synapsevampire]

The report isn't sorting anything unless you're using a grouping or explicitly using a sort, it's the way the data is being returned by the database.

Sorting by a parameter isn't supported, as it ISN'T part of the data. It's akin to saying that you want to sort by a text object.

There may be some workarounds, such as creating a subreport and passing each ID from the parameter array to the subreport as the next label to generate, not sure though.

-k

 

[rrgkanth]

Yes, I understand. I beleive that the sort is determined by what is being generated in the query. I have no explicit sorting or grouping.

I think what needs to be done is to have the selection criteria be driven by the parameters rather than by the database, though I have no clue of how that can be done.

Thanks, but I hope someone has faced this before and found a solution.

 

[synapsevampire]

The sort is probably NOT being determined by what is in the query, go to Database->Show SQL Query and you can check it.

I've offered an alternative, which is to attempt to pass the parameter values one at a time to a subreport, which is kludgy.

The selection criteria IS being driven by the parameters, it's the sortation that's your concern.

Generally the way this sort of thing would be handled is by using a stored procedure.

-k

 

[rrgkanth]

Hi,

I tried what you suggested.

I created my parameter as usual. Passed the parameter to the subreport, and in the subreport detail, displayed the address.

Embedded the subreport in my detail section of the main report.

But whats happening, is that all the parameter values get passed to the subreport, and the subreport again puts it in ascending order.

So, still get the same result!!! :-(

-rrgkanth

 

[lbass]

I'm not sure this would be a cost-effective approach, but you could create the following formula, with the negative numbers starting at the maximum number of Ids to be entered (here, 5):

if isnull({table.ID}) then 0 else
if {table.ID} = {?Spec Order}[1] then -5 else
if {table.ID} = {?Spec Order}[2] then -4 else
if {table.ID} = {?Spec Order}[3] then -3 else
if {table.ID} = {?Spec Order}[4] then -2 else
if {table.ID} = {?Spec Order}[5] then -1 else 0

Add this formula to report->sort records as your sort field. If you want to display the selected parameter options in the order selected, as a check, then use a formula like the following in the report header:

NumberVar i;
NumberVar counter := UBound({?Spec Order});
StringVar Display := "Specified Order: ";

For i := 1 to counter do (
Display := Display & (if totext({?Spec Order},0,"") <> "0" then
totext({?Spec Order},0,"") +", "));
left(Display,len(Display)-2);

-LB

 

[rrgkanth]

lbass..

Thanks a lot! It worked perfectly. Since I didnt know how many values the parameter would have, I adapted the formula as follows:

(Basic syntax)

dim iParamCount as Number
iParamCount = UBound({?Member Id})

dim iCount as number
iCount = 0

formula = 0

if isnull({MBR_Membership.MbrshpID}) then
formula = 0
else
for iCount= 1 to iParamCount
if {MBR_Membership.MbrshpID} = {?Member Id}(iCount) then
formula = (-1 * (iParamCount - iCount + 1))
end if
next
end if


I wanted to do it with Crystal syntax but got totally bugged trying to set those parameters and semicolons right.. I really need to figure it out. This gives me an error 'Number expected' at the else. Can you please tell me what's wrong there?

numbervar iParamcount := UBound({?Member Id});
numberVar iCount := 0;
numbervar iSortVal := 0;

if IsNull({MBR_Membership.MbrshpID}) then
iSortVal := 0
else
for iCount := 1 to iParamCount DO
(
if {MBR_Membership.MbrshpID} = {?Member Id}[iCount] then
iSortVal := (-1 * (iParamCount - iCount + 1))
);

iSortVal;

Thanks again,
Rrgkanth.

 

[lbass]

Try:

numbervar iParamcount := UBound({?Member Id});
numberVar iCount := 0;
numbervar iSortVal := 0;

if IsNull({MBR_Membership.MbrshpID}) then
iSortVal := 0;
for iCount := 1 to iParamCount do
(
if {MBR_Membership.MbrshpID} = {?Member Id}[iCount] then
iSortVal := (-1 * (iParamCount - iCount + 1))
)
;
iSortVal

A * for you for putting it into a variable--I didn't think of it, but should have!

-LB

 

[rrgkanth]

thanks for the *.

Yep.. this works fine too.

thanks,
rrgkanth