Getting Started with SSRS 2016 - Part 4

by Santhakumar Munuswamy

  Posted on  27 February 2017

  SSRS


 

In this article, we will discuss how we can create a Matrix Report using Report Wizard in SQL Server Reporting Services 2016. we have already discussed how to create a Table Report without using Report wizard, how to deploy the SSRS Report into Reporting Server, and how to run the Report Application in my previous articles. If you want to learn SSRS Series, you can read the below link:

In this article, we will see the following

  • How to setup the SSRS Environment
  • How to create a Table in database
  • How to create an SSRS Project
  • How to create a new Report
  • How to deploy the Report
  • How to run the Report

Prerequisite

We should have some basic knowledge in SQL Server Query and Visual Studio Tool for creating an SSRS Reports using Visual Studio 2015.

  • SQL Server Basic Query
  • Visual Studio 2015

How to setup the SSRS Environment

If you want to know how to setup the SSRS Environment procedure, prerequisite and SSDT, please you can read out my previous article as below link

How to create a Table in database

Firstly, create a Product table using SQL Server Management Studio in SQL Server database. Then, you can add product details in the Product table.

CREATE TABLE [dbo].[Products](

[ProductID] [int] IDENTITY(1,1) NOT NULL,

[ProductName] [nvarchar](max) NULL,

[SupplierID] [int] NULL,

[CategoryID] [int] NULL,

[QuantityPerUnit] [nvarchar](max) NULL,

[UnitPrice] [decimal](18, 2) NULL,

[UnitsInStock] [smallint] NULL,

[UnitsOnOrder] [smallint] NULL,

[ReorderLevel] [smallint] NULL,

[SupplierName] [nvarchar](max) NULL,

[CategoryName] [nvarchar](max) NULL,

[Rating] [int] NOT NULL,

[Discontinued] [bit] NOT NULL,

 CONSTRAINT [PK_dbo.Products] PRIMARY KEY CLUSTERED 

(

[ProductID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

 

GO

How to create an SSRS Project

Open Visual Studio 2015. Go to File menu, point to new and click new project. New Project Window will open, you can select an installed template like “Reporting Services” in Business Intelligence Template and then select the Report Server project and type Project Name SSRSMatrixReport. Choose the project location path and click OK button


Now, you can see EmployeeTableReport project structure, as shown in the screen-shot as below


How to create a new Report

You can see in details how to add new Report using Visual Studio 2015 in the Solution Explorer. You can see the step by step procedures as below

Go to the Reports folder, right-click the Reports folder and point to Add New Reports,  then clicking the Add New Report. 


The Report Wizard window will open and click the Next button


The Select the Data Source window will open and click the Edit button


The Connection Properties window will open and type the Server Name, Select the Authentication, User Name, Password, Select or Enter a database name and finally you can check the database connection succeed, followed by clicking the Test Connection button. Then Click the OK button.


The Select the Data Source window will open and select the new data source, connection string and click the Next button


The Design Query window will open and clicking the Query Builder


The Query Designer window will open and type the query in which table you want to load the report and executing the SQL query, followed by clicking the SQL icon. Then, Click OK button

select ProductID,ProductName, CategoryName, SupplierName, QuantityPerUnit, UnitPrice, Rating from Products


The Design Query window will look with SQL query as shown in the following screenshot. Then, Click the Next button


The Report Type window will open and select the report as Matrix and click the Next button.


The Table design window will open and adding table columns, rows and details to display fields, followed by clicking columns, rows, details button. Then click the Next button.


The Completing Wizard window will open and type the Report Name and click the Finish button.


After creating SSRS project report, you can see the solution explorer structure, as shown in the following screenshot


Now, you can see the report design view as following


Finally, click the preview button to see the report view



How to deploy the Report

Go to the Solution Explorer, Right-click the EmployeeTableReport and click the deploy context-menu item.


After successfully deployed the report to Report Server, you will see the information from the output window as shown in below screenshot


How to run the Report

You can see in details how to run the Report using Report Server in the Browser. You can see the step by step procedures as below

Firstly, you have to copy the default Report Server Url and paste the same Url into your browser for the run report application.

http://localhost/reportserver


Now, you can run the report application, followed by SSRSMatrixReport and clicking the ProductReport


Conclusion

I hope you understood how we can create a Matrix Report using Report Wizard in SQL Server Reporting Services 2016, how to create a new report, how to deploy/run the report application in the Report Server. I have covered all the required things. If you find anything which I missed in this article, please let me know. Please share your valuable feedback or comments and suggestions to improve future articles.

 


About the Author
Santhakumar Munuswamy is a seasoned Solution Architect and Most Valuable Professional in Cloud /AI solutions. He has been experienced around 14 years in Solution designing, Software Product Development, Technical Documentation, Project Management for Web and Cloud Applications. He has experience in the IT industry across different domains (Automotive, Telecommunications, Healthcare, Logistics & Warehouse Automation, etc.) with roles in Technical Architect, Genesys Consultant, Technical Lead, Team Lead, and Developer. He has experienced in mentorship and coaching high potential developers.

Follow Me: Facebook, Twitter, Google Plus, LinkedIn
blog comments powered by Disqus


Month List

Community Badges