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 usingctrl+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.

Create SSRS Report

Please go through below steps :

1. In Visual Studio File -> New Project to create new project.

2. In New Project Window select Business Intelligence Projects and then select either Report Server Project Wizard or Report Server Project.

3. It shows Report Wizard Dialog Box.

4. Click on, Next Button. Now we are going to Select the Data Source. We can also connect to other data sources like Oracle, XML etc .Here we select (use) Microsoft SQL Server.

5. Select Edit Button to configure details, related to our database like Server name, Log on to the server and Connect to database. After that we can verify it with ‘Test Connection‘ to check whether connection is done successfully or not.

6. Then click on Ok button. Now you can Design the Query using Query Builder or you can use simple query. We are going to use Simple query. It is given below.

You can find used query below:

SELECT FirstName + ‘ ‘ + MiddleName + ‘ ‘ + LastName as Name,AddressLine1 as     Address,City,Name AS State,PostalCode
FROM Person.Person
INNER JOIN Person.BusinessEntityAddress on Person.Person.BusinessEntityID = Person.BusinessEntityAddress.BusinessEntityID
INNER JOIN Person.Address on Person.BusinessEntityAddress.AddressID = Person.Address.AddressID
INNER JOIN Person.StateProvince ON Person.Address.StateProvinceID=Person.StateProvince.StateProvinceID
WHERE MiddleName is not null
AND City = ‘Seattle’

7. Select the Report Type Dialogue Box will open. Select Tabular Report type.

8. In next step (Design the Table) we can select Available fields and Display Fields in which area (like Page, Group, and Details), we want fields to show.
We are going to show all fields in details section.

9. In Next step Choose the Table Style to select Report Table Style. Here it is selected to State.

10. Clicking on next will give Completing the Wizard where we will giveReport Name.
Here you can see All details which we had given in earlier steps in Report summary.

11. Click on Finish button. First SSRS Report is completed.
You can see the report design screen as below. Also we can change Report Design or Data Source details here.We can Preview Report by click on button Preview.

12. You can also Export Report to PDF, Excel, HTML, Word, CSV, TIFF File.

Congratulations!Our First SSRS report is completed.

Create New Project in SSRS 2014

2015-07-14 by suresh

SQL Server Reporting Services (Shortly called as SSRS) is a server-based reporting platform used to create and manage tabular, matrix, graphical, charts and free-form reports. The reports that we create can be accessed and managed over a World Wide connection.

TIP: SSRS can extract data from relational and multidimensional data sources to generate report.

Creating New Project in SSRS 2014

In this example we will show you, How to create New SSRS Project in SQL Server Reporting Services 2014.
First, Double click on Business Intelligence Development Studio (shortly called as BIDS) and then select the File option from Menu item

Please select New and then select the New Project… option for creating new SSRS Project

Once you click on the New Project… option, It will open New Project window to select the required project from available project templates

Click Ok to finish creating New SQL Server Reporting Service project. Now if you look at the solution Explorer, you can understand the solution name and project name.

From the above screenshot you can observe that we have three folders under the SSRS project

• Shared Data Source: If you want to share the same data source to multiple reports, report models and subscriptions then we have to create shared data source. Please refer Shared Data Source in SSRS article to understand the Data Source configurations
• Shared Dataset: If you want to share the same dataset to multiple reports then we have to create shared Dataset. Please refer Shared Dataset in SSRS article to understand the Dataset configurations.
• Report: This folder is used to create SSRS Reports. If you want to create Reports using Wizard then, Please refer SSRS Table Report Using Wizard article otherwise Please refer SSRS Table Report article

Thank You for Visiting Our Blog