Telerik Reporting R1 2017

How to Nest a Graph Item in a Table item

The Telerik Reporting data items are used to present data by two dimensions (the Table defines Row and Column groups, the Graph defines Category and Series groups). Often it is useful to create another dimension, which will result in an additional data "slice" and will give us the opportunity to display more measures.

This can be achieved with simply nesting data items. In this case the nested data item will get its data from the “master” data item, binding its data source to the master item's DataObject.

As a real life scenario, in the current example we will list the top 5 performing agents and the amount of their sales divided by product categories on a yearly basis, presented in a pie chart. We will use a Crosstab item as a master data item and it will define the dimensions “Sales Person Name” and “Year”. For a nested data item we will use a Graph item which will add the dimension “Product Category”.

We will use the AdventureWorks sample database and the Telerik Report Designer.

How to nest a Graph item in a Table item

  1. Open the Telerik Report Designer and create a new report.

  2. Cancel the Telerik Report Wizard, because it would not be needed for the current example.

  3. Start the CrossTab Wizard and create a new DataSource with the following select statement:

    SELECT
    C.FirstName + ' ' + COALESCE (C.MiddleName, '') + ' ' + C.LastName AS SalesPersonName
    , YEAR(SOH.OrderDate) as OrderYear
    , PC.Name as CategoryName
    FROM
    Sales.SalesPerson AS SP
    INNER JOIN Sales.SalesOrderHeader AS SOH ON SOH.SalesPersonID = SP.SalesPersonID
    INNER JOIN Sales.SalesOrderDetail AS SOD ON SOD.SalesOrderID = SOH.SalesOrderID
    INNER JOIN Production.Product AS P ON P.ProductID = SOD.ProductID
    INNER JOIN Production.ProductSubcategory AS PSC ON PSC.ProductSubcategoryID = P.ProductSubcategoryID
    INNER JOIN Production.ProductCategory AS PC ON PC.ProductCategoryID = PSC.ProductCategoryID
    INNER JOIN HumanResources.Employee AS E ON E.EmployeeID = SP.SalesPersonID
    INNER JOIN Person.Contact AS C ON C.ContactID = E.ContactID
  4. On the following page add the SalesPersonName field to the RowGroups box, OrderYear to the Column Groups box and the CategoryName field to the Detail Values box, where it will be automatically changed to Count(CategoryName). After the wizard ends, you can make some design adjustments to the generated crosstab to make it look better.

  5. Set the row group's Filters to "Count(Fields.CategoryName) Top N =5". Your group properties should now be set like this:

    di Nest Graph In Table Row Group Properties
  6. Set the crosstab headers to "Person Name" and "Total Sales" and the row group textbox expressions to "Fields.SalesPersonName" and "Count(CategoryName)":

    di Nest Graph In Table Cross Tab Layout

    If you preview the report now, you will see that the crosstab shows the top 5 sales agents along with their sales count by year.

    Now we have to set up the pie chart which will display their sales divided by product categories.

  7. Select the cell that displays the count and choose the Pie Chart item from the Insert menu. The Graph Wizard will appear. Since we will bind our graph to the current ReportItem.DataObject, actually there is no need to connect it to a data source, but on the other hand, the design-time support might come in handy.

  8. Select the data source and on the next page add the CategoryName field to the Series and Values box, where it will be automatically changed to Count(CategoryName). You can make some additional adjustments like removing the graph title, aligning the texts and resizing the crosstab's rows and columns. Finally your crosstab should look like the one below:

    di Nest Graph In Table Crosstab With Graph

    If you preview the report, you will notice that the chart data stays the same on every row and column of the crosstab. That's why we connected it to the whole dataset. In order to connect it to the current row group data, defined by the expression "Fields.SalesPersonName", we have to bind the graph data source to the current report item's data object.

  9. Open the Graph's Bindings editor and create a new binding for the DataSource property as shown below:

    di Nest Graph In Table Bindings Editor

    Since we are explicitly binding the DataSource property, we don't need to declare it in the Graph's properties, so you can set it to No Data Source. Please note that this will cancel the design-time support and the data will not be previewed in the graph designer.

    Your report is now ready. When you preview it, you will see that the pie chart is displayed on every cross-section of a sales person and an year, showing the percentage of the product categories sales.

Live examples

You can download the described report as a .trdx report definition from the following link: Nested Graph Item in a Table Item

The same approach is used in the Product Sales example report which is shipped with the Telerik Reporting installation. This report displays the monthly sales trend using a line chart for the product subcategories and a column chart for the total fields. You can see the report in action if you have a valid connection to the AdventureWorks sample database.

See Also