Usually developers like having full control over their reports but what happens if you have someone designated to build reports who does not quite know the backend schema. A good way to separate the building of the data for the report and the report design could be stored procedures. Now I consume stored procedures using Oracle which is not much different consuming stored procedures with SQL Server, however building the procedures is much different between the two. Even though I mention and show examples of stored procedures this is not an article for building them, just a guide for consuming a stored procedure within a Reporting Services Report.
Some developers may have dedicated DBA’s to build the stored procedures however power is knowledge by having the low down of how stored procedures are created. Let’s take a look.
Creating a Stored Procedure
Here are examples of creating SQL Server and Oracle stored procedures…
SQL Server Example
SQL Server Stored Procedures seem a little easier to build then Oracle. This stored procedure called ‘Sales by year’ taken from the infamous NorthWinds Database, takes two parameters which are dates and then returns back some data. This is a lot like what we do with our embedded SQL statements within our code.
ALTER procedure [Sales by Year]
@Beginning_Date DateTime, @Ending_Date DateTime AS
SELECT Orders.ShippedDate, Orders.OrderID, "Order Subtotals".Subtotal, DATENAME(yy,ShippedDate) AS Year
FROM Orders INNER JOIN "Order Subtotals" ON Orders.OrderID = "Order Subtotals".OrderID
WHERE Orders.ShippedDate Between @Beginning_Date And @Ending_Date
Oracle Example
In this example, the Oracle stored procedure is passed a value and is returned a REF CURSOR which as a vb or c# developer we can think of as a recordset or dataset
CREATE OR REPLACE PACKAGE Types AS
TYPE cursor_type IS REF CURSOR;
END Types;
/
CREATE OR REPLACE
PROCEDURE My_SP (p_Value IN TableName.Field%TYPE,
cur_data OUT Types.cursor_type) AS
BEGIN
OPEN cur_data FOR
SELECT FIELD FROM TABLE
WHERE UPPER(FIELD) LIKE '%'||p_Value||'%';
END My_SP;
/
Now that we have seen what these buggers look like, what do ya say we use them within our report?
Creating a Report That Uses a Stored Procedure
1. Open Visual Studio .Net 2003
2. Create a new Report Project
3. Create a new Shared Data Source by right clicking on ‘Shared Data Sources’ and selected Add New Data Source
4. Give the Data Source a name=>select the type of database=>type in a connect string to connect to the database=>Select the credentials tab and click the radio button ‘Use a Specific User Name and Password’=> enter the credentials to be used.
5. Now we have a Shared Data Source for this project to use for connecting to a database.
6. In the solutions provider for the Report Project, right click on the Reports Folder and select Add=>Add New Item
7. Select Report and give it the name ‘spReport.rdl’
8. Under the Data Tab of the report, Create Dataset for the report by using the Dataset Dropdown box, under the Data Tab. Drop it down and select <New DataSet…>
9. A new form will popup asking for the new dataset name. Use ‘Myds’
10. Select the Data Source we created above
11. Dropdown the box for Command Type and select Stored Procedure
12. We have now created the Dataset that will be used by our stored procedure
13. Now if you look in the upper right of the report you will see a textbox labeled Stored Procedure which is where you put the name of the procedure you want to use for your report.
14. Click the Layout Tab of the report to see the fields tab. These fields are associated with the Stored Procedure through the Dataset that can be added as fields for the report.
15. Drag and Drop the fields from the fields tab, that you want to be seen on your report.
16. Click the Preview Tab of the report to view the parameters that need to be entered for the report.
The use of Stored Procedures can save you time as a developer or Report Writer. Please look at my other articles on creating a custom Report Viewer for viewing this report through a web application.