January 29, 2010 at 3:03 am
Hi Please help
I have two datasets in my report.
Dataset A has a list of cities
Dataset B has a list of towns
both datasets will populate the related multivalue parameters on the report.
now what I'm trying to get at is when a user makes a single selection on parameter A then parameter b will be populated with a list of towns in the selected city and if the user select two cities then parameter b should be populated accordingly.
This is what I tried:
Query A = Select Distinct City From Locations_Table
Query B = Select Distinct Town From Locations_Table Where City In (@City)
Now the problem is that when I run this report and make a single selection it works but when I make multiple selection I get an error:
Query execution failed for 'dataset b', Incorrect Syntax near ','.
Can someone please help...... I am not using any stored procedures for this report
January 31, 2010 at 1:24 pm
You would typically have a function which does this for you, however, this may be the cleanest/easiest solution if you want to utilize a report dataset. Make sure to set the parameter expression for the dataset to =Join(Parameters!<ParameterName>.value, ", ")
DECLARE @S varchar(max),
@Split char(1),
@X xml
SELECT @S = '1,2,3,4,5',
@Split = ','
SELECT @X = CONVERT(xml,'<root><s>' + REPLACE(@S,@Split,'</s><s>') + '</s></root>')
SELECT [Value] = T.c.value('.','varchar(20)')
FROM @X.nodes('/root/s') T(c)
Googling 'comma delimited string split rows sql server' will also provide you with many other techniques
February 1, 2010 at 6:49 am
I'm not sure that this link exactly addresses your issue, but you might find it helpful.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply