Help with a parameter

Last Post 10 Mar 2005 06:34 AM by xfonhe. 4 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
timmcowan
New Member
New Member

--
04 Mar 2005 07:01 AM
Hi All

I'm setting up a report and I want the user to be able to put in a comma separated string of parameters that will go into an IN() clause.

For example, my user wants to be able to compare periods 10, 16 and 25, he should just be able to type 10, 16, 25 into the parameter box and it would give him the report he wants. However, he might want to compare 5 periods in the same way.

Can anybody help?
xfonhe
New Member
New Member

--
10 Mar 2005 06:11 AM
I use UDFs to accomplish this. What you'll want to do is parse the comma-separated string into tabular format, then apply the IN clause to a subquery.
timmcowan
New Member
New Member

--
10 Mar 2005 06:20 AM
Thanks xfonhe, trouble is, my datasource isn't SQL server... :-(

Any other ideas?
xfonhe
New Member
New Member

--
10 Mar 2005 06:34 AM
Doesn't matter - SRS runs on SQL Server. You can populate a temporary table (or tabvar) within the dataset, then using OPENROWSET, return the data from your primary data source into a temp table (probably will have to use dynamic sql for this... I recommend sp_executesql).

The problem you'll run into then would be performance. You'll have to take the comma-separated string, parse it into a temp table, populate another temp table with data from your non-sql data source, then apply your IN clause via subquery.

OR, you could simply pass the string to your non-sql dataset and parse it to a tabular form there (may be preferable)
timmcowan
New Member
New Member

--
10 Mar 2005 10:22 PM
Great idea xfonhe, however, the remote datasource that I'm querying contains over a million rows so performance would be a real problem. I'm going to have to pass the string to the informix database and write some sql to parse it to a temp table there, never written stored procs in Informix before, this should be fun! Neat workaround mate, thanks.


Acceptable Use Policy
---