Im on a roll with posts. This blog can be rebuilt ...
I received a question today from Camilo in Colombia asking
how to achieve the following.
‘What are my options to deliver excel files with multiple sheets? I know we can split 1 report in multiple
sheets in with the BIP Advanced Options, but what if I want to have 1 report /
sheet? Where each report in each sheet has a independent data model ….’
Well, its not going to be easy if you have to have
completely separate data models for each sheet. That would require generating
multiple Excel outputs and then merging them, somehow.
However, if you can live with a single data model with
multiple data sets i.e. queries that connect to separate data sources.
Something like this:
Then we can help. Each query is returning its own data set
but they will all be presented together in a single data set that BIP can then
render. Our data structure in the XML output would be:
Three distinct data sets within the same data output.
To get each to sit on a separate sheet within the Excel
output is pretty simple. It depends on how much native Excel functionality you
Using an RTF template you just create the layouts for each
data set on a page(s) separated by a page break (Ctrl-Enter.) At runtime, BIP
will place each output onto a separate sheet in the workbook. If you want to
name each sheet you can use the
<?spreadsheet-sheet-name: xpath-expression?> command. More info here. That’s as
sophisticated as it gets with the RTF templates. No calcs, no formulas, etc.
Just put the output on a sheet, bam!
Using an Excel template you can get more sophisticated with
This time thou, you create the layout for each data model on
separate sheets. In my example, sheet 1 holds the department data, sheet 2, the
employee data and so on. Some conditional formatting has snuck in there.
I have zipped up the sample files here.