April 17, 2011

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


Did you like this post?
kick it on DotNetKicks.com
subscribe via rss subscribe via e-mail
print this post follow me on twitter



 
  Feedback:

comments

2 Responses to "SQL Server: Designing Tablix Report in SSRS 2008"
  1. Pratik said...
    March 2, 2012 at 9:42 PM

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

  2. Pratik said...
    March 2, 2012 at 9:44 PM

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

 

Copyright © 2009-2014 All Rights Reserved for SQLServerCurry.com by Suprotim Agarwal | Terms and Conditions