How to create a report using SQL Server 2012 reporting services

In this article i would like to give a demo as to how to create a report using SQL Server 2012 reporting services. I have been developing reports using this tool for some time and this attempt is to share my knowledge for the same. In this article i am assuming that SQL Server Reporting services is installed and is in a running state.

Step 1: Open the Report Manager Home page
Step 2: Click on the Report Builder tab.

Step 3: Once the Report Builder opens, you will see the following screen -> Click on “New Report” -> “Blank Report”
Step 4: Once click on “Blank Report” you will find the below scree. This is the report builder where we will develop our first report. The while blank area is the body of the report where we will insert a matrix. On the left hand panel are the data properties based on which the report will function. Let us go through each of the terms that we see there.
a) Built-in-Fields: Built in fields help us render in built functionality on our report page. For example if we want page number on each of our pages in a report we can use the “Page Number” field from here. We will be using them in our demo.

b) Parameters : Parameters help us to build reports based on a certain input. For e.g if we want to extract some information based on 2 dates, then we need to define parameters based on which the report will pull data and display.

c) Images: This will help us add images to the report.

d) Data Sources: very important aspect of Report Builder(RB). Data source help us define and maintain the source from which we will pull data for the report. I will explain Data source with more clarity once we start developing the report.

e) Datasets: A dataset specifies a query, query parameters, filters, and a field collection. It might be a query, table or stored procedure.
Let start to build our report now.

Step 5: Right click on Data Sources -> Add Data Source
Step 6: You will see the below screen once you click on Data Source properties. Choose the below depicted options and click on the build button
Step 7: Once you click on the build button, the “Connection properties” dialog box will come up. In the server name field, type in the name of the SQL server from where you want to pull data. Once you feed the server name, select the name of the database from where you want to pull data. In our example we have used the [AdventureWorksDW_WroxSSRS2012] database. Click on “Test Connection” button to verify the connection. Click on OK.
Step 8: On you click on OK you will see the “Data Source Properties” window with the connection string embedded. Click on OK. Now your data source is ready
Step 9: Now we need to create a new dataset for our report to access.Right Click on “Datasets” -> The Data set properties dialog box pops up -> Under the name type in a name of the dataset. Choose “Use a dataset embedded in my report” option and choose Datasource1 from the dropdown. Select query type as “text” and paste the below query in the text box
SELECT vResellerSalesProdTerrDate.CalendarYear ,vResellerSalesProdTerrDate.CalendarQuarter
,vResellerSalesProdTerrDate.MonthNumberOfYear ,vResellerSalesProdTerrDate.MonthName
,vResellerSalesProdTerrDate.Category ,vResellerSalesProdTerrDate.Subcategory
,vResellerSalesProdTerrDate.ProductName ,vResellerSalesProdTerrDate.SalesTerritoryGroup
,vResellerSalesProdTerrDate.SalesTerritoryRegion ,vResellerSalesProdTerrDate.SalesTerritoryCountry
,vResellerSalesProdTerrDate.SalesAmt ,vResellerSalesProdTerrDate.OrderQty
,vResellerSalesProdTerrDate.Frieght FROM
AdventureWorksDW_WroxSSRS2012.dbo.vResellerSalesProdTerrDate

Click on the fields tab below the query tab and you find the fields populated. Click on Ok and you will find your dataset ready. Below images for illustration.

Step 10: Now Click on the “Insert” tab -> Matrix Wizard
Step 11: Once you click on the Matrix Wizard you will find the dataset that you had created few steps back selected. Click on that dataset, click on next
Step 12 : Drag and drop the fields as shown in the below illustration. Click Next
Step 13: Select the Corporate Style in the Styles Tab -> Click Finish
Step 14: When you click on finish you will find a template which will have the fields you had selected.Give a name to your report. Expand the Built-in Fields option and drag the “Page Number” property below the template. Save the report.
Now your report is ready. Click on the Run button and your report will open up in IE.
I hope this article was helpful in building a basic report from scratch. In my next post i will discuss how to build a report based on parameters.