One of the most popular questions asked in the Telerik Reporting forums is how to go about data binding subreports. Subreports are actually full reports, created just as you would create any other report. They are built so that they can be used independently as well as embedded as a subreport within an existing report using the SubReport control from the Telerik Reporting toolbox.  Much of the confusion on data binding subreports surrounds how to get data from the main report and use it as parameters feeding values into a subreport. In our example, we are going to create a main report that deals with reporting on the top 10 best-selling video game consoles of all time. For each of these consoles from the main report, we will include details via a subreport that displays the top 10 best-selling video games for that console. You can download a PDF of the final report.

masterDone
Figure 1 – the main report header provides a summary of the top 10 selling video game consoles

 

subreportEnd
Figure 2 – the main report details includes a SubReport control to display the top video games


Creating the Main Report

Please take the time now to download and attach the SQL Server 2012 Express database for this article. Once this is complete, open Visual Studio 2012 and create a new C# Class Library project. Once the project is created, delete the default Class1.cs file that is added by default. Right-click the project, and add a new item. Select Reporting and select to create a new Telerik Report class. Name this class Top10VideoGameConsoles.cs.

newItem
Figure 3 – creating a new Telerik Report

The report wizard is now displayed. We will use the report wizard to set up our main report. Click Next until you reach the data source screen. On the data source screen, click the button to add a new SQL Data Source to the database you’ve just downloaded and name it ConsoleData then use the following SQL query:

SELECT Consoles.*
FROM Consoles

Go ahead and preview the data and you should be presented with the following raw data:

previewData
Figure 4 – raw data from the ConsoleData SQL data source

Click Finish to complete setting up the data source. Once this has been done, select a Standard Report Type [click next], next select a left-aligned report layout [click next], be sure to use the Normal style sheet. Once the report is generated in the designer, delete the pageHeader and reportFooter sections. Your design surface should look similar to this:

generatedReport
Figure 5 – wizard generated report with pageHeader and reportFooter removed

Resize the main title textbox so that we can add more items to the report header. From the Telerik Reporting toolbox, drag an instance of the Graph Wizard into the report header section under the title. In the wizard, select the already existing ConsoleData data source, and select to create a Bar graph, ensure you check the checkbox so that we create a stacked bar graph.

graphType
Figure 6 – creating a stacked bar graph


Next drag the ConsoleName field to the Categories box. Drag the NorthAmerica, Europe, Japan and RestOfWorld fields to the Values box, remove the Sum function by clicking the ellipsis button and selecting “None” for each of these fields.

graphFields
Figure 7 – creating the console sales bar graph


Click Finish. The graph is now shown in the designer, let’s add a sort, to do this select the graph, then click the ellipsis in the CategoryGroups property. Change the sort order to sort on =Fields.Global ASC so that the graph items are arranged in order according to the total sales for the console rather than alphabetically. Resize and rearrange the graph as desired, leaving space for one more report element in the header.

Next drag an instance of the Cross Tab Wizard to the report header directly below your graph and reuse the existing ConsoleData data source. When setting up the fields for the crosstab, drag the ConsoleName field to the Row Group, and drag NorthAmerica, Europe, Japan and RestOfWorld fields to the Detail Values box, also removing the Sum function as you did previously with the graph wizard, then click Finish to add the crosstab to the designer.

crosstabFields
Figure 8 – setting up the main crosstab fields


To add a sort to the data displayed in the crosstab, right-click on the crosstab row (click on the=Fields.ConsoleName cell), select row group, then Group Properties, set the Sortings property to =Fields.Global DESC. Next resize and rearrange the crosstab in the report header as you see fit. You can now preview the initial report, it should look similar to the following.

firstReportPreview
Figure 9 – main report preview


You can see the main report has a repeated details section for each console. This is where we will add the sub report to display the top 10 best-selling games in the context of the console.

Creating the SubReport

We will be creating the sub report in much of the same way we created the main report. Right-Click the project and add a new Telerik Report to the project named Top10GameSalesForConsole.cs. Cancel out of the Report Wizard this time around and delete the report footer. Next select the report (click anywhere on the background of the designer where there are no report elements), and in the properties, click the Ellipsis in the ReportParameters property.  Add two properties, name the first one ConsoleId and it is of type Integer, name the next one ConsoleName make it of String type.

reportParameters
Figure 10 – subreport parameters


Next we will add the report data source by dragging an instance of the SQLDataSource to your report. Connect this data source to the database and set it up to use the following query:

SELECT GameId, ConsoleId, GameName, Year, Genre, Publisher, 
        NorthAmerica, Europe, Japan, RestOfWorld, Global
FROM Games
WHERE (ConsoleId = @ConsoleId)

When setting up the data source parameters, ensure to set @ConsoleId to be the value of the ConsoleId report parameter, and for design-time, you can set the value to 1.

datasourceParameter
Figure 11 – setting up the data source parameters


Once the data source setup is complete, rename the data source to GameData. Add a textbox to the report header section of the report to act as a title for the report, and set its value to “Top 10 Best-Selling Video Games For The {Parameters.ConsoleName.Value}”, the title will display the name of the console through with the ConsoleName report parameter that has been passed in. Now drag a graph wizard to the report header section below your title. Select GameData as the data source, and similar to the main report, select a Bar graph, and check the checkbox to use the stacked series. Drag the GameName field to the Categories box, then drag the NorthAmerica, Europe, Japan and RestOfWorld fields to the Values box, also removing the Sum function from these fields as you did in the main report. Add a sort, just as we did in the main report, by selecting the graph, and accessing the CategoryGroups collection. Set the Sorting on the category group to =Fields.Global ASC. In the color palette, choose a monochromatic scheme using a color of your choice.

Next drag an instance of the Crosstab Wizard to the report header, choosing GameData as your datasource, drag GameName to the row group box, and drag Year, Genre, Publisher and Global to the Detail Values box (removing the Sum function as before). For the crosstab, we will not be using any totals or subtotals, and will be utilizing the normal stylesheet. Add a sort, just as before by Right-Clicking on the Resize and rearrange the elements in the report header, and your designer should look something similar to this:

subreportdesigner
Figure 12 – sub report designer


Hooking it all together

Now we are ready to include our subreport into our main report. To do this, drag an instance of the SubReport control from the toolbox into the Details section of the main report. Increase the size of the control in the designer. Then in the properties of the SubReport control, access the ReportSource property. Select Object Instance, and in the drop down, select the Top10GamesSalesForConsole class from the project tree.

loadReport
Figure 13 – loading an instance of your subreport


Next click the Edit Parameters button, and set the parameter values to the Fields provided by the main report details.

subreportparams
Figure 14 – setting subreport parameters with data obtained from the main report


Now when you preview the report, you will see the sub report is rendered in the main report as the details of each video game console. This was accomplished through passing report parameters from the main report into the subreport.

Making the report pretty

Now that we have the data rendering, it’s time to make the report more presentable. In order  to achieve the look from the final product, I did some simple things that made a whole lot of difference.

  • select all text elements in both reports and change the font to SegoeUI (use shift + click to select multiple items), also add a Left Padding value of 2px (both of these properties are in the Style property grouping)
  • add titles to the graphs, the data scale is displayed by the millions of sales so I reflected this in my graph titles
  • edit the Legend property on each of the series displayed in the graphs to change the text value in the legend
  • set the graph elements fonts to SegoeUI
  • in the console name group header section of the main report, I set the BackgroundColor property to SteelBlue, and changed the textbox colors within the header to White
  • add a right-aligned label above each crosstab to indicate the sales are scaled by the millions
  • main title color, I used SteelBlue
  • sub title colors, as well as x-axis text values, I used a maroon color with the value 214, 97, 74
  • to help with printing, select the console name group header section, and set the page break value to Before

Summary

In this blog post we walked through creating two separate reports. We then used the SubReport control to embed one report within the other and showed how to pass values from the context of the main report into the sub report using parameters. As you’ve seen, Telerik Reporting is a powerful product yet it remains very straightforward to use.

Download final report PDF

Download Database

Download Source Code

Source for the data for this report can be found at http://www.vgchartz.com/analysis/platform_totals

Download Telerik Reporting


About the Author

Carey Payette

is a Developer Advocate. You can follow Carey on Twitter @careypayette or read her personal blog at www.codingbandit.com.

Comments

Comments are disabled in preview mode.