Solver Global’s flagship reporting tool, BI360 Report Designer has several strong features and enables many businesses to create financial and operational reports. Unfortunately, one of the limitations of the report designer (v4.6 at this time) is that creating custom drill-down views is extremely repetitive.  There isn’t a built-in copy function for the drill-to definitions, which would be especially handy for reports where the row filters deviate just slightly (such as a P&L statement).

Earlier today I needed to create a ton of nearly identical drill-to definitions in BI360 report designer (~40) for a single report and cut a corner using the XML definitions inside the Excel file.  This isn’t as user-friendly as the Excel report designer interface, but it saved me about 3-7 minutes per drill-to definition. 

Before we start breaking finishing the Excel report, we do need to setup at least 1 of the drill-to definitions. This includes the columns and the complete filtering scheme. (this is our drill-to template)

I still need to go into every cell I am creating a drill-to definition for and add a mostly-empty drill-to definition.  Input the name for the drill-to definition and add the unique filter that is messing up your ability to use the same drill-to definition over and over.  Feel free to curse at it.

Now we’re ready open the XML! (maybe backup your file now)

Excel is a Fancy XML Parser

Sometimes I forget all of the interesting things you can do by editing the raw XML within an Excel file – and getting at the XML code is a quick 2-step process.

Change the .xlsx file to a .zip File

Unpack/Extract the Archive

Open customXml/item2.xml in a Text Editor

Our template drill-to definition starts on line 30

If you scroll through the XML after [cci_xml][/cci_xml] you likely will see some empty column definitions [cci_xml][/cci_xml].  With find and replace we’re going to remove the empty column definition and the opening [cci_xml][/cci_xml] tag and replace it with the columns and the other filters from our template drill-to definition.

We’re going to leave the unique filter (in this example, d_Account.Code) alone in every drill-to definition.

[ccin_xml]

[/ccin_xml]

 

Put the Excel File Back Together

Close out of the XML file and select all the files in report directory.

Rezip all the items and change the file extension back to .xlsx. You can go back to editing the report in BI360 report designer as if nothing ever happened, but all of your drill-to definitions are done!