Tablix report format is similar to the table format. It represents the data within the form of a table. It’s usually a single data source table that you are representing it.

Matrix

It’s quite similar to the Tablix report. But the difference is that here we are working with more than a single value. Both the column and rows pertain to some specific data. It’s employed to group the data by using multiple fields within the row and column group. In matrix report, when the data is combined at the run time then the report grows horizontally and vertically. You can even format the rows and columns group on which you want to emphasize. For example, we want to know the sales volume of a product, and also we want to know the sales volume with respect to the regions then we go for the matrix report rather than Tablix report.

Charts

The Chart consists of different types of visual representation, such as bar chart representation, pie chart representation, line chart representation, and so on. All these graphical representations represent the charts. Depending upon the type of data, the graphical representation is chosen. For example, if we have a series of data then the bar chart is chosen as bar chart represents the graph horizontally, pie chart represents the proportion as a whole such as you want to represent the ratio between male and female members or you want to represent the ratio of different items sold whereas Line chart consists of a series of points connected to a single line and it evaluates the data over a continuous period of time such as sales volume over a constant period of time or to evaluate the number of employees over a fixed period of time.

Sub Reports

Sub Report is a report itself. It’s basically embedded in another report. It’s an independent report. Based on your requirements, it can be either related to the report that you are currently working on or can be worked as an independently. Suppose you are working on a major report, i.e., telling the overall sales volume and you also want to know the sales volume with respect to the regions then we use a sub report.

Drilldown Reports

Drilldown reports depend on how the data is processed. It shows the relationship between the top level and the sub levels. You can put data in a variety of ways to show the relationship between the top level and sub levels. You can arrange the data in a report, but you need to set it hidden until the user clicks on it to reveal details. You can display the data in a data region such as tables or charts, which is nested inside another data region such as tables or matrix.

Drillthrough Reports

Drillthrough reports also depend on how the data is processed. It’s a report that the user can view by opening the link within another report. Drillthrough report shows all the details of an item which is contained in another report. For example, a sales report contains the lists of all the sales orders, and when you click on any sales order, then you will see the report containing the details of a sales order.

Now we will start by creating three types of reports, i.e., Tablix, Matrix, and Charts. First, we will create the Tablix report.

How to create a Tablix report

Step 1: To create a Tablix report, we need first to launch the visual studio data tools. The below screen shows how the visual studio looks like:

Step 2: First, we will create a new project. Click on the file -> New->Project.

Step 3: When you click on the Project, the New Project window appears in which we select the Reporting services and then click on the Report Server Project.

Step 4: When you click on the OK button within the above screen, the screen appears, which is shown below:

Within the above screen, the rightmost screen represents the solution explorer, the leftmost screen represents the server explorer, and the bottom screen is the output screen.

Step 5: In this step, we will create the data source. Right-click on the shared data source and then click on Add New Data Source.

Step 6: On clicking on the Add New Data Source option, the screen appears, which is shown below:

Within the above screen, you need to enter the subsequent fields:

Name: Within the Name field, enter the name of the data source. I have given the name of the data source as DataSource1.
Type: Choose the type of database. I have selected the database like Microsoft SQL Server.
Connection string: You can modify the connection string. Click on the Build button to modify the connection string.

When you click on the Build button, the screen appears shown below:

Step 7: Till now, we have created the data source, but we do not want to work on all the tables of a database so, we will create the datasets. Right-click on the shared datasets, and then click on Add New Dataset.

Step 8: When you click on the Add New Dataset option, the screen appears, which is shown below:

Step 9: Click on the OK button.

Step 10: Now, we create a report. Right-click on the Reports folder, and then click on the Add New Report option.

Step 11: After clicking on the Add New Report option, the screen shown below appears:

Click on the Next button.

Step 12: Click on the Next button.

Step 13: Click on the Query Builder button.

Step 14: Click on the OK button.

Step 15: Right-click on the Reports then move to the Add option and click on the New item option.

Step 16: After clicking on the New item option, the screen appears, which is shown below:

Step 17: Click on the Report option shown within the above screenshot. When you click on the Report, the screen appears shown below:

Within the above screen, the middle screen represents Report workspace, where we create the design of the Reports.

Step 18: Now, we will choose the type of the visual that we want for our report. To create the Tablix report, Right-click on the workspace, select the insert option and then click on the Table.

