Store Reorganization using Dodeca

The idea behind this report is that a company is growing quickly and a new division must be created. This report was originally created by Michael McLaughlin. You should visit is excellent Dodeca Blog. I've taken his original report and made it completely dynamic and solely using MDX to return data. There are no sheet formulas or Essbase Retrieve Ranges. As with most things in Dodeca, there are several ways to accomplish the same task. This is merely an exercise to show the power of using MDX.

The outline is shown here with emphasis on the Location dimension. We'll use this dimension to allow the user to rearrange the divisions.

The first step is to set up the selectors. I created selectors for Departments, Location, Measures, Periods and Scenario.

Then I created the Selector Lists. Most of them are straightforward. For the Scenario dimension I allow users to select Actual, Budget or Forecast.

For the Locations list I use MDX to retrieve the list of stores. I also changed the TokenValueItemFormat to [{0}]. This will allow each value to be wrapped by square brackets for use in an MDX script.

The store list MDX is shown here.

I use an outline UDA to create the list of Measures that the users may include in their report.

The Measures list MDX is shown here.

I used MDX to pull back the list of Years as well. All MDX, all the time...

Next I configured my selectors in the Reorganization view properties.

I added my Selectors and assigned them their appropriate Selector List. I set the Location selector as optional. I also set it to allow multiple selections.

Then I created a very simple template. I included several tokens for a report heading and added a few formatted cells that I will use later to copy into the report.

I set up Excel named ranges for the starts cells I will use to place data, formatted cells and the columns being used in the report.

Since there are no Essbase retrieval ranges in this report, the majority of the work will be done by the Workbook Script.

 

I first add the AfterWookbookOpen event and OnAfterWorkbookOpen Procedure.

 

I then added a AddDataCache method. This method will run MDX to retrieve the list of divisions in my Essbase outline.

Next I added an AddProperty method to set a DivisionOffset property. This will act as a variable to keep track of where the latest division was added.

Now I created a ForEach method which will use the DivisionList DataCache from the earlier method. The BuildDivision procedure will be called for each Division in the list.

The BuildDivision Procedure starts out by running an MDX script.

This script is how most of the data on the report will be created.

 

Notice the WITH MEMBER statements. These create members on the fly. In this case the measures will be dynamic based upon what the user selects. Locations selected by the user will be removed from the report.

Tokens and the CurrentLocation property value will be automatically replaced by Dodeca at runtime.

The DivisionOffset property will be adjusted based upon the size of the data returned in the MDX query.

The RowCount property will also be adjusted based upon the size of the data returned in the MDX query.

Next I added six CopyRange methods. These handle the copy of formatted cells in our hidden rows to the proper place on the sheet. I do this using the Excel Offset function and the property values I've been keeping track of.

Once the BuildDivision procedure has been run for all of the divisions in our DataCache, we can then run the MDX Query for the new division.

This MDX script uses the WITH MEMBER statement along with Location tokens from the selector to pull in the locations for the new division and total.

Now that all the data has been retrieved on the sheet, I used the CopyRange method to format the data for the new division.

Finally, I set the Column Width on the sheet to AutoFit since there are no Essbase Retrieve Ranges to handle that for us.

By default, the report doesn't select any locations for the new division.

Once the report is run, the user can see the existing divisions with their changes as well the new division. This report can help a user quickly see the impact to changes in the organizational structure. This methodology could be implemented in a number of places to see the impact of changes before they happen.