One of Dodeca’s banner features is its incredibly advanced support for cascading reports. In case you’re not familiar, cascading reports are where multiple views or reports are built for all different permutations of given parameters. How this generally relates to the world of reporting is that we may want to choose multiple items across several different dimensions, then generate a report or view for each different combination.

For example, consider an Income Summary report where a user would want to choose a particular location, scenario, and product combination. In Dodeca, these would each be a selector associated with a report. As an administrator, when I am designing my report, I  associate these selectors with my report. When an end-user of the report goes to build it, they will be presented with choices from each dimension to simply choose from.

Of particular note is that the end-user doesn’t really need to know what a selector is, how it’s configured, where its data comes from, or anything. All they need to know is that they can click on whatever they want and nicely formatted data will be presented to them in a familiar, intuitive format. Incidentally, quite often the format of the report that comes back is their own design – some gnarly spreadsheet that used to take minutes or hours to painstakingly refresh, and now comes back reliably and quickly, every time, with the push of a button.

Let’s take a look at how this plays out for a user, using a simple Income Summary report. First, let’s load the view by clicking on it:

The report has not run yet, we must choose selections for each selector, then run it.

Now we have selections for each selector: notice that we want Colas, Cola, and Diet Cola from the product dimension, New York, Oregon, and Washington for the Location, and Actual for the scenario. The number of permutations of report is 9: 3 locations times 3 products times 1 scenario. Now that we have selections made, we can run the report.

Notice the different tabs at the bottom of the sheet. Let’s collapse the selectors to get a little more real estate for the view and the tabs:

As you can see, a custom tab name based on the value of the selector for that tab was generated. The first tab is Colas – New York – Actual. This is one of those times when something simple goes a long way for the user: instead of some terse or generic name, the exact description is used. The text of the tab is even configurable (of course), if we need something a little more custom than just the names of the items.

Before I delve into the administrator configuration of cascading, I want to take a brief moment to wax technical on some of the power and flexibility we’re seeing.

Dodeca reports aren’t limited to a single datasource, a single type of data source, or even a single version of data sources. Dodeca’s flexible and robust spreadsheet paradigm allows it to arbitrarily combine, use, and build data from any number of data sources, even on a single sheet. For example, we could build a view that pulls some data from a relational database, and then uses it to build several retrievals from multiple Essbase cubes, all on the same sheet. So consider this: we can give our users the ability to build such a report, with disparate and heterogenous data sources, and have the report be built dynamically for every given permutation of selections – selections which, by the way, themselves may be from multiple data sources. The selections in the above screenshots all happened to come from the dimensionality of an Essbase cube but they could have just as easily come from a dynamic SQL query.

Under the Hood with Cascade Configuration

Now that I’ve shown an example of a cascading report, let’s talk a little about how it’s setup under the hood by the Dodeca administrator or report builder. Of the numerous properties we can use to configure and fine-tune the behavior of a view, there is a dedicated section for the cascading options:

Generally we are okay with the defaults for the cascade options so we can leave them alone. The main thing we need to configure is to tell Dodeca which selectors will participate in the cascade. We can further configure options for Cascade Sources:

That’s it – that’s all there is to it. Keep in mind that in Dodeca, selectors are highly reusable components that are used across multiple views/reports. So we didn’t really have to do any special one-off setup for these selectors, just grab and go.

Stay Tuned

Stay tuned for more this week as I do a daily blog post about a particular feature of software in the Applied OLAP software suite. More to come!