Telerik blogs

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.

ADDING ACTIONS TO THE CATEGORY AXIS

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. 

Clustered Column Chart

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:

SELECT  
	PC.ProductCategoryId 
	, 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

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 Graph Fields

 Once the graph is generated on the design surface, expand its size a little bit so that there is room for the graph to expand and collapse. Next click the Home tab, and click the Preview button. The Graph Wizard automatically detects the Parent/Child relationship of the ProductCategory and ProductSubcategory fields. It will also generate the necessary expanded views of the ProductCategory using ProductSubcategory data.

Sales report collapsed:

Sales Report Collapsed

Sales report expanded:

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.

Category Action

ADDING ACTIONS TO THE SERIES OF A GRAPH

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.

Category Sales Fields

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.

Adding a Report Parameter

For parameter name, use CategoryId and it is of Integer type.

Report Parameter Editor

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.

Configure SQL Data Source

Modify the query by appending the following:

WHERE PC.ProductCategoryId=@ProductCategoryId

Configure the data source parameter to use the CategoryId Report parameter, use a value of 1 for the design-time value.

Configure Data Source

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.

Subcategory Sales Fields

Save the SubcategorySales report and return to the top-level CategorySales report. In the Report Explorer, click on the seriesGroup.

Series Group

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”.

Loading a Report From 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. 

Setting Report Parameter Value

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:

Top Level Report

Details Report:

Details Report

CONCLUSION

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. 

Download Telerik Reporting


About the Author

Carey Payette

Carey Payette is a Senior Software Engineer with Trillium Innovations (a Solliance partner), an ASPInsider, a Progress Ninja, a Microsoft Certified Trainer and a Microsoft Azure MVP. Her primary focus is cloud integration and deployment for the web, mobile, big data, AI, machine learning and IoT spaces. Always eager to learn, she regularly tinkers with various sensors, microcontrollers, programming languages and frameworks. Carey is also a wife and mom to three fabulous boys.

Comments

Comments are disabled in preview mode.