Post
Ask Question

Getting Started With SSRS 2016 - Part Four

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 refer to the links given below.

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 to create 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, prerequisites and SSDT, then you can read my previous article at the link given below.

How to create a table in a database

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

  1. CREATE TABLE [dbo].[Products](  
  2. [ProductID] [int] IDENTITY(1,1) NOT NULL,  
  3. [ProductName] [nvarchar](max) NULL,  
  4. [SupplierID] [int] NULL,  
  5. [CategoryID] [int] NULL,  
  6. [QuantityPerUnit] [nvarchar](max) NULL,  
  7. [UnitPrice] [decimal](18, 2) NULL,  
  8. [UnitsInStock] [smallint] NULL,  
  9. [UnitsOnOrder] [smallint] NULL,  
  10. [ReorderLevel] [smallint] NULL,  
  11. [SupplierName] [nvarchar](max) NULL,  
  12. [CategoryName] [nvarchar](max) NULL,  
  13. [Rating] [int] NOT NULL,  
  14. [Discontinued] [bit] NOT NULL,  
  15.  CONSTRAINT [PK_dbo.Products] PRIMARY KEY CLUSTERED  
  16. (  
  17. [ProductID] ASC  
  18. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]  
  19. ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]  
  20. GO  

How to create 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, type Project Name SSRSMatrixReport. Choose the project location path and click OK button.



Now, you can see EmployeeTableReport project structure, as shown in the screenshot.

 
How to create a new report

You can see in the details on how to add new report, using Visual Studio 2015 in Solution Explorer. You can see the step by step procedure, as shown below.

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



The Report Wizard Window will open and click Next button.



As soon as Data Source Window is selected, Data Source Window will open and click Edit button.



The Connection Properties Window will open and type the Server name, select the Authentication, username, password, select or enter a database name and finally, you can check the database connection succeed, followed by clicking the Test Connection button. Now, click OK button.

 

On selecting Data Source Window, it will open and select the new data source, connection string and click Next button.



The Design Query Window will open and click Query Builder.

 

The Query Designer Window will open and type the query into the table you want to load the report and execute  the SQL query, followed by clicking SQL icon. Now, click OK button. 

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

 



The Design Query Window will look as shown in the screenshot given below. Now, click Next button.

 

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

 

The Table design Window will open and add table columns, rows and details to display the fields, followed by clicking columns, rows, details button. Now, click Next button.

 

The completing wizard Window will open, type the Report name and click Finish button. 

 

After creating SSRS project report, you can see Solution Explorer structure, as shown below. 

 

Now, you can see the report design view, as shown below. 

 

Finally, click the preview button to see the report view.

 

How to deploy the report

Go to 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 below.

 

How to run the report

You can see in detail how to run the report, using Report Server in the Browser. You can see the step by step procedures, as shown below.

First, 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 click 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, and 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.


Similar Articles