In the last webinar, Telerik Reporting introduced interactive reports. In the example provided by the webinar, we demonstrated the ability for the Graph Wizard to automatically detect parent/child relationships and by clicking the category axis label, the graph automatically expanded into the child components of the selected category. This interactivity was provided by the Graph Wizard by applying an action to toggle the visibility of the child breakdown graphs. In Q2 2013 SP1, Telerik Reporting went one step further to provide you with the ability to add actions directly on the series of your graph. For example, you can now add action that will allow your users to load a separate report when they click on a bar in your bar chart. Let’s now review step by step these interactive features available in Telerik Reporting.
Let’s quickly review the sample from the Webinar using the AdventureWorks database. Open Visual Studio and create a new Class library project, I’ve named mine InteractiveReports. Delete the default Class1.cs file that was generated upon creation of the project. Create a new Telerik Report and name “SalesReport.cs”. Cancel out of the Wizard, then select and delete the header and footer sections of the Report. You can also expand the details section of your report so that you have more room to work with. Next we will add our SQLDataSource by dragging and dropping an instance onto our design surface. In the Wizard, establish a connection to the AdventureWorks database, then use the following query for your data:
, 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 are ready to create our graph. Drag and drop an instance of Graph Wizard to the details section of your report. We will be creating a Clustered Column chart for this example.
In the data source screen, select our newly created SqlDataSource, and click next. Now it is time to define the fields of our graph. For the Categories, drag and drop ProductCategory then ProductSubCategory. The Wizard will automatically detect the parent/child relationship between these two fields and will generate the necessary graphs and actions. For values, drag and drop the LineTotal field to the Values box, retaining the Sum function on the field.
Click Finish in order for the graph to be generated. You can now arrange the location of the graph on your report. I’ve also changed the graph title to “Sales Report” and changed the Legend value for the series to Total Sales. Save the report, then click on the Preview tab to see the report. You will now see the completed report, you will notice that the category axis labels have an expander icon next to them, by clicking this icon, the category will expand into a breakdown that displays the sales of its subcategories.
Return to the Designer tab of your report, and select the Graph. In the properties window, navigate to and expand the Categories collection. You will see that the way this behavior is accomplished was through an action that toggles visibility of part of the report. You don’t need to rely on the Graph Wizard to implement this functionality, you can also add Actions manually. For instance, if you decided you want to navigate to a separate report instead.
Let’s now dive into adding actions to the series of our graph. We’ll implement a drill-through where we will have a parent report and by clicking on the series we will navigate to a breakdown report. Create a new Telerik Report and name it “CategoryReport.cs”, this will be our top-level report. As before, cancel out of the Wizard and delete the header and footer sections of the report. Copy and paste the SqlDataSource from the SalesReport that we created earlier. Next, drag and drop an instance of the Graph Wizard to the details section of the report. Select a clustered column chart type. Using our SqlDataSource, we’ll be using the ProductCategory field as our Category, and the LineTotal field as our value (preserving the Sum function). The Category Sales report will then be generated on the design surface. Title the report “Category Sales Report” and change the Legend Value of the series to Category Sales.
Next we’ll define a subcategory report, this will be our detail report that will be based on the Category clicked in the above report. Add a new Telerik Report to your project, and name it “SubCategoryReport.cs”. As before, cancel out of the Wizard and delete the header and footer sections. Copy and paste the SqlDataSource from the CategoryReport. Select the report itself (by clicking on the yellow area), and in the Properties pane, expand the ReportParameters property. Add a new property and name it “CategoryId” of type Integer.
Next, right-click on the SqlDataSource and select Configure. At the end of the Select statement, append the following line:
In the Data Source Parameters screen, assign the value of the parameter to the report parameter that we just created.
For a design-time value, set it to 1, which is the Bikes category. Next drag and drop an instance of the Graph Wizard to the details section of the report. This time we’ll choose a Clustered Bar chart, select our SqlDataSource and select the ProductSubCategory field as our Categories field, and LineTotal as our values field (preserving the Sum function). Rename the graph to “Subcategory Sales Report” and change the Legend value for the series to “Total Sales”. Save the report, and build the project.
Let’s now add the action that will allow us to drill through to the Subcategory Sales report when we click on a bar from the Category Sales Report. Open the CategoryReport, and while on the Designer tab, click on the series of the graph. With the series selected, in the properties pane, expand the SeriesGroup properties. Next, click the ellipsis next to the Action property and select the “Navigate To Report” radio button. On the next screen, you will see a bunch of options to load a report, each approach is equally functional. In this example, we will select Type Name, and choose the SubCategoryReport.
Next, click the Edit Parameters button, click New, select CategoryId (our Report Parameter that we defined on our SubcategoryReport). Set the value of this parameter to the ProductCategoryId and click OK.
Click OK again to complete adding the NavigateToReport action on our series. Save the Report, and click the Preview Tab. You can now drill through to the Subcategory Sales Report when you click on a bar from the Category Sales Report.
Top-Level Category Sales Report:
Subcategory Sales Report based on the Category selected in the top-level report:
In this blog post we covered how to go about adding actions to a graph category axis and series in Telerik Reporting. By using actions, your reports can become fully interactive and engaging to your users.
Carey Payette is a Developer Advocate. You can follow Carey on Twitter @careypayette or read her personal blog at www.codingbandit.com.