Why I created a blog

Its been many years since I first created this blog. It has remained true to Essbase and related information over those years. Hopefully it has answered questions and given you insight over those years. I will continue to provide my observations and comments on the ever changing world of EPM. Don't be surprised if the scope of the blog changes and brings in other Hyperion topics.

Thursday, March 27, 2008

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


Sunil said...

Hi Glenn,

Very useful tips. i think the end user needs to know about the reporting commands and also very importantly the normal retreival provides you the flexibility and user friendliness in retreiving data; you can place members/dimensions anywhere you want in excel sheet and you can change the view of the data etc etc.

i think a normal user would go for simple "legacy" reteival method. :-)

This is just my personal exp, since am into Essbase for mere 2 yrs; you can give me more insights on this. :-)

Once again, thanks for starting up the useful blog. like Jake Turrell mentioned in the Network54, pls come up with articles on best practices etc.....


Anonymous said...

WoW!! Cool stuff..

Anonymous said...

Foound this link on Network54 Essbase board and I already added it to my RSS reader.

Anonymous said...

It is very nice. I am also interested to know how to write report scripts using report script editor. also, it would be nice if you teach us how to export zero level data using report script.

great efforts please continue with this blog

Anonymous said...

One of the early uses for report scripts may have to create a batch of reports following a calculate. Some of the syntax reminds me of SAS and Focus.

srinivas said...

Excellent article.

Anonymous said...

Very useful indeed.
But... sorry I didn't understand how you set up "cascade" drop down list box: how can the second drop down list (level 0 products) be updated after a selection in the first drop down list (children of product) ?