• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2603
  • 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
Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

 
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

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

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