Jazz Up Those Static Dodeca Views With Advanced Essbase Features

Oftentimes when I am demonstrating or teaching aspects of Dodeca to people, they are amazed at the sheer number of options and configurations that are available on a view. Fortunately, I am able to tell them that yes, there are many, many options – and they are there if you need or want them, but they won’t get in your way. The defaults are very sensible and getting a basic Essbase-based Dodeca view running is incredibly easy.

Another thing to keep in mind is that for the most part, the extreme amount of options and flexibility we have on a single view is often available to us in lieu of code. So, tasks that typically required some non-trivial amount of VBA code are now completely code free. When we need some advanced functionality that isn’t available out of the box, we can use Workbook Scripts, which is an event-driven scripting technology that is particularly well suited to working with spreadsheets and the data contained in them.

That all said, today I want to walk through a bit of a cross-functional example that starts with a very typical Dodeca view based on an Essbase retrieve range, then enhance it to give our users the ability to zoom in on the different time periods in the view without having to rebuild the view. So we’re going to blur the line a bit between static and dynamic reports, and our users are going to enjoy some additional flexibility and convenience with regard to their reporting (and keeping users happy is always a good thing, right?).

Continue Reading…

Dodeca Technique: Multiple Essbase Data Sources in View

When I’m talking about Dodeca features, one that very often comes up is that Dodeca views have great support for multiple data sources. I’ve seen customers and clients use this to give them a cutting edge in terms of developing reports that tie together information from disparate data sources in a flexible way that was previously very cumbersome or impossible with the tools at hand. Among other instances, this feature comes into play when it would be beneficial for a user to view data that happens to reside in multiple databases, but for the sake of the user experience, we don’t want them to have to run multiple reports.

So today I want to look at a very simple Dodeca view that taps into multiple sources. There are a couple of nuances to consider for this development scenario. Consider that a typical view with a single data source will just have its connection specified explicitly as a property on the view, and the selectors on the view (if any) will assume that they are to be populated based on that connection as well. For example, let’s say we have a view based on the Sample/Basic database, and we have two selectors that are dynamically generated: Time and Product. When Dodeca goes to generate the list of Products to display to the user to make their selection(s), it knows to use the Sample/Basic database. However, if we want to have multiple selectors and have their contents be based on a particular cube’s outline, then we need to simply associate the proper connection with the selector.

For today’s example, I’m going to build a simple view that has one tab based on Sample/Basic and another tab based on Demo/Basic (as a brief aside, Demo/Basic is Sample/Basic’s less popular, less-talked about sibling that is eagerly awaiting its day in the spotlight). Note that while this example will have multiple Essbase connections and multiple selectors (one on each database), this isn’t necessarily how a view will always need to be configured. If you have a selector whose contents aren’t dependent on a particular database, then you wouldn’t need to worry about the connection specification for that selector.

Continue Reading…

Dynamic Calendar with Comments in Dodeca

I keep telling myself that I’m going to do more blog posts that are short and sweet, instead of these epic 6,000 word monsters, but I’m just having too much fun. Today’s article is going to be a little bit of thinking outside the box. Outside the box – but inside the grid. This is actually inspired by a use-case I saw a Dodeca customer present on at Kscope this year.

The basic original idea was “Why not make a calendar view in Dodeca?” Those of us that are heavy in the Essbase/Excel world are used to modeling financial data, but spreadsheets are used for countless different activities. Create a workout plan. Create a list of your favorite movies – and even make a calendar.

In the context of Dodeca, a calendar, whether it be static or dynamic, is a really cool use of the tool, if a bit unorthodox. A lot of financial departments and companies have very complex but methodical financial processes, particularly around the “close period”, and keeping everyone on track and coordinated is important. And companies that have Dodeca already have a very quick and very easy way to make dynamic spreadsheets centrally available to their users without having to email around a bunch of Excel files.

For today’s post I am going to start off with a basic calendar, then absolutely turbocharge it. The user is going to be able to select a month and year from Dodeca selectors and the calendar will dynamically update. We’re going to make it so we can add comments to each cell of the calendar. The comments will be associated with arbitrary intersections of our choosing (a great feature of Dodeca comments that I’ll go into extensively in this post). We’re going to accomplish this using the built-in Dodeca comments functionality. Along the way, I’m going to show off some of the power and versatility of Dodeca comments and use practically every option available.

Continue Reading…

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).