columns as parameters in Reporting Services
-
Hi, i need some assistance. Im trying to create a report in Reporting Services and cant seem to get it to work. I want to let the users select a column that contains a value they want but it seems like the columns aren't passing correctly. Here is my dataset
SELECT
column1, column2
FROM table1
where @column = @value
the second dataset contains the name of the columns passing the @column parameter.
I've even tried to use the ''+@column+'' and '+@column+'
report runs fine but wont pass the @column parameter
any advice?
-
How many columns are we talking about here?
If the number is small you could create a dataset for each of the columns and you could put some logic in to change datasets depending on the column parameter.
Additionally, you could write a stored procedure and call that passing in the column name and other variables and put it together in the sproc. The biggest drawback there would be that the optimizer wouldn't be able to do cache reuse or many of the other optimizations we like so much like proper index usage and whatnot.
-Luke.
-
Luke L (10/6/2008)
How many columns are we talking about here?If the number is small you could create a dataset for each of the columns and you could put some logic in to change datasets depending on the column parameter.
Additionally, you could write a stored procedure and call that passing in the column name and other variables and put it together in the sproc. The biggest drawback there would be that the optimizer wouldn't be able to do cache reuse or many of the other optimizations we like so much like proper index usage and whatnot.
-Luke.
First off, thanks for the help
2nd dataset already contains the column names. Its just not passing it to the first dataset correctly. It works for @values but for some reason it wont for the @columns
-
Never Mind about the additional datasets, for some reason I was thinking you could use an expression to switch the dataset for your table/matrix etc during report execution, apparently I was incorrect.
Something you could experiment with is a CASE expression in your where statement. Depending on your environment you'd want to be mindful of the possibilities of SQL injection, so you want to give the users a list to select from, not just a textbox to type in what they want. also you'll need to test to see how it performs in your environment.
Something like...
SELECT column1, column2
FROM table1
WHERE CASE @column
WHEN 'column1' THEN [column1]
WHEN 'column2' THEN [column2]
END = @value
-
i am fully aware of SQL Injection and the parameter is built to where the users are selecting from a dropdown box based on the 2nd dataset column names. I will test out the case statement later on and see how it goes.
thanks luke
-
that worked... thanks
another question i have is.. how would a user be able to use the multiselect function to run a report with the same requirements... since reporting services uses single quotes along with commas for multiselect.. so the end result will search both columns for the values
for example
set @column1 = ('column1','column2')
Select column1, column2
from table1
where @column1 = @value
-
Multi select is a whole different animal all together. For that you'd probably need to pass the values into a stored procedure, parse them out and put it together with some dynamic SQL. You also need to accept various values in a multi select? This again would need to be parsed somehow, although I would think you might have difficulties trying to match value1,value2, value3 with Col1, col2, col3. You also may run into issues with datatype being different and such.
-
all of the columns will be casted as FLOAT. I just one multiple columns searching for 1 value that they want. Like if they wanted to search for "TEST" on both column1 and column2. Instead of going through the search 2 separate times.
-
If you're attempting to match text have you given any thought to using full text search? Today's newsletter had an article by Michael Coles which may be of interest to you...
http://www.sqlservercentral.com/articles/Full-Text+Search+(2008)/64248/
-
ive thought of full text search before but thats not what the users want. They just want to simply choose the column that they want and filter out what they need.
-
If you right click the table object on the layout designer tab and click properties, you will see that there is a Filters tab in the Table Properties dialog. You could use an expression in the Filters tab.
=IIF(parameters!column_name.Value = "column1", fields!column1.Value, parameters!value.Value)
Use an IIF function like this one in the expression box that evaluates to whatever parameter you have tied to @value if false so that if the user selects a column other than column1 then the report will return all rows.
I have a report that does something similar but with the sorting tab, so my users can get "dynamic" sorting for the records.
-
I should also add that I've never actually tried this with filtering, but it might work. No guarantees 😀
-
The filtering on a variable column name will work with the CASE statement and it has the added bonus of only pulling in the data that you need. Filters return the entire result set and then filter it.
The issue with either approach would be the ability to use a multi select parameter for the columns to search. I think the only way you'd be able to do it would be to pass a multi-select param through to a stored procedure and then use a tally table or some other means to parse it into the separate column names. You'd have to use some dynamic SQL to build your case statement to accommodate for the multiple available selected columns.
-
Just a thought, but perhaps you could consider having part of your query generate an interim table that contains one column that has ALL the searchable columns joined together into a single fixed length one, such that each contributing column is converted to fixed length text.
Then create a "match string" from the parameters supplied, that uses a fixed number of underscores (same number as each column value is converted to) to represent columns not being searched, and then appropriate wildcards for each column that is being searched, using underscores instead of % to fill out to the full fixed length of each column being searched. This might be a good way to avoid a lot more hassle.
Steve
(aka smunson)
:):):)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply