We help IT Professionals succeed at work.

ssrs parameter on dataset from tablix row

TSG954
TSG954 asked
on
3,426 Views
Last Modified: 2013-11-05
Novice in SSRS using SQL Server 2008, BIDS.
I'd like to know how I can Query a second dataset based on the value of a row in a tablix, so that I may then turn around and display the value from returning query on another column in the same tablix.
Any point of direction would be appreciated.
Thanks
Comment
Watch Question

1.Create a new report parameter called ParameterA. Set both the available values and the default values to the value column resultA.
2.Create a second dataset B. Add ParameterA as a parameter to QueryB, called @parameterA.
3.In your query, use the parameter like this:

SELECT col1, col2 WHERE valueColumn IN (@parameterA)

http://www.sql-server-performance.com/articles/reporting/report_parameters_p1.aspx
http://msdn.microsoft.com/en-us/library/ms155917(v=SQL.100).aspx
http://msdn.microsoft.com/en-us/library/aa337292.aspx

Author

Commented:
Thanks, what would be the syntax for #1. the value of column resultA.
When you create the paramenter, you should be able to set the value as non-queried or From Query. You should select From Query and the value from QueryA.

http://www.sql-server-performance.com/articles/reporting/report_parameters_p1.aspx 
has a good example.

Author

Commented:
Thanks,  I'm now receiveing the data JobOrderID in the parameter, i'd like to take it a step further and use dataset2 field Placement and add into the tablix. Is this possible?
I would recommend merging the two datasets into one (if the data is coming from the same database), but if it's not possible for you to do that, then...

You should beable to create another parameterB (set it as Multivalue and set it's value to dataset2.Placement) . Then add the parameter to your tablix.

Author

Commented:
Cant do the first suggestion,
Tried the second suggestion however, it says only fields from the current dataset can be added.
You may need to create a third dataset (DatasetC) that is exactly the same as DataSetA, but has ParameterB included in the select stmt.

ie.  SELECT col1, col2,.., Parameters!ParameterB.Value AS col3....

Then bind datasetC to the tablix

You can do this by:
In data set properties of datasetC, click on parameters tab and use JOIN() funtion as:

=JOIN(Parameters!ParameterB.Value, “,”)
You should be able to add ParameterB in the fields list.



or you can try using a subreport

Author

Commented:
Unfortunately, it doesnt seem to like the Parameters!ParameterB.Value in the select stmt.
Says incorrect syntax near '!'.

Author

Commented:
Subreport may be the way to go. Any examples out there?
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.