CODEWITHSUNDEEP
sqlreporting-servicesssrs-2012
RJ27
RJ27

Reputation: 23

SSRS - Select query on dependent columns

My report has stored procedure as a dataset and returns several columns. I am trying to add a new column which depends on one of the columns data. I have to get the dependent column data for that row and run a select statement on another table and return a table column data which matches the dependent column data in the report and the table. It is like a join on the common column.

Here is the sp output
col1  col2  col3
3456  Name1 Address1
5678  Name2 Address2

Here is the table
col1   tblcol2   tblcol3
9876   Name1     Address3
1234   Name1     Address4

So, in the new column, Address3 and Address4 should be displayed in the report for that Name1 row.

I tried using LOOKUP() in the expression box in the following way =LOOKUP(Fields!col2.Value, Fields!tblcol2.Value, Fields!tblcol3.Value, "DataSet2")

But this gives an error on the report

enter image description here

The report works locally when I do not add the new column, but the new column with that expression gives that error.

Please let me know if there is a way to accomplish my scenario?

Thanks.

Upvotes: 0

Views: 186

Answers (2)

RJ27
RJ27

Reputation: 23

I have resolved the issue by using LookupSet function expression and comma separated values by following Microsoft docs

Upvotes: 0

wedrowycz
wedrowycz

Reputation: 36

The easiest way is to create parameters which rely on dataset.
1. Create dataset (query)
2. Create parameter and apply avialable values from that dataset
3. Create next query which consumes this parameter for example parameter is named "param1"

WHERE field1 = @param1

then draw your data as table.
Depending on type you should be able to choose from combo box (dropdown)
Another way (no question about param value) is to create subreports
In this case keep in mind order of query execution .

Upvotes: 0

Related Questions