How to print Multiple Values in report with SQL Commands? 1

[CrysRptDev]

Hi everyone,

I am using CR XI and Oracle 10g.

I had created a report with SQL query inserting in a Command and I followed this FAQ/hints in this link

http://www.tek-tips.com/faqs.cfm?fid=6779

to create multiple values parameter and pass it within subreport. Everything works perfectly without any problems. However, when it comes to print multiple values parameter in my report, problem occurs.

I have 6 possible values for this parameter. When user selects a value or multiple values then it takes a string and pass it to sub report. Basically, it would take 'Tables','Chairs','Sofas' string and passes it to subreport within IN statement (Ex: District_name in (?Parameter) in a Command.

The problem i have is writing a formula and determine if parameter has all values. If the user hits double arrow in Multiple Values Parameter, then it is fine, it should print 'ALL' in the report. But if user selects randomly all 6 values not in order then my formula that I write base on the string will be long because combination of 6 values can be large and all it should print is "ALL". And if I don't write a formula then I will get a long string like 'Tables','Chairs','Sofas','Spoons','Chopsticks','Bowls', which is too long. And all of these values can be selected not in order. All I want is to print "ALL" in my report.

Ex.

@Formula_1
If (?parameter) = "'Tables','Chairs','Sofas','Spoons','Chopsticks','Bowls'" then
"ALL"
if (?parameter) = "Tables','Sofas','Chopsticks','Chairs','Bowls','Spoons'" then
"ALL"
....

It takes too long to write. Any ideas on how to write a formula for this problem?

Thanks,

-A

 

[lbass]

Try:

if ubound(split({?parameter},",")) = 6 then
"All" else
{?parameter}

-LB

 

[CrysRptDev]

Cool, that's worked! Thanks!

 

[CrysRptDev]

Btw. Do you know how to remove single quote, change 'Tables','Chairs','Spoons' to Tables, Chairs, Spoons ?

Thanks

 

[lbass]

if ubound(split({?parameter},",")) = 6 then
"All" else
replace({?parameter},"'","")

-LB

 

[CrysRptDev]

Great. Thanks!