SQL Reporting Service And Business Intelligence Development Studio With Stored Procedure

Introduction

The following is a step-by-step illustration of creating a “Raw material stock closing report” using SQL Reporting Services and Business Intelligence Development Studio with a stored procedure.

Create an SQL report as described in the initial steps of the following article “Using report service and Server Business Intelligence Development”: 

Now we will configure a data source.

In the Report Data panel, click on “New”>>”Data Source”.

Now click on New>>Data Set.

Select “Stored procedure” and click on “Query Designer …”.

Write down the SP name and click on the “Query designer . . .”. 

Execute the stored procedure in the query designer. This will help the wizard to identify the parameters of the Stored Procedure (if any) and the schema of the returned table (fields of the dataset). 

Choose the query parameter value if required. 

Select fields if required (mostly it is mapped correctly if we execute SP in the query designer as mentioned in the above step.) 

Click on OK to save the dataset property and close the popup.

Now add a table object in the report.

Drag fields from the data source in the report data to the table. Also, add a total field.

Using the format menu, you can format the reports. I have added color to the header of the report.

We can also set some custom properties like the Number format for quantity should be 12345.000 (as quantity can be 5.750 KG also), alignment, font style, border, and color.

Hit the Run button, enter the input parameters, and the report is generated. You can publish your report to the Reporting Services portal and access the portal also.