Dodeca exposes a tremendous amount of Excel functionality to the relational view type. One of the most common things that most customers leverage is the ability to use Excel formulas in the subtotal blocks. One of the challenges most developers have when adding formulas, such as the SUM() function 'on-the-fly', is determining the range address for the formula. We made it very easy to use formulas as we insert rows to contain the actual data. This approach leverages the built-in spreadsheet functionality which adjusts the range addresses automatically.
Another useful Excel functionality that is available in Dodeca is grouping. Grouping provides users a way to tie a range of cells together so they can be easily collapsed or expanded. It supports the ability to automatically sort and group data based on column values and expressions. Additionally, up to 8 levels of grouping is supported and each level can be configured to initially display expanded or collapsed.
The requirements for the Excel template are simple. First, there must be a range defined for the data. The range must be exactly the number of columns returned by the SelectSQL of the SQLPassthroughDataSet object. The range must also be exactly 2 rows tall or, if you intend to show the column headers returned by the relational query, exactly 3 rows tall. This range can optionally contain formulas which will automatically be both preserved during the retrieve process and copied to each data row inserted into the worksheet.
Optionally, you can also include subtotal blocks. The subtotal block typically includes the entire data range and may include one or more rows, and or one or more columns, before and/or after the data range. This subtotal block typically contains formulas and labels to summarize the detail rows contained within the data range.
The range names should be defined with sheet level scope. Sheet level names are mandatory when the relational view is cascaded. Here is a screen shot showing the data range name definition.
 |
| DataRange definition in the Excel template |
For my example, I am going to insert subtotal blocks to summarize the operation and break on the server / application / database. The operation block will contain a row for the average, minimum and maximum values for each operation. Here is my completed spreadsheet template.
 |
| Completed spreadsheet with OperationBlock range selected |
In the final post of this series, I will setup the SQLExcel view that will join together the SQLPassthroughDataset from part 1 and the Excel template from part 2 to display and summarize Essbase performance data.
