In my last blog post, I outlined how to add interactivity to your Telerik Reports using Visual Studio. In this blog post I want to switch gears a little bit and outline how to accomplish the same results using Telerik Report Designer. Adding interactivity to the graphs in your report can give users additional perspective of the data being presented as well as provides for a more engaging user experience. First I’ll demonstrate the ability to automatically generate expansion and collapsing of a parent/child relationship through actions on the category axis. Next I’ll show you how to add an action to the series of a bar chart to achieve a report drill-through.
To begin, open Telerik Report Designer and create a new report and name it Sales Report. Cancel out of the Wizard and delete the header and footer sections of the report. Feel free to resize the Detail section of the report so that you have more room to work with.
The next thing we’ll need is a data source. This example uses the SQL Express 2012 version of the AdventureWorks sample database. Click on the Data tab in the designer and choose SQL Data Source then establish a connection to the database. When prompted for a SQL Query, use the following:
, PC.Name AS ProductCategory
, PS.Name AS ProductSubCategory
, P.Name AS ProductName
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
Now that we have our data source, we can begin creating our graph. Click on the Insert tab in Report Designer and select a column chart, then choose a clustered column chart. Select our SQL data source as the source for our data. Next choose Product Category and Product Subcategory as the Categories fields, and choose the LineTotal field for our Value, retaining the Sum function.
Sales report collapsed:
Sales report expanded:
It would be fine just to assume all that just happened here is magic, but what if you’d like to manually add actions to the category axis? The way the graph wizard accomplished this is by adding an action. Select your graph in the design view of the report, and in the properties window, click into the CategoryGroups collection. In the Action property, you can see that what the report is doing is toggling the visibility of the ProductSubCategory grouping.
To demonstrate adding actions to the series of a graph, we’ll create a drill-through report. Our top-most report will be the Product Sales report displayed as a column chart. When a bar in the chart is clicked (representing a product category), we will then drill through to a secondary report that displays the product subcategory sales that make up the category. Let’s first create the top-most report. Click on the File tab and select New. Create a new Telerik Report called CategorySales. As before, cancel out of the Wizard and delete the header and footer sections of the report. Next we’ll add our datasource, access the SalesReport that we just created, and copy its data source, then paste it in our new report. Next click on the Insert tab, and insert a clustered column chart just like we did before. When selecting fields for the graph, select ProductCategory as our category and LineTotal as our Value field, retaining the Sum function.
Now let’s move on to creating our details report. Click on the File menu item, and select New. Create a new Telerik Report named SubcategorySales. As before, exit out of the Report Wizard, and delete the header and footer sections of the report.
The first thing we will need to implement our details report, is a report parameter. This will allow the report to receive the category that was clicked in the top-level report and filter the data appropriately. In the Report Explorer, right-click on the Parameters item, and select add Parameter.
For parameter name, use CategoryId and it is of Integer type.
Next we’ll need some data. Copy the data source from the designer of your CategorySales report and paste it into the new report. Modify the query of the data source, by right clicking on it in the designer and selecting the Configure item.
Modify the query by appending the following:
Configure the data source parameter to use the CategoryId Report parameter, use a value of 1 for the design-time value.
Now that we have our data source set up, we can continue with adding the graph. This time, access the Insert tab and add a clustered bar chart. Select the data source, and use ProductSubCategory as the Category and LineTotal for the Value, preserving the Sum function.
Save the SubcategorySales report and return to the top-level CategorySales report. In the Report Explorer, click on the seriesGroup.
In the Properties pane, you will find an Action property in the Interactivity section. Drill into this property and select the “Navigate To Report” radio button. Click on the “Select a Report Source” button and select URL or file, and in the drop down list, choose “Select a File”.
Select the SubcategorySales report that we have already created. Next, click on the Edit Parameters button, and add the CategoryId parameter and set it to the ProductCategoryId field.
Accept all the values by clicking OK and return to the design surface of our CategorySales report. Save all the files and preview the report.
Top Level Report:
In this article we used Telerik Report Designer to create interactive reports through the use of actions. The ability to create interactive reports visually without having to resort to custom coding is a very impressive feature.
Carey Payette is a Developer Advocate. You can follow Carey on Twitter @careypayette or read her personal blog at www.codingbandit.com.