A report in Excel format is a very common requirement and BI Publisher can generate excel output using RTF, XSL or Excel Template. Excel template is recommended when the requirement is to create pixel perfect column width, to use built in excel functions, to create multi-sheet output, to handle preceding zeroes in data, to maintain data formatting, to manage high number of columns of data, etc.
How about adding native charts and pivot tables in the excel report ? Well, excel templates can handle that too.
There is no wizard in the Excel Template Builder to create charts or pivot table, but you can certainly include Excel Pivot Charts and Pivot Tables in your report using MS Excel features. Here is a step-by-step guide:
Step 1: Create Excel Template to build data for Pivot Chart & Pivot Table
Use Excel Template Builder to create Excel Template
Load a sample XML data. Add data column header names.
Use "Insert Field" option from BI Publisher Ribbon Menu and create data place holders as shown below.
You will see an interim dialog box from the Template Builder that a metadata sheet will be created. Click OK on it.
Add looping of data using Insert Repeating Group. Select the For Each entry at the repeating node level
Preview the output. This will bring all records in the excel sheet in a separate .xls output file.
Step 2: Create Pivot Chart & Pivot Table
You can close the output .xls file and stay in the Excel Template. Now select all the data columns to be used in the Pivot Chart and table. You can click on column headers and select the entire column to be included or you can just select the table with column headers and single row of data placeholders. From Excel Menu Insert, select Pivot Chart & Pivot Table option.
In the dialog box "Create PivotTable", you can keep selected the option "Select a table or range" and leave the Table/Range that appears by default based on the selection.
You can choose to create the Pivot Chart and Pivot Table in a new work sheet (recommended).Click OK.
This will add a new Sheet in the Excel file and insert a Pivot Table and Chart place holder, with Pivot Table fields on the right panel
Here you can select the fields for the Pivot table and chart, to be depicted as Axis, Legend and Values. In this example we have included Product Type, Product, LOB and Brand as Axis and Revenue as Values.
Please note that by default the function selected under Values is Count. Therefore, select the drop down next to Count function and choose Value Field Settings, where you can change this to Sum function.
One more thing to note is the presence of Field Buttons in the chart. You can hide these Field Buttons. With Pivot Chart selected, go to Analyze Menu in the Ribbon style Menu, and under Show/Hide section choose "Hide all Field Buttons".
Finally the template will look like this
Step 3: Include dynamic data generated by BI Publisher for Pivot Chart & Pivot Table
Right click on Pivot Chart, select PivotChart Options, select Data tab. Here select the option "Refresh data when opening the file". This will bring the data dynamically into the PIvot Chart and Pivot Table.
You can run preview of the excel output and you will see the pivot table and chart displaying dynamic data.
You will notice blank data appearing in the Pivot Table and Chart. This is due to the way the looping works against the dynamic data. You can hide this blank data by filtering the blank data from the parent field in the pivot table of the output excel file. In this example, we will remove the blank data from Product field and the complete blank section will be removed without affecting rest of the data. To do this, just hover over Product in the right side pane under Pivot Chart Fields and click on the down arrow. This will open the filter options for Product field. Uncheck the Blank value from filter list.
So, this completes the template design and the final output will look as shown below
You can further include excel functions and formula within these pivot table and charts as necessary for your requirement. You can even change the chart type, style etc. to create the most appropriate visual representation of the data. You can upload the excel template on BI Publisher server and run it against live data. You can include as many sheets with different pivot charts and tables, as required for your report.
Also note that excel template can be run against any data source type in BI Publisher Data Model. Therefore you can use BI Analysis or even run a BIJDBC SQL query against RPD layer, and bring complex calculations, aggregations as a part of your data.
Hope this was helpful. If you want to check the sample template and data, download it from here.
Have a great day !!