SSRS: Stored Procedure as Report Data Source

This article is a serial, make sure you've read these articles:
Creating report without wizard part 1
Creating report without wizard  part 2
Design report layout
Creating parameter for filtering
 
I made data source using SELECT query before, now I'll try to modify the datasource using stored procedure.Starting with creating stored procedure:
CREATE PROCEDURE [dbo].[spRptStudentScore]
    @Year VARCHAR(4)='%',
    @City VARCHAR(50)='%'
AS
SELECT StudentID,
       StudentName,
       [Year],
       City, Score
FROM Student
WHERE ([Year] = @Year OR @Year = '%')
      AND
      City = @City OR @City = '%')

Click Data tab on report file (.rdl)
1. Activate Generic Query Designer
2. Choose Command Type = StoredProcedure
3. Type stored procedure name in textbox area for query.
4. Click refresh button.

Parameter that exist in stored procedure will automatically become report parameter, for checking click run button.

For detail setting of parameter goto Report -> Report Parameters...

Click Preview tab to see the result.

Choose filter parameter then click View Report.

Look similar? Yes because we only modified on backstage (datasource).

Click here if you like this article.


Related Article:

1. Installing Business Intelligence SQL Server 2005
2. Creating Report with SQL Server Reporting (Wizard)
3. Creating Report with SQL Server Reporting (without wizard) | Part 1
4. Creating Report with SQL Server Reporting (without wizard) | Part 2
5. Design Layout Report
6. Creating Parameter for Filtering
7. Stored Procedure as Report Data Source
8. Parameter Data List from Database Query
9. Creating Report Group
10. Create Sequence Number Using RowNumber Function
11. Uploading Report FIle (.rdl) to Report Server
12. Remove Report Page Break/Page Break 
13. Query to Generate Subscription List 
14. Direct Printing to Printer