November 30, 2011 at 7:42 am
Hi, I'm having what seems like a regular problem in RS, yet no solutions I find on the web are working for me. I'm using SQL RS 2008 R2
If I create a report and check 'allow multiple values' to create a drop down box for the user, it works all the time I only select one, but as soon as I select multiple it complains about...incorrect syntax near ','
From the Available Values tab of the Parameter I'm selecting 'Get values from a query' Dataset1 with Region being selected as the Field. I had coded the query as
Select .... from xxxx where Region = @Region (I've tried using....IN (@Region) at that doesn't seem to work either.
The drop down box displays fine, but it only works when I pick one (or it did until I've made changes, now nothing works!?) So frustrating, so any help will be gratefully received, I've wasted almost a day on this!
November 30, 2011 at 8:04 am
You will need to split your inbound parameter. Take a look at the link in my signature about splitting strings. It should accomplish exactly what you are looking for.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 30, 2011 at 8:41 am
Cheers for that Sean I appreciate the info, although I've no idea where to start with that and what would need coding where!?
I've since done more tests and seen that if I create a 2nd Report Server dataset and use that to supply the list of values available to the first, the where IN (@xxxx) works....finally! I'm just finding that the report takes a while to run, presumably because I'm using 2 datasets?
Will your suggestion (or anything else!) make it faster?
Thank-you
November 30, 2011 at 9:18 am
It is a pretty drastic departure from standard thinking. 😉 Basically all you would need to change is the query for your report.
Here is an example to show you how it works.
--setup example data
create table #xxxx
(
Region int identity,
Name varchar(25)
)
go
insert #xxxx(Name) values ('Example 1'), ('example 2'), ('example 3'), ('example 4'), ('example 5'), ('example 6'), ('example 7'), ('example 8'), ('example 9')
go
--now for your query
declare @Region varchar(25) = '1,3,7' --this would be your inbound parameter
select x.*
from #xxxx x
join dbo.DelimitedSplit8K(@Region, ',') s on x.Region = s.Item
drop table #xxxx
This will probably be faster than using two datasets. If you use this, make sure you read that article and understand what it does and how it works because you will be the person business calls when something is not working right.
Hope this example helps understand how to use the function.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 1, 2011 at 4:24 am
Thanks for trying to help Sean but I still dont really understand......whats that 8k table you refer to that your joining on ??? Also I don't want to have to list the regions like that as they change all the time which is why I wanted to use' get values from a query' in the RS parameter (and just select them all) and not 'specify values.'
Thanks, Simon
December 8, 2011 at 4:02 am
Hi Simon, I just tried and worked this way.
Step 1: Add your query without any parameters within where conditions of the query.
Sample Query:
SELECT ResellerName, AnnualRevenue, YearOpened
FROM DimReseller
Step 2:
2a.Right click on Parameters folder in Report Data Pane.
2b. Add a new Parameter -- Refer to screen shots ReportParameterProperties1 & ReportParameterProperties2
General Tab: enter Name, Prompt and then select "Allow Multiple Values"
Available Values: Select "Get Values from Query", Select dataset, Value field you would like to source the unique values and Label field.
Click OK
Step 3: Right click on tablix and select "Tablix Properties" -- Refer to screenshot TablixProperties-Filters
Click on Filters (left side on Tablix Properties), Select the Expression (field), Operator must be "In", Value -- click on fx (expression button).
This will take you to expression box, Click on "Parameters" from bottom part of the window, double click on the parameter you created in step 2b.
Expression appears in the top section of the expression window: =Parameters!Reseller.Value(0)
As this is multivalued parameter you have to remove "(0)" so the expression should look like =Parameters!Reseller.Value
Refer to screenshot "FilterExpression"
Click Ok and OK again.
Now preview the report change the selection to Select All or select few values or one value and hit View Report button.
It works exactly what you are after...
I have no idea why it doesnt work when you simple have a where condition with "in" 🙂
Hope this helps!!! Good luck and let me know how you go.
regards
Natraj
December 8, 2011 at 4:43 am
Natraj, thanks for your time on this, it works very well. Many thanks for that
Rgds, Simon
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply