Multiple Parameter Challenge

[jmskarp]

Hello,

I am trying to resolve a challenge with allowing users to select multiple parameters from a database. The challenge is that the application from where the report is launched from does not allow/support multiple parameter selections and the data is constructed in a way that I get more results than I am trying to extract.

CRV = 9
Oracle Db
************start of data*************
the field name in the database is "DocType" and the values of the data within the database are:

DocType

ccal
ccal_form
cval
cgen
cgen_form
gen

************end of data*************

I have a parameter to allow the user to enter multiple values separated by a comma. Here is the formula portion that relates to the selection of the DocType:

If lowercase({?p_docType}) = "all" then
true
else
lowercase({Command.DocType}) in lowercase({?p_docType})

The problem is that if a user enters "cgen" the results returned also include "gen" or any substring of the value they enter.

Any ideas to limit the results returned to the values entered by the user are appreciated.

Thanks in advance.

 

[rosemaryl]

Dear jmskarp,

That is because the application is returning the comma separated list as a string. I just ran into the exact same problem at a client site.

I resorted to a Stored Procedure for my report to overcome this. Does your application support reports written against Stored Procedures?

Many of things that you can do in Crystal will make the record selection not pass to the SQL Where Clause.

Is that important to you?

regards,

ro





Rosemary Lieberman
rosemary-at-microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762

 

[synapsevampire]

And you're using this formula where?

It won't work in the record selction formula, as you mentioned, the coders of the app didn't understand Crystal, so you're in deep kimchee.

If you can use stored procedures within the app, then you might parse the parameter values out in the stored procedure and apply them.

Now I might have a cheat here, it won't pass to the database so it will be slow, but it should work:

Try:

{table.field} in '["'+join({?MultiParameter},'","')+'"]'

-k

 

[synapsevampire]

In fact I tested it and it does work, it's just sloooooooooweeeeeer as the processing is done in Crystal.

-k

 

[jmskarp]

Synapsevampire,

The parameter is not multi select. It is a string, not an array. Great thought though....

Let me know if you have any other thoughts. I will try to manipulate the string in a formula using the join priciple.

Thanks.

 

[Turkbear]

Hi,
Actually the string Synapse's code should produce:
["Value1","Value2",'Value3"]
when used with the IN operator, IS an array....








To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[jmskarp]

Hello Turkbear,

Thank you for your response.

Maybe I am not understanding or clear...? Not sure which, but when I tried the recommendation above, I received the following message: A string array is required here. The parameter field in the following listing was highlighted: '"'+join({?p_docType},'","')+'"'

Thus, my comment above. My parameter is type string, but does not have the option of multi parameters selected because the functionality does not exist in the application in which this report will be embedded within. Thus my parameter is one long string, not an array of values. I am thinking of trying to use the Replace function to manipulate the string.

Am I making sense? Please let me know.

Thanks in advance.

 

[rosemaryl]

Dear Jmskarp,

No, what is happening to you is because of the way the application passes the string. It passes:

'Value1,Value2,Value3' when you try to join this you indeed get an error string array required here.

Change his code to:

'["'+join(split({?Parameter}),'","')+'"]'

regards,

ro

P.S. As previously advised, it will be very slow as this must be processed by Crystal.

ro

Rosemary Lieberman
rosemary-at-microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762