Dodeca Technique: Essbase View with Cascaded Transaction Details Tabs

A support request came in the other week regarding some help on how to setup a particular report. The user wanted to create a view where the first tab would be a normal “bread and butter” Dodeca view that is based on Essbase retrieval ranges (and where the data shown is based on the values of different selectors. Additionally, when the report is built, for every item on the view (in this case, different products), create a separate tab within the workbook that has transactional details for that product.

So, just to visualize this a bit more concretely, check this out:

The built view!

The built view!

The first tab in this workbook is just a normal everyday Dodeca view with Essbase data. Note the series of additional tabs after the first tab, though: One for each product at the bottom of the Sample/Basic database. These tabs are all generated dynamically when the report is run.

Our Chief Software Architect (hi Amy!) wound up putting together an example that showed this technique off. After I took a look at it, I knew that I wanted to show this technique off (with a couple of twists), because it shows an absolutely amazing cross-section of functionality that highlight the power and flexibility of Dodeca. Even better, this report can be accomplished without any custom programming at all. This is all out of the box functionality that neatly ties together the ability to retrieve Essbase data, relational data, cascaded tabs, hidden selectors, Excel formatting, and more.

Continue Reading…

Dodeca Techniques – Auto Load Windows User Name During Relational Input

Today I want to look at a practical example in Dodeca that came up while I was at a Dodeca training workshop for a client the other week. We know that Dodeca can update data in a relational database using its robust SQL Passthrough DataSet functionality. And we know that it can automatically use values from the selectors to update columns as needed (for example, if the user has selected a Scenario of Actual, we could use a token such as [T.Scenario] in the SQL query).

Now let’s say that we have a table with data, such as headcount forecast data, and for whatever reason (auditing), we want to record the username of the user that is inputting the data. To achieve this, we can use a single Workbook Script method and a function to dynamically insert the Windows username of the current user into the view, and then use that token just as we would use any other token. In this case, we’ll use that token in the INSERT/UPDATE statements on the SQL Passthrough DataSet. Note that none of this configuration has anything to do with security, per se, it’s purely to have a column in the table to make it easy to see who updated/inserted a given piece of data.

Continue Reading…

Dodeca Techniques: Dynamic Rolling Quarters

It’s very common to want to perform reporting with a rolling time period. For example, given a particular month chosen by the user, we’d like to display the 4 or 12 or however many previous months to it. We might be given a particular quarter and want to show the previous 4 quarters.

Achieving such dynamic reporting can be tricky in some tools or lead to a less than ideal user experience, but this type of layout can be achieved easily in Dodeca, without having to write any code (although we will use some Excel formulas).

The technique itself is pretty simple, as I will describe in a moment. But I also want to comment on the general technique, since it applies to so many different Dodeca reports. The way that we can easily build this report type has to do with the nature of using a spreadsheet as the basis of the report in the first place.

In Dodeca, we have extreme control over a very methodical report build sequence. The simplest Essbase retrieve we could lay out would be a normal Essbase retrieval grid (members from each dimension, laid out properly). We can then elaborate on this and tokenize various cells in the retrieval grid so that the user selection is placed in for certain cells (such as the time period), and then the grid is retrieved. If we want, we can dynamically build the contents of the grid (such as running an MDX script to retrieve dimension properties). More specific to this case, however, is that we can use use Excel formulas in the cells that participate in the retrieve.

As a simple example, consider a report where the user chooses a year such as FY16. If we want the report to always show the year the user chose, plus the previous year, then we could just write a formula for the column next it, such as ="FY" & RIGHT(A1, 2) + 1. At runtime, Dodeca drops the value from the selector in, and in the case of FY16, our formula will evaluate to FY17, then Dodeca performs the retrieve. Using an elaboration on this technique, we can easily calculate the rolling quarters to show on a report.

Check out this report template:

A simple Dodeca template that calculates rolling quarters

A simple Dodeca template that calculates rolling quarters

In this report, I will have two selectors that will fill in the green cells. The user will choose a quarter and a year. The four columns to the left of the green cells are calculated dynamically. The upper area in this sheet is my “work area” that will actually just be hidden when the report is built. For a real world report I would probably have condensed the formulas down a bit, but it won’t affect performance.

Let’s take a look at all of the formulas (again, keep in mind this is just pure Excel, nothing specific to Dodeca), to see what’s going on before walking through it:

Simple Dodeca template with formula display turned on

Simple Dodeca template with formula display turned on

In sequence:

  1. The numeric quarter is derived from the user selected quarter. Over in cell G6, This is as simple as a =RIGHT(G11, 1). Q4 gets turned in to 4, Q1 turns in to 1, and so on.
  2. The quarter number is “mapped” to a month name using the CHOOSE function in Excel, then converted to a date using DATEVALUE. This is the formula in cell G4: =DATEVALUE(CHOOSE(G6, "Jan", "Apr", "Jul", "Oct") & " 15, " & G7). The CHOOSE function is really, really, useful and powerful in this context.
  3. We extract the numeric month in G5 using =MONTH(G4)
  4. Calculate the year, either using the =YEAR function or building it with ="20" & RIGHT(G12, 2), depending on which column
  5. Up in row 3, notice that we have a “month adjust” value. For the columns to the left of the initial date column, we use =EDATE to calculate the adjusted date based on the original date we calculated
  6. With the numeric month and year in hand, the formula for the cells in our actual Essbase retrieve grid are very simple now: for quarters, concatenate “Q” and the quarter number, and for years, concatenate “FY” and the two digit year.

Again, what happens with the grid is that the user makes a selection for the quarter and the year, we use Excel formulas to calculated previous quarter/year values (by way of converting to a date and using Excel date functions to do the “hard” work” for us), then build Essbase member names in the retrieve grid. That’s it. As I said, this general technique is applicable in many, many use cases, and not just for dates (although it’s particularly effective with dates).