To Start the Reporting service -> Start - > All programs - > Microsoft Sql server 2005 - > Sql server Business Intelligence Development Studio.



Create a New Project



As Report server project



Once the project is created, Reports and Shared Data sources folder are created by default. The Shared Data sources will act as a Data source for the report and the report contains the rdl files.



In order to connect the database and the reporting service the data source is used. To create the data source right-click on the Shared Data Sources and click “Add New Data Source”.



Shared Data source Window will pop-up asking the user to fill the Data source information. To add that Press the Edit button.



Enter the Server name if it is from other PC, otherwise enter ‘.’ To represent the same PC Database and provided the Sql server authentication and based on the Server Name it displays the Database information. Select the Database that is going to be used for the reports. After that press the “Test Connection” button to make sure the connection is established properly.



Once the connection is created the Data source Name is displayed under the “Shared Data Sources”.



To add Report – Right-click on the Reports -> Add - > New Item



A pop-up will appear and asking the user for the Report Name.



Once the “Add” Button is pressed the created Report is displayed under the “Reports” in the Solution Explorer.



Create the Dataset for the Data source to display the report



On clicking the New Dataset link a pop-up will appear asking the user whether the dataset is going to be a inline-query or stored procedure. If it is stored procedure give the Execute statement of the stored procedure with the parameter name. If it is Inline query write the query inside the Query string.



In this example I am writing an Inline Query as “select * from temp2 where empno > @Empno”. To add the Report Parameter , Menu – Report – Report parameters





By Default the Empno is added in the report parameter, if more needs to added it can be added thro this method or adding the condition with parameter in the inline query. Thro Inline query again I add a parameter thro the design mode as “Sal”. Now the query is modified as “select * from temp2 where empno > @Empno and sal > @Sal” The selected columns are displayed in the Dataset. Now to display the report drag a table from the tool box and drop it in the Layout, since it retrieves more than one value. By default the table is created with the Header, Detailed and Footer row. If need any row can be added on right clicking the row and press the “Insert above” or “insert below” button. Then Drag and drop the Dataset values in the Table in the detailed row. To view the report press the preview button next to the layout tab.





Enter some values in the empno and sal textboxes and press the “View Report” button. It displays the report based on the applied filter. We can able to export the report in the required format.