Comma Separated String Parameter

[organickarma]

I have a report which has parameters from the main 'container 'report which is a single valued parameter being passed to a subreport via a direct parameter to parameter link.

I am simulating it to behave as a multivalued parameter by asking users to enter multiple values as a comma separated string
The parameter gets passed to the oracle command object in the subreport for filtering purposes.

The values entered for the parameter ID are for e.g. 163,77,100

The Where clause in my subreport command is as follows

Where
1= CASE WHEN INSTR(',' || '{?ID}' || ',' , ',' || TRIM(table.ID) || ',',1 ) > 0
THEN 1
ELSE 0
END

Everything tells me that this should work, yet it does not.

Was hoping another set of eyes might be able to point out what I am doing wrong here.

It appears as though the string resulting from this

,163,77,100, does not seem to be recognized as is by the oracle command.

Any ideas appreciated.

Thanks,

A

 

[lbass]

There is an faq by RustyAfro on this very topic: faq766-6779.

-LB

 

[organickarma]

Thanks LB, I looked at this solution. However it works if we use a multi valued parameter. The issue here is that the user does not want to enter each value and click the add (yes I know anal but *rolls eyes*), so I used a single valued parameter.

i guess the solution I am interested in is mentioned is this line on the thread

"There is also a method by presenting the user with a single value parameter and them manually input a comma delimited string – but then you lose CR’s ability to present a List of Values for the user to select and you have to trust the user to enter the string properly or risk a scary error."

However I could not find any details on it. Anybody know how this can be done?

Thanks,

A

 

[organickarma]

Additionally I have another parameter, lets call it {?Desc}

This is a multi valued parameter and I select 3 values A B C from a pick list on the main report.

This is used as a filter on the subreport command as

Where 1 = Case When 'All' in ('{?Desc}') or table.field in ('{?Desc}')Then 1 Else 0 End


Strangely, this works straight off the bat without having to use the Join based solution provided in the threads.

PS: I am using CR XI R2. Have they changed something in this to allow multiple parameter reading to be more direct without having to parse the array? And I don't understand why single valued parameters are failing.

 

[organickarma]

Oops please ignore last post .. I am using the join method to parse the array. I should go home..my brain is not functioning!!

 

[organickarma]

Just for closure, I finally figured this out.

I was concatenating commas to my subreport parameter as follows

',' || '{?ID}' || ',' LIKE '%,' || table.field || ',%' and it would not work

I moved ',' || '{?ID}' || ',' to a formula on the main report and linked to the formula and it worked. Not sure if this is because it was an Oracle command via ODBC. Either way it worked!

 

[lbass]

I thought they didn't WANT to enter multiple values? Not sure what you entered on the main report or how the final command looked.

If the user wanted to enter just a single value for a string field, it would be entered like this in the main report:

'FedEx','Loomis','UPS'

...and in the command in the subreport, you would set it up like this:

where Orders.`Ship Via` in ({?Shipper})

...without the usual single quotes around the string parm. But this is much more complicated than just selecting multiple values from the pick list and then using the faq approach.

-LB