Report Scripts the unloved part of Essbase

When I took my first Essbase class long ago in the dark ages (V3.11), the instructor talked about report scripts. I can repeat all he told me. He said “There is something called a report script you can use, but we don’t go over it in the class”. As I went through projects, I heard the same line over and over “You can use report scripts, but they are slow and unless you really need to extract data from another system, no one uses them”. Interesting, no one used them, but Both Analyzer and Reports used reports as a basis for their queries and performance was not real bad. So I started investigating how and when to use them. I have to say, I didn’t know what I was missing. There are some pretty good uses for report scripts. This article will so you how to use report scripts in Excel to simplify some tasks, some commands that help format reports and others to avoid.

Although I have no proof or evidence to back my claim, it is my belief that report scripts were the first way to extract data from Essbase into Excel. Why else would that functionality be build in? You might ask, why would you want to use a report script in Excel when the interface works so well to retrieve Essbase data? There are lots of reasons, automation, ease of change, speed of retrieval; yes I said speed of retrieval. In some cases, you can get much quicker retrieval using as report script than by drilling in. So let’s get started.

If you want to use report scripts in Essbase, you first have to make a few changes to settings. Open Excel and log on to Sample.Basic. You don’t want to retrieve data. Go to the Essbase menu, select options and select the Mode tab. Change the retrieval type from Advanced Interpretation to Free Form. If you do not plan to do any other zooms on the sheet, you can leave Template Retrieve checked. If you think you might want to zoom in after the report has been created, uncheck this option.





Click OK to set these options. When you want to do a regular Ad-hoc retrieval, you will have to switch this back to Advanced Interpretation.

For one of the easiest reports, on your worksheet, type <children “Product” in a cell and retrieve the data. (If you have mouse buttons turned on, double click, otherwise, go to the Essbase menu and select Retrieve). If your database has been loaded and calculated, you will see the following:

How simple was that?


This is great if you want to get a list of all product groups for a drop down selection list. By changing the command to <IDESCENDANTS "Product"

you could get the entire product list. Turn on or off “Suppress Missing Rows” to toggle between getting all rows or rows with data. If you want all rows, you could make it even faster by turning on “Navigate without Data”

The problem with this is you get the shared member rollups included twice (see rows 3 and 19 for example. By changing it a little, you can exclude what you don’t want. Try <LINK(<DESCENDANTS( "Product") and not(<DESCENDANTS("Diet Drinks")))

It removes all of the Diet Drink rollup. I don’t like diet drinks anyway.

OK, this is nice, but what is the point, one zoom in and I have it in a regular report. How about creating two drop down lists? One to give you the children of Product and the second one to give you the level zero members of what you select in the first list. Have your first list created and populating a dropdown box using <Children “Product”, then when you select an item from the first list, create the second report script command. So for instance, if you select “Root Beer” from the first list <LINK( <DESCENDANTS( "Root Beer") and <LEV("Product",0) ), you would get just the level zero of Root Beer.

Not very helpful in this example, but what if you had 50 product groups and 4 levels between it and the level zero members.

You are not limited to simple queries like I have been showing. You can use formatting and selection settings. Give it a try.

In The next installment of the blog, I’ll go over some more ways to use report scripts in excel and also some suggestions on how to improve report script speed