Batch Printing
-
Hello There! In my minds eye, this is a tricky question. Maybe I'm making it harder than it actually is, but I am near stating that SSRS is incapable of producing the following request.
I have created a report that takes in a parameter. Let's call it ID. This ID is a foreign key in another table that has individual steps for that particular ID. In the report that I have created it outputs some fields from the core table, then pulls in a subreport that takes in the ID as a parameter (=Parameters!ID.Value). The single report works perfectly. However, let's say I want SSRS to produce the output to print 25 ID's. By changing the query or stored proc to pull where ID IN (@ID) -- and if id's '1,2,3,5,6,8,9,23' were passed in to the report, the report is returning the parameter is out of scope error when getting to the subreport.
I personally feel that this request is flawed at best due to the way the printing would end up on the web page anyhow, but I am just trying to make it work if possible.
Any ideas or perspectives on this is greatly appreciated,
Brian K. McDonald
-
Hi
Could you give some details of yr table data so i can understand that tricky part
?thx
JV -
Of course! I'll shrink it down a little bit, but you'll get the picture.
Table 1:
- SOPID int --PK
- Title varchar(100)
- Procedure varchar(max)
Sample Records:
- 71,'Clean the windows','A lot of details about this procedure'
- 72,'Replace the door','Some details on replacing the door'
- 73,'Fly to the Moon','Get on the space shuttle and some more info'
Table 2:
- StepID int --PK
- SOPID int --FK
- SequenceNumber int
Sample Records:
- 101,71,3
- 102,71,2
- 103,71,1
- 104,71,0
- 105,72,1
- 106,72,0
- 107,73,2
- 108,73,1
- 109,73,0
So, Table 2 has multiple records that refer to the SOPID in Table 1. The sequence number is the step number of the SOP in reverse order. For example, SOPID 71 has four total steps, Sequence Number 3 is the first step, 2 is the second step and so on. SOPID 72 has two steps.
I do a simple join on these tables in a stored proc that accepts @SOPID. I filter the records by WHERE table1.SOPID IN (@SOPID).
When I pass in the values into the report using SOPID = 71,72,73 it fails. However, when I execute the stored proc in SSMS it runs fine and returns all of my records.
At the report layer: I have a Report Level Parameter of @SOPID. The report has a subreport that pulls each of the steps from TABLE 2 using that passed in @SOPID. The failure occurs when the SOPID is passed into this subreport..stating out of scope.
I hope this will help you! Please let me know if you have any ideas.
Thank you,
Brian
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply