• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2759
  • Last Modified:

ssrs parameter on dataset from tablix row

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
0
TSG954
Asked:
TSG954
1 Solution
 
DataFieldCommented:
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
0
 
TSG954Author Commented:
Thanks, what would be the syntax for #1. the value of column resultA.
0
 
roshnipatelCommented:
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.
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
TSG954Author 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?
0
 
roshnipatelCommented:
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.
0
 
TSG954Author Commented:
Cant do the first suggestion,
Tried the second suggestion however, it says only fields from the current dataset can be added.
0
 
roshnipatelCommented:
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.



0
 
sureshbabukrishCommented:
or you can try using a subreport
0
 
TSG954Author Commented:
Unfortunately, it doesnt seem to like the Parameters!ParameterB.Value in the select stmt.
Says incorrect syntax near '!'.
0
 
TSG954Author Commented:
Subreport may be the way to go. Any examples out there?
0
 
roshnipatelCommented:
http://msdn.microsoft.com/en-us/library/ms160348.aspx
goes over how to create a subreport pretty nicely
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

Tackle projects and never again get stuck behind a technical roadblock.
Join Now