Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

ssrs 2008 r2 dataset call stored procedure Expand / Collapse
Author
Message
Posted Wednesday, April 16, 2014 3:47 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, September 25, 2014 11:17 AM
Points: 377, Visits: 446
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.
Post #1562487
Posted Wednesday, April 16, 2014 8:35 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 9:09 PM
Points: 886, Visits: 5,671
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.
Post #1562506
Posted Thursday, April 17, 2014 8:29 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, September 25, 2014 11:17 AM
Points: 377, Visits: 446
What do you want to see from the dataset? I have supplied the sql that I am planning to use.
Post #1562686
Posted Sunday, April 20, 2014 12:01 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 9:09 PM
Points: 886, Visits: 5,671
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!
Post #1563311
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse