0

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?

see the relationship

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.

Dale K
  • 25,246
  • 15
  • 42
  • 71
ScarfaceNo1
  • 11
  • 1

2 Answers2

0

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.

Alan Schofield
  • 19,839
  • 3
  • 22
  • 35
0

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.

David Browne - Microsoft
  • 80,331
  • 6
  • 39
  • 67
  • Thank you for this hint. But how do I get the LotCodes from the first dataset to the parameters in the second, third, fouth dataset? – ScarfaceNo1 Aug 21 '23 at 11:33
  • Passing parameters to subreports is covered here: https://learn.microsoft.com/en-us/sql/reporting-services/report-design/subreports-report-builder-and-ssrs?view=sql-server-ver16#using-parameters-in-subreports Also plenty of videos and tutorials online. – David Browne - Microsoft Aug 21 '23 at 13:46
  • Thanks, but all the hints I found only describe how to add a subreport inside the main report with relations to the explicit fields. None of these describes how to extract some of the field values to use them in a seperate table. – ScarfaceNo1 Aug 22 '23 at 13:19
  • The subreport has it's own data sources and datasets. You just pass parameters to the subreport from the main report. – David Browne - Microsoft Aug 22 '23 at 13:30
  • Yes, I see. But when I use the subreport inside the main report, I can just take Field result by using the column name. There is no need to write a fuction or something. When using the Field result in a seperate table I can´t access it in the same way.. – ScarfaceNo1 Aug 22 '23 at 13:46
  • My Question is how to access these values? – ScarfaceNo1 Aug 23 '23 at 07:31