Step 19: Within the Design tab, we create the design of the Report on how our report should look like. We have designed the report in which I have added the four fields of a Student table (student id, student name, marks, place).

Preview tab shows how the report looks like before it gets published.

Step 20: Click on the Preview tab. Within the Preview tab, we can view our report as shown within the below screenshot

How to create the Matrix report

Step 1: Create the data source equivalent as we created within the Tablix report.

Step 2: Create the dataset within the equivalent way we created in Tablix report.

Step 3: Right-click on the Reports and add a new item. When you click on the New item, the screen appears which is shown below:

Step 4: Right-click on the Report workspace, select the insert option and then click on the Matrix.

Step 5: Now, I add the two fields of a student table (student name and city) in a matrix report.

Step 6: Now, I want to group the students based on their countries, so I drag the country column from the Datasets and add it to the blue line shown within the below screenshot:

Step 7: After adding the country column to the Report, the design would appear as shown below:

Step 8: Click on the Preview tab.

The above screen shows that students are categorized with respect to their countries. So, we conclude that the matrix report is employed when classification is required.

How to create charts

Step 1: I created a new table, i.e., employee having three fields, i.e., employee_id, employee_name, and gender in SQL Server. Now, we need to create a new data source in a similar way we created in other reports.

Step 2: Create a new dataset.

Step 3: Add a new item in Reports.

Step 4: Right-click on the Report workspace, select the insert option and then click on the Charts.

Step 5: Choose the chart type.

I selected the Pie chart.

Step 6: When you select the pie chart, the screen appears, which is shown below:

Step 7: Add the gender column to the categories group and values within the chart data.

Step 8: Click on the dropdown icon of the gender and select the show data labels option.

Step 9: Click on the Preview tab to view the Report.

Within the above screen, the pie chart shows that 3 are males and 3 are females.

So, this brings us to the end of blog. This Tecklearn ‘Reports in SSRS’ blog helps you with commonly asked questions if you are looking out for a job in Microsoft BI. If you wish to learn Microsoft BI and build a career in Business Intelligence domain, then check out our interactive, Microsoft SSRS Course Training, that comes with 24*7 support to guide you throughout your learning period. Please find the link for course details:

https://www.tecklearn.com/course/microsoft-ssrs-course-content/

Microsoft SSRS Course Training

About the Course

SQL Server Reporting Services (SSRS) is a server-based report generating software system developed by Microsoft. It is used for building custom reports from a variety of data sources. In this course, you’ll understand the terminology behind SSRS and its configuration for creating reports and visualizations.

Why Should you take Microsoft SSRS Training?

The Average salary for a SSRS Report Developer is $90,842 per annum – ZipRecruiter.com
Microsoft BI is a Leader in 2018 Gartner Magic Quadrant for Business Intelligence & Analytics Platforms (9th Consecutive Year).

What you will Learn in this Course?

Introduction to Data Warehousing and MSBI

Fundamental concepts of Data Warehousing
Concepts of Dimensions, Measures, Metadata, Schemas
Data Marts and Design approaches
Normalization and Denormalization and Schema types
Online Analytical Processing and Transactional Processing concepts
OLAP Cube
Types of Slowly Changing Dimensions
Explain ETL process and various tasks involved in it
Discuss Business Intelligence concepts
How BI works with data-warehouse

Introduction to SSRS

Understanding SSRS Architecture
Learning the data flow in different components
SSRS Report Building tool components

Matrix and Tablix Overview

Understanding the concepts of Matrix and Tablix
Working with Text Box, learning about formatting, row and column grouping
Understanding sorting and formatting
Concepts of Header, Footer, Totals, Subtotals and Page Breaks
Hands On

Parameters and Expression

Learning about Parameters, filter and visibility expression

SSRS Reporting

Reporting Services Architecture & Lifecycle
Report Parameters and Multi-Valued
Cascaded Parameters
Types of Reports: Parameterized, Linked, Snapshot, Cached, Ad hoc, Clickthrough, Drillthrough, Subreports
Creating Groups, Filters, Expressions Sort and Interactive Sort, Alternate Row Colors, Repeating Row Headers and Column Headers
Describe Mobile report publisher
Exporting Reports to different formats
Hands On