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, February 27, 2014

Undocumented Change to Smart View 11.1.2.5

As we all know (or at least the cool kids know) the changes to Smart View have been coming quickly. So fast that the wise at Oracle decided to decouple its releases from the rest of the EPM stack. That is how we are at 11.1.2.5.200 while the rest of the stack is 11.1.2.3. I think this is a good thing as it allows Smart View to be more proactive in introducing changes to help us do more and better reporting.  I applaud Oracle for doing this.

However, because the changes are coming so quickly, not everything gets documented very well. From the 11.1.2.5 new features documents we see this.

“Change to Display of Duplicate Variable Names

With this release, Smart View added functionality to display fully qualified variable names when variable names are duplicated. This helps to identify variables defined at the global, application, and database levels. “

But what does it mean? One of the cool features of Smart View is the ability to use substitution variables. You are thinking, what is so cool about that, the add-in could always do it and Smart View could do it since version 9X. Well yes, in both cases you could enter your substitution variable like &CurrMonth and when you retrieved, you would get the value of the substitution variable returned when you refresh the data. With this comes the limitation that if you save the spreadsheet, you save the actual value of the substitution variable and not the substitution variable itself. Huh? What I mean is suppose the substitution variable is set to Mar. It saves Mar and not &CurrMonth. 

Starting in Smart View 11.1.2.1.102 a new way to use variables was introduced. HSGETVARIABLE. When used in a worksheet, it would retrieve the value of the variable but keep it as a formulaic member so next month when you changed the variable, your report would update with the new information. Pretty cool!

So lets get on to the change. Suppose I have the following substitution variables:

image

Notice I have both a global and database specific variable with the same name.

In 11.1.2.3.X and below, I could enter

=HSGetVariable(“HSActive”,”CurrMonth”) and when refreshed, get the variable. Note, HSActive means the current active connection for the sheet, I could also put in a private connection name.

image

Starting in 11.1.2.5, the command allows you to put in a qualified name

=HSGetVariable(“HSACTIVE”,”Sample.CurrMonth”) to determine the scope of where to get the variable from. If left blank, it seems to get the global variable. If qualified, it picks up the variable from where you specify

image

The screenshot above shows what the formulas look like, but in reality when you enter them you get is

image

Once you refresh you get

image

The difference between rows 1 and 2 is Row one has been physically been changed to Mar while row 2 is still a formula.  The interesting thing is unless you qualify the application, you will get the global variable. 

Further, I’m using HSACTIVE for the connection name, you can actually use a different connection instead. For example if I created a private connection for Demo.Basic called DEMO, I could use it and it would pull from the Demo.Basic version of the variable even if I’m connected to Sample.Basic.

So here is where it is getting more interesting. In 11.1.2.5.200 I tried the same thing. When trying to use a global variable

=HsGetVariable("HSActive","currmth")

I get an error message

image

and what did not work before supplying both the application and database now does work

=HsGetVariable("HSActive","sample.basic.currmth")

As a test, I deleted the application and database level variables and then tried:

=HsGetVariable("HSActive","currmth")

and now it returns the global variable.

By the way &Currmth stopped working as well. 

Between the two versions Oracle development has apparently been refining how this functionality works. So what worked in 11.1.2.5 does not necessarily work the same way in 11.1.2.5.200 and of course different than prior versions.

Anyway this is a good enhancement to substitution variables and I urge you to give it a try.

10 comments:

Anonymous said...

Hey Glenn, cool trick!

Question.. for some reason, the values of my variables are wrapped in quotes. I think FR likes it that way?

They work fine using the static method on the spreadsheet, &CUR_MONTH, but the HSGETVARIABLE function returns the value, quotation marks and all, which isn't recognized, so the dimension is brought in at the top level.

I can wrap the function in the REPLACE function, but I was wondering if you ran across this, or had any thoughts..

GlennS said...

As far as I remember, in the old add-in, we had the same issue. If I wanted to use a variable in a calc script, I had to have it in quotes for the calc and without them for the spreadsheet as it would return quotes and all. If you do wrap it in the replace function, then make sure you go into the smart view options and checkmark refresh selected functions and their dependents on the advanced tab

Anonymous said...

Hi Glen,

If you use this function and then use Undo/Redo function on the retrieval sheet the formula get replaced by value!.

Thanks, Gaurav

ansky08 said...

I know this post is old-ish, but it just became relevant to me.

Thanks for your insight on how to keep the substitution variable within the SV report upon refreshing.

It works great with ad hoc analysis -- say HsGetVariable("&CurMonth") returns "Aug", and the value helps return Aug figures. However, when using a function HsGetValue which references the cell with the HsGetVariable function... I get "#Error Run Formulas Interrupted" upon refreshing.

I have tried a couple variations, including trimming the value returned and embedding the HsGetVariable within the HsGetValue formula -- to no avail. Any ideas?

I have set up an SR to Oracle since I cannot find the answer to this.

GlennS said...

something you can try, is on the Smart View Options is an option "Refresh selected functions and their dependents" This makes Smart view do two passes to resolve the variable then retreive

Anonymous said...

Hello Glen,

... The interesting thing is unless you qualify the application, you will get the global variable...

Well, when there is no variable at application and database level (which you call global variable, but is a database variable) will it pick up the application variable?

It is a feature, but I wonder how useful it is. When we do implementations, we agree on one level of variables and not more. Just because other clients do not have this feature
and it will become too complex to maintain the code with substitution variables with the same name, for different purposes.

Regards,
Philip

GlennS said...

Phillip,
I have seen too many implementations where a global variable was used but in one application they overrode it for special needs. I agree, managing the variables at the same lever is the best approach, but some functions like in Planning can make that difficult. This at least gives you a way to deal with the variations

GlennS said...

Phillip,
I have seen too many implementations where a global variable was used but in one application they overrode it for special needs. I agree, managing the variables at the same lever is the best approach, but some functions like in Planning can make that difficult. This at least gives you a way to deal with the variations

ansky08 said...

thanks GlennS -- "Refresh selected functions and their dependents" did the trick!

Peter Nitschke said...

Ahhh Glenn - you're a lifesaver.

Been writing something up which worked perfectly in a previous version.

Spent the last few hours thinking I was going insane because something I've got listed as working perfectly 8 months ago no longer works!

Anyways - finally went looking and found your note....Refresh selected functions and their dependents fixed my problem.

So thankyou very much!

Cheers
Pete