SQL Server: Designing Tablix Report in SSRS 2008

In this article we will see how to design a Tablix (Table + Matrix) report in SQL Server Reporting Services (SSRS) 2008. For this demonstration, we will use the AdventureWorks sample database provided by Microsoft.

In SQL Server Reporting Services 2008, Microsoft has introduced a new reporting model called Tablix. Tablix is a combination of ‘Table + Matrix’ report. To design this report, open Visual studio 2008 Business Intelligence Studio and create a new Report Server project as shown below –

clip_image001

Once your project is ready, let’s create a data source which will fetch the data from the AdventureWorks database. To fetch the data, write the following query –

SELECT YEAR(SOH.OrderDate) OrderYear,PC.NAME AS CategoryName,PSC.NAME as SubCategoryName,SUM(UnitPrice) TotalPrice, SUM(OrderQty) TotalOrders
FROM Production.ProductCategory PC INNER JOIN Production.ProductSubcategory PSC ON PC.ProductCategoryID=PSC.ProductCategoryID
INNER JOIN Production.Product P ON PSC.ProductSubcategoryID=P.ProductSubcategoryID INNER JOIN Sales.SalesOrderDetail SOD ON P.ProductID=SOD.ProductID
INNER JOIN Sales.SalesOrderHeader SOH ON SOH.SalesOrderID=SOD.SalesOrderID
GROUP BY YEAR(SOH.OrderDate), PC.Name,PSC.Name
ORDER BY OrderYear,CategoryName,SubCategoryName


If you execute this query, you will get the following result –

clip_image002

To add a new data source to our report, go to Solution Explorer. Right click ‘Shared Data Sources’ folder and add a ‘New Data Source’. It will show you shared data source property window. Name the data source as ‘AdventureWorksDS’ and configure the connection to the AdventureWorks database by clicking the ‘Edit’ button as shown below –

clip_image003

Now let’s add a blank report using the solution explorer. To add a blank report, right click the ‘Report and click on add ‘New Item’ and make a choice of ‘Report’. Name the report as ‘AdventureWorksYearlySalesReport’ as shown below –

clip_image004

Now go to ‘View’ menu and click on ‘Report Data’. In the Report Data window, add a new dataset as shown below –

clip_image005

It will show you a Dataset Properties window. Name the dataset as ‘YearlySalesDS’ and choose the existing data source by clicking a ‘New’ button from ‘Data Source’ option. It will show you a Data Source Properties window. Make a choice of ‘Use Shared Data Source Reference’ radio button and choose our data source ‘AdventureWorksDS’ which we have created in the above steps. Then paste the query shown above in a ‘Query’ box -

clip_image006

Now we are ready to design the report. Drag and drop ‘Matrix’ control from tool box on our report as shown below –

clip_image007

Now if you check the properties window, you will see the name ‘Tablix1’. Align the report to the left side of the report designer.

Now from the ‘Report Data’ Window drag and drop ‘OrderYear’ on columns group section and drag and drop ‘CategoryName’ on Rows group section. Then drag and drop ‘TotalPrice’ on Data section. Now your report will look like this –

clip_image008

Now click on the preview report tab and see your report. It will look like below –

clip_image009

Now let’s modify the report little bit. Let’s first format the report. Make all columns bold. Now let’s add a ‘Total’ column before Columns group and add ‘Total’ column after Rows group.

To add a total column, right click to ‘[OrderYear]’ column header and click on ‘Add Total’ ‘Before’. Repeat the same step by right clicking the Category name column and ‘Add total’ ‘After’. Now preview your report and it should look like below –

clip_image011

Now let’s add a ‘SubCategory’ section in the Rows group. Drag and drop ‘SubCategoryName’ field below the ‘CategoryName’ field as shown below –

clip_image012

Now preview your report. It will look like this –

clip_image014

If you observe this report, it is similar to reporting services 2005. Let’s modify this report to display it in ‘Tablix’ format. Go to Subcategory column and ‘Add Total’ as shown below –

clip_image015

Now click on the total column and choose a field ‘Category Name’ and delete ‘Category Name’ column as shown below –

clip_image016

Preview your report and it will look similar to the following –

clip_image018

Summary – In this article we have seen how to design a basic ‘Tablix’ report using a ‘Tablix’ control in SQL Server Reporting Services (2008).

Download the source code


3 comments:

Pratik said...

How to get the fields as "0" where all the fields are null i.e blank?

Pratik said...

and i would also like to mention how to make the fields in column group as "0" where no data is entered?

Unknown said...

We are using MS Visual Studio ver 9.0.30729.4462 QFE .Net Framework ver 3.5 SPI.

My Visual Studio has two separate controls, Table and Matrix. There is no Tablex control. I recently saw a tutorial that explained how the two controls were combined into a new control called Tablex.

We are having problems with our SSRS, SQL Server Reporting Services within Visual Studio. The reports will work, we then update the query such as add a new parameter and then suddenly the report no longer returns rows(records), just table headers. It is unfixable and we must delete and restart the report.

What is interesting is that when you select a control that I know is a Table, the properties window labels it as a Tablex control.

I am theorizing that the differences between the Tablex control and the Table and Matrix control are causing a conflict. What are your thoughts?