Introduction

In this article we are going to see how to create a Matric Report with the help of the Report Wizard and Report Designer. In our earlier articles, we have covered how to design and deploy the report to the server with the option to design using the wizard and also with the report designer. However, in this article we are going to see how to make a matrix type report, since most of the business reports are developed based on the pivot format to analyze. Now, let us see how to design the report and get the report in a much formatted matrix format.

Steps

Open Microsoft SQL Server Business Intelligence Development Studio and select the new Report Server Project Wizard as shown in the screen below.

As the Report Wizard opens, click Next to proceed further as shown in the screen below.

Next step is to select the data source. In this example we are going to use the Employee table as shown in the screen below. Click on Edit button to open the options to select the database and table.

Now select the database by providing valid credentials and then select the table as shown in the screen below.

Now click on OK to complete the connection properties and proceed to next step by clicking on Next button. This will open the query designer window. Using this window we can select the query based on the requirement. To design the query click on Query Builder button as shown in the screen below.

Now write the query to fetch the desired data. Once verified that the data is correct, click on OK to complete this task and click Next to proceed to the next window as shown in the screen below.

Now we need to select the report type. Since in this article we are going to concentrate on the Matrix type report, select Matrix and click on Next button to proceed further as shown in the screen below.

Now select the columns based on the requirement such as which columns should be applicable to be a column and in rows along with the details as shown in the screen below.

Now click on Next button to proceed further and select the design for the report from the list of predefined templates as shown in the screen below.

Now click on Finish button to complete the design. This will close the wizard and the report designer opens as shown in the screen below.

Now build and execute the report by clicking on the Run button or F5 functional key button from the keyboard. Once the building is completed, we can see the Matrix report as shown in the screen below.

Conclusion

So in this article we have seen how to make use of the Matrix template to design and use the report in a much analytical format for easy analysis.

Table Script: Create table EmployeeInfo ( EmpIDint, EmpFnameVarchar(100), EmpLnameVarchar(100), EmpStateVarchar(100), EmpSalary money )