Telerik Reporting R1 2017

How to: Add Drilldown action to the Graph item

In this article we will show you how to create a Column chart and add drill-down capabilities on its category groups using the Graph item and the AdventureWorks sample database. The chart will display the amount of sales by product categories per year, and the attached drill-down actions will give you a more detailed view over the sales, showing the subcategories for the selected product category.

You can use a report designer of your choice - the described approach is valid for both Standalone Telerik Report Designer and Integrated Visual Studio Report Designer.

How to: Add a drill-down action to the Graph Item

  1. Start the report designer and create a new report:

    • In Standalone Report Designer: File - New - Blank Report;

    • In Visual Studio Report Designer: File - New Project - Telerik Report Library [VERSION] - Blank Report.

  2. Add a new SqlDataSource component by: double-clicking on the SqlDataSource item in the VS Toolbox for VS Report Designer, or on the SqlDataSource in the Data tab of the Stabndalone Report Designer. This should start the SqlDataSource Wizard.

  3. On the “Choose Data Connection” page, you need to either select an existing connection to the AdventureWorks database, or create a new one by specifying the SqlClient Data Provider and a valid connection string to an AdventureWorks database.

    On the "Data Connection options" page, the newly created connection string can be saved by name in the current project's CONFIG file or it can be embedded with all details in the SqlDataSource.ConnectionString property

    On the "Configure Data Source Command" page, paste the following SQL query in the “Select Statement” window:

    SELECT
    PC.Name AS ProductCategory
    , PS.Name AS ProductSubCategory
    , P.Name AS ProductName
    , SOD.LineTotal
    , SOH.OrderDate
    FROM
    Production.Product AS P
    INNER JOIN Production.ProductSubcategory AS PS
    INNER JOIN Production.ProductCategory AS PC
    ON PS.ProductCategoryID = PC.ProductCategoryID
    ON P.ProductSubcategoryID = PS.ProductSubcategoryID
    INNER JOIN Sales.SalesOrderDetail AS SOD
    ON P.ProductID = SOD.ProductID
    INNER JOIN Sales.SalesOrderHeader AS SOH
    ON SOD.SalesOrderID = SOH.SalesOrderID

    Click Next, and then on Execute Query... button to make sure that everything is fine with your data connection and data is fetched from the database. Click Finish when you are ready. This will close the SqlDataSource Wizard.

  4. Select the report in the Report Designer and increase its Width to 11in. Open the report's PageSettings properties and set:

    • Landscape to True

    • Margins (Top, Right, Bottom, Left) to 0(zero) units.

    The purpose is to provide more space for content in the desined report.

  5. Now that the data source is configured, you should set up the Graph item:

    • If you are using Visual Studio Report Designer, from the VS Toolbox drag and drop the Graph Wizard icon to the Detail section of your report, and select Column -> Clustered Column from the first page.

    • In case you are using the Standalone Telerik Report Designer, select the report’s Detail section, and click on Charts - Column - Clustered Column from the Insert tab:

      How To Add Drill Down Action 3 Select Clustered Column

    This will bring up the Graph Wizard which will do the rest of the work for you.

  6. Select the data source you’ve created in step 3 and click Next.

    On the “Arrange graph fields” page, drag the ProductCategory and ProductSubCategory fields consecutively to the “Categories” box. The wizard will determine the master-detail relations between them and will create automatically the toggle visibility actions.

    To display the sales amount by year, expand the OrderDate field and drag its Year field to the “Series” box.

    At last, drag the LineTotal field to the Values box, where it will be displayed as Sum(LineTotal), as the Sum() is the default aggregate function.

    Now the “Arrange graph fields” page should look like the one shown below:

    How To Add Drill Down Action 4 Arrange Graph Fields
  7. Your chart is done. When you click Finish, you will see the graph displaying the product categories grouped by year. You can increase the Graph item in Width for better preview. When you click on Preview, you will see that every category has a small toggle mark that shows its subcategories when clicked, as shown below:

    How To Add Drill Down Action 5 Drill Down Graph
  8. To see what the Graph Wizard has done, you can examine the Graph’s CategoryGroups. You will notice that the Graph wizard created one CategoryGroup instance for the product categories and has added a child group to it for the product subcategories - CategoryGroups -> ChildGroups. The wizard has also created a ToggleVisibilityAction and set its Targets to the ProductSubCategoryGroup, providing the ability to show or hide this group by clicking its parent’s toggle mark. Initially the ProductSubCategoryGroup.Visible property is set to false to provide more compact view. The expanded view of the Graph’s CategoryGroups should look like this:

    How To Add Drill Down Action 6 Expanded Category Groups

In conclusion:

In this how-to article we demonstrated how to add a drill-down capabilities to the Graph item, having the Graph Wizard to create the groups hierarchy and the toggle visibility actions for us.

To learn how to add more actions and set them up manually, you can look at the following documentation articles: