I use an existing stored procedure that declares these parameters:
@Unit varchar(4000),
@BegDate datetime,
@EndDate datetime,
@SortBy varchar(20)
The challenge I now face is coming up with a derivation of that stored procedure which takes any number of @Unit parameters, from 1..N
How can I do that? Is it a matter of changing the @Unit parameter to @Unit varchar(max), assigning delimited values to the parameter, and then parsing the delimited-value from within the stored procedure?
IOW, do I need to do something like this in the stored proc:
@Unit varchar(max),
@BegDate datetime,
@EndDate datetime,
@SortBy varchar(20)
...this in the C# code:
string unit = unit1 +',' + unit2 +',' + unit3 // and on for however many units are being added
. . .
sqlCommand.Parameters.AddWithValue("@Unit", unit);
...and then, back in the stored proc, parse the comma-delimited values into a string array and have it query the table like so (pseudo-dml):
string[] unitArray = @Unit.Split(',');
. . .
SELECT BLA, BLEE, BLOO FROM F_BUELLER WHERE Unit in unitArray[0], unitArray[1], unitArray[2], // and on for however many units are being queried for
...or what/how?
NOTE: If this were a "normal" project (non-SSRS, that is), I would simply use the original stored proc, calling it multiple times, and amalgamating the returned data, grouped by Unit. I don't know if that's possible or how to do it from SSRS, in an rdl/xml file, though...
What really needs to happen here is that there be a page break after each unique "Unit", the reason being that the report is exported to Excel, and the requester wants each Unit on its own separate sheet.
So basically I need to call the same stored procedure N times, plopping the results each time on its own "page."
So the tip here seems to be good ("you can create multiple datasets in Reporting Services and then use them in different elements in the report. Just add a from the Dataset: dropdown on the data tab")
The crux of the biscuit is, though: how can I provide 1, and only 1, dataset for each Unit? There is no way to know in advance how many units/sheets are going be generated/needed; there could be a couple, there could be a couple dozen.
The way I've done this is by passing in a delimited string as a report parameter.
Build a dataset in the report which shreds the delimited string into rows using XML casting. Assuming that the @Unit parameter is comma-delimited when there is more than 1 value, the dataset query would look something like this:
DECLARE @UnitXml XML = CAST('<unit>' + REPLACE(@Unit,',','</unit><unit>') + '</unit>' AS XML)
SELECT unit.x.value('.','VARCHAR(200)') AS [unit]
FROM @UnitXml.nodes('/unit') AS unit(x)
Then make a tablix that has a row for each item in the dataset.
Put a subreport in the row group of the tablix. In your case, the subreport would point to a report part showing all the info for one Unit, and passing the unit number from the dataset to your stored procedure.
The stored procedure would run once for each Unit. I realize this is neither elegant nor efficient but this is SSRS.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments