SSRS Using some Field Values from Dataset1 as Parameters in DataSet2

Question

I have a dataset referring to a stored procedure which delivers me some data. One column contains a specific Code (see picture), which I want to use in the second table/dataset. For each of the Codes (amount is variable) I want to add a new table with the details of that code.

The second dataset also feeds a stored procedure. Both procedures can´t be touched, because they are used in many other applications.

how to realize?

I tried to use the "lookupset" function in SSRS after searching the web for a solution. I got different problems / errors. Sometimes no parameters specified, sometimes field value cant be used as a parameter. Please help.

Answer 1

I would probably create a new dataset that just gets the code from your stored proc (it will run the SP again but not too much can be done about that).

Here's a simplified example..

Let's say you SP only return 3 columns ( MaterialNo, MaterialName and Code)

You can create a temp table to put the results into and then query the temp table so your dataset query would look something like this..

CREATE TABLE #t(MaterialNo int, MaterialName varchar(50), Code varchar(50))
INSERT INTO #t 
    EXEC myProcName
--
SELECT d.*
    FROM myDetailsTable d
    JOIN #t t on d.Code = t.Code

Now you have the data you need you can create a table, grouped by code and add whatever details in you need.

Answer 2

Simplest solution here is to use a Subreport

A subreport is a report item that displays another report inside the body of a main paginated report. Conceptually, a subreport in a report is similar to a frame in a Web page. It is used to embed a report within a report. Any report can be used as a subreport. The report that is displayed as the subreport is stored on a report server, usually in the same folder as the parent report. You can design the parent report to pass parameters to the subreport. A subreport can be repeated within data regions, using a parameter to filter data in each instance of the subreport.