In a dataset that already exists within the ssrs 2008 r2 report I need execute
a stored procedure called StudentData and pass 3 parameter values to the stored procedure. The stored procedure will then return 5 values that are now needed for the modified ssrs report. My problem is I do not know how to have the dataset call the stored procedure with the 3 parameter values and pass back the 5 different unique data values that I am looking for.
Must have been having a "sharp as a marble" day!
Unless I'm misunderstanding, you should be able to used an embedded dataset in your report and base it on a stored procedure with the 3 parameters. Then if you base your report on that, the report parameters should be created automatically. The stored procedure signature would be something like:
CREATE PROC uspMyProc
@prm1 INT,
@prm2 DATE,
@prm3 VARCHAR(10)
AS
SELECT column1, column2, column3, column4, column5
FROM MyTable
WHERE SomeNum = @prm1
AND SomeDate >= @prm2
AND SomeString = @prm3
Then you would just use the stored procedure in your report - just use an embedded dataset, and you can choose the sproc you need. The designer will create report-level parameters so you can pass values to your stored procedure.
Hope I understood this time!