Monday, March 2, 2009

Cascade Views in Dodeca

There is a question up on the Network54 board about the availability of a cascade utility for Essbase that will copy charts along with the Essbase cascade. To tell you the truth, I didn't remember that the classic add-in functionality didn't allow you to copy charts, so I fired up good old Excel 2003 to try it out. First, I had to work around the bug where Essbase doesn't work with multiple instances of Excel running. I can't believe users put up with that bug!. Once the Excel add-in was running successfully I quickly found that the classic add-in doesn't copy charts even when you check the checkbox to copy formatting.

The post also reminded me of a time back in the the mid-90's when I was at Lex Software. Arbor Software hired us to to write an obscure utility to automate the generation of cascades. If I remember correctly, the utility wasn't carried forward into 32-bit Windows. Due to a change in how printing worked, it basically would have caused a significant rewrite. Note that I wasn't the primary developer on that utility as I was busy working on Essbase consulting gigs for our friends at Arbor.

Of course, I was pretty sure we could create cascades with charts in Dodeca so I went to the Dodeca admin screens, exported the Excel template for our sample cascaded Income Statement view, added a chart, saved the file, re-imported the file and committed/saved it to my Dodeca server. As expected, it worked fine:

(click on the graphic to see a larger version)

When we generate a cascade view, we literally make a copy of the base worksheet including all of the formulas, formatting, range names and objects including charts, so despite the fact that I had not tried a cascade with a chart in Dodeca previously, I was confident it would work.

Cascade functionality is controlled by the administrator in Dodeca. Essentially, any Excel Essbase view may be cascaded with the administrator controlling which dimensions are to be cascaded. Further, the user typically gets to choose from members anywhere in the outline for the cascade and may, optionally, have a summary sheet generated that automatically generates subtotals for the selected members. Here is a screenshot of the Cascade category properties in the Dodeca View Metadata Editor, which is used by an administrator to turn on cascade functionality:

The other thing that always bugged me in the Excel add-in cascade functionality is the annoying numbering of the worksheet (or workbook) names. When I spec'ed the cascade functionality in Dodeca, I made sure that annoyance was fixed.

Oh, and about that other annoyance. The one with the multiple Excel instances being open and causing the Excel add-in to just not work... Dodeca doesn't have that problem either.

No comments: