April 16, 2014 at 3:47 pm
I am modifying an existing SSRS 2008 r2 report. 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.
The basic dataset is the following:
SELECT SchoolNumber,
SchoolName,
StudentNumber,
from [Trans].[dbo].[Student]
order by SchoolNumber,
SchoolName,
StudentNumber
I basically want to pass the 3 parameters of SchoolNumber, SchoolName, and StudentNumber to the
stored procedure called StudentData from the data I obtain from the [Trans].[dbo].[Student]. The 3 parameter values will be obtained from the sql listed above.
The columns that I need from the stored procedure called StudentData will return the following data columns
that I need for the report: StudnentName, StudentAddress, Studentbirthdate, StudentPhoneNumber, GuardianName.
April 16, 2014 at 8:35 pm
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.
You would have to create a stored procedure to return all the columns/rows that you want. You could do it by calling other stored procedures, if necessary. Without seeing your database structure, it's hard to tell.
April 17, 2014 at 8:29 am
What do you want to see from the dataset? I have supplied the sql that I am planning to use.
April 20, 2014 at 12:01 am
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!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply