DDR Excel Export: rendering chart report item as Excel chart object by using the Power of Templates

Preface
Excel Export in Data Dynamics Reports uses the amazing technology called “Templates” behind the scene. When a report is exported in Excel, we first build the intermediate XLS document that contains the placeholders for the actual data. This intermediate document is called “Template”.
Then we obtain the data that report normally shows and insert them in the template. The attentive reader will ask - So what? Why is this technology amazing? The answer is we expose the template to the real world: it is possible to obtain the template, modify it and pass to Excel Export. As the result the output may be tuned by creating the custom data placeholders: formulas, pivot tables and so on. In this article we will see how to solve on of the common problems with Excel export by using the Power of Templates.

The problem: chart output in Excel Export
If a report contains chart report item, then it is shown as the image in the exported XLS Document. It is not comfortable - Excel has the great charts support and it would be nice to see the DDR chart shown as Excel Chart in the output. Let’s look at hypothetical example. The application displays the attached DelayReport.rdlx(change xml to rdlx) by using the windows forms viewer and allows users to export in Excel(they just click “XLS” button and the resulting document is opened once exportation is done). The report consists of the table and the pie chart that show the data of employees delay statistic. By default the chart is transformed to the picture. The application users are not happy. They demand the Excel Pie Chart in the output. Let’s see what you can do step by step.

Templates power in action

• Open the attached DelayReport.rdlx in the report designer - either in standalone application or visual studio integrated editor.


• Select Report->Generate Excel Template menu item. Save the template in the appropriate location. Open the generated template in Excel.


• The cell contains “<#Chart1#>” text is the placeholder where the chart picture is going to be inserted, so remove this text, unmerge the cell and decrease its height if it’s needed.


• The cell contains “<#txtReason#>” text is the placeholder for “Reason” column of the table. The values from Reason column will be transformed to the range of cells. This range will start from “<#txtReason#>” cell position and grow further. Let’s name this range: select “<#txtReason#>” cell and define the new named range - in Excel 2007/2010 you can go to Formulas tab, click “Define name”, leave the default values in the popup dialog and click OK.


• The cell contains “<#txtCasesNo#>” text is the placeholder for “CasesNo” column of the table. Name the range it will produce by using the same way as in (4).


• Add the Pie chart on the appropriate place(Excel 2007/2010 - Insert tab, Pie chart). Right click on the chart, choose “Select Data...” and remove the default Legend Entry(Series) value.


• Click Add on Legend Entries(Series) editor. Set Series Name to “Delays”. This will be chart title which equals to one in the original report. Then set Series Values to “=Sheet1.txtCasesNo”. This way you map the chart data to the range created on step (5).


• Click Edit on Horizontal (Category) Axis Labels. Set labels range to “=Sheet1!txtReason”. This way you map the chart labels to the range created on step (4).


• Note that the chart data range is set automatically. Click OK. Save the template. The attached template.xls is the sample of the described modifications result.


• Test the exportation with the modified template. For example open the report by using DataDynamics.Reports.Preview.exe, choose Export->Excel in the toolbar. Set Template property in the popup dialog to the template path, select the output file name, click export. The attached output.xls shows the example of the output.


• Now in your hypothetical application adjust the code of “XLS” button click handler so that the customized template is used for exportation. Use TemplateStream property.

The tip of the iceberg
What is described here is just the small piece of the great functionality that is provided by Excel Export Templates. Basically you can tune the output with any available Excel feature.