Wednesday, October 8, 2014

My First Report in SSRS

In this article we will be creating our first SSRS report. I am using SSRS 2012 environment to walk you through the SSRS functionalities.

I will start from the very basics. Once we are comfortable with the tool we can skip these steps in coming articles.

Once you install SQL Server 2012 successfully, you get Microsoft SQL Server 2012 folder in your programs. You can find the snapshot below, Click on SQL Server Data Tools.



We will create a Solution and under that Solution we will create SQL Server Reporting Services Project. A Solution can have multiple different types of projects i.e. SSRS, SSIS and SSAS etc. Our Project will contain all the reports we create.

Please follow the below steps to create a solution and project.

 

Once you click on Project or you press Ctrl+Shift+N another window will open. In this new window you have to select your project type. As I am going to demonstrate SQL Server Reporting Services, so I will choose Reporting Services Template and then Report Server Project Business Intelligence. You have to give a proper Solution name with Project Name. You can find below the screen shot.



Once you successfully create your Solution and Project, by default it will be saved under C:\Users\YourUsername\Documents\Visual Studio 2010\Projects. In my case myusername is GG.

Solution file will have .sln file extension and project file will have .rptproj file extension.
Once you create a solution and project, you have to open Solution Explorer to create Report. If it is not open then you can open it either using ctrl+alt+L shortcut or go to View in the Menu bar and then click on Solution Explorer as shown below.


Once you follow any of the method, you will get Solution Explorer. As you can see from the below image our new SSRS project has three folders inside it.


Shared Data Sources –This folder contains list of all Shared Data Source Connections.
Shared Datasets – This folder contains list of all Shared Datasets.
Reports – This folder contains all SSRS reports.

In this article we will be creating a Report without Data source and Data sets. I will be creating another article in which we will first discuss about Data Sources, Data Sets and then create a report.


To add or create a new report into this project, please right click on Reports folder and then click on Add and then choose new item as shown below. You can also add a new item using Ctrl+Shift+A.


Once you follow any of the above method, you get a new window which gives you option to choose your new item. As we are creating a Report so we will choose Report from the available options. You can refer the below image. You have to give a proper Report name and click on add button present at the bottom.


Once you click on the add button your new report will be added to the project under Reports folder.
As you can see from the below image, a new report name as Report.rdl has been added into the SSRS Project (RHS). LHS we have Report Data and in the middle we have Report Body. Report body has two tabs Design and Preview. In the Design tab we design our report and preview is used to execute the report. 


To add an element in the report please right click on report body and then choose insert. Once your do mouse over on Insert, it gives you all available elements for reporting. You can refer below image for list of all available reporting elements.


Please Text Box from available reporting elements. Once you add Text Box it will be available in the report. You can drag & Drop, Increase and Decrease the Text Box size as per your need. This is the element which is used for labeling purpose. You can double click the Text Box and write whatever you want to present.


 To execute or preview the report please click on the Preview button.


You can export your report to any of the available option as shown below.

Congratulations you have successfully created and executed your first report in SSRS.