This Essbase discussion board is provided as a free service and dedicated to all the Essbase professionals out there!
  << Previous Topic | Next Topic >>Return to Index  

How to use planning user variables in calc manager rules?

August 8 2013 at 3:48 PM
No score for this post
Amit 
from IP address 67.159.36.18

Hello All,

I do understand that how to use user variables in POV and then use prompts in calculation rules to fix on user variable POV. However, in this instance, I am using user variables to drive list of members of rows. A user selects product parent node and the descendants of the parent node are shown in webforms on rows.

Since this user variable drives rows instead of POV, is it possible to use user variable to calculate only descendants of select parent node. Instead of running calculation for full product dimension (sparse), if I could find a way to run these only for user selected product nodes, it should save time on calcs.

TIA
Amit

 
Scoring disabled. You must be logged in to score posts.Respond to this message   
AuthorReply
Cameron Lackpour

98.111.148.59

Have a read of these links...

No score for this post
August 8 2013, 6:01 PM 

http://camerons-blog-for-essbase-hackers.blogspot.com/2012/03/why-i-hate-and-love-calculation-manager.html
http://camerons-blog-for-essbase-hackers.blogspot.com/2012/03/why-i-hate-and-love-calculation-manager_18.html

I am a huge proponent of using the focused aggregation approach in BSO Planning apps. I don't know why it isn't taught as standard practice. It is not a panacea for an overly large plan type/database, but it can be very useful.

Regards,

Cameron Lackpour

 
Scoring disabled. You must be logged in to score posts.Respond to this message   
Cameron Lackpour

98.111.148.59

DOH!

No score for this post
August 8 2013, 6:03 PM 

Sorry, misread your post.

You want to pick up the rows. Sigh. Everyone else, too.

Here's a teaser of how it can be done in Dodeca:
http://camerons-blog-for-essbase-hackers.blogspot.com/2013/07/an-even-better-focused-aggregation-with.html

If Planning could drive what's selected on the rows (however it might be done, user variables sound like a good start), then this same technique could apply.

Sorry for not reading your question all the way through. It has been one of those days...

Regards,

Cameron Lackpour

 
Scoring disabled. You must be logged in to score posts.Respond to this message   
Anonymous

67.159.36.18

Thanks... hopefully a new feature in near future!

No score for this post
August 8 2013, 6:34 PM 

Thanks Cameron. This feature indeed would be really helpful.


 
Scoring disabled. You must be logged in to score posts.Respond to this message   
Christian M.

195.168.157.170

This is the method I use

No score for this post
October 11 2013, 8:52 AM 

Here what I can suggest:

Step1) Set-up @ planning level
1. Create your user variable in planning (E.g. SelectedProduct)
2. Create your webform with the products on row and the relevant selection (e.g. ILev0Desc(SelectedProd))

Step2) Set-up @ essbase level
3. Create a rtp variable (e.g. MyVar) in EAS with the following settings:
-Type: Members (you need to select MemberS otherwise it will not work)
-Dimension: Product
-Default Value: &SelectedProduct (This is the same name of the user variable used in the form)
Note: The validation of the variable in EAS will fail but you can save it and it works

4. Use this rtp in the BR code (e.g. @RELATIVE([MyVar],0) )

Step3) Attach the BR to the form
5. You can attach the BR to the form and select the relevant options (on-save, take selection from the forms, etc..)
6. You can as well hide the prompt window BUT (BIG "BUT")

- In the version 11.1.1.3 I was able to hide in this way the prompt window
- In the version 11.1.2.2 (with Calc manager) I'm currently struggling to hide the prompt window and I would really appreciate if anybody can give me a solution on this!!!!!!!








 
Scoring disabled. You must be logged in to score posts.Respond to this message   
Cameron Lackpour

74.109.20.23

Are you saying Calc Mgr picks up Planning user variables?

No score for this post
October 11 2013, 11:28 AM 

That would be pretty cool if true. Or it could be yet another example of my colossal ignorance. Probably a coin toss.

I'm getting confused by this line:
>>-Default Value: &SelectedProduct (This is the same name of the user variable used in the form)
^^^Is that leading ampersand indicating a Planning user variable or an Essbase substitution variable?

Regards,

Cameron Lackpour

 
Scoring disabled. You must be logged in to score posts.Respond to this message   
Christian M.

195.168.157.170

Yes it does

No score for this post
October 11 2013, 12:24 PM 

yes, I'm saying exactly this.

Through this method I'm able to pass to the BR the value of the PLANNING USER VARIABLE! :)

We implemented this 3/4 months ago in our PROD env. and we are doing the same with the new version.
This is tested and it working perfectly! :)

The only problem is that with the 11.1.1.3 I was able to HIDE as well the prompt window and the final user was just pressing SAVE and the process was completely transparent to them.

With the new 11.1.2.2 (where we use calc manager) it pick the PLANNING USER VARIABLE correctly but I can't hide the prompt window.

If you look at the prompt window, after pressing save in the form, you will see in the field &SelectedProduct but if you go ahead it pick up the value of the variable.

Just to be more precise: we do not have any substitution variable with the name "SelectedProduct" so this is really the Planning user variable! :)


Now.. please.. please.. please suggest or help me in finding how to hide the prompt window!!!! :)



BTW ==> I used a lot of information from your blog!! I'm now very happy that I can as well share something new for you!!! ;)


 
Scoring disabled. You must be logged in to score posts.Respond to this message   
Celvin Kattookaran

199.248.185.22

wow that's exciting

No score for this post
October 14 2013, 11:07 AM 

Christian M, Can I steal your idea for my next post, if I share the secret to hide the prompt window :)

You can perform the hide in Calc Manager, if you open the rule in Calc Manager and go to the tab called "Variables", just check "Is hidden" and that's it.

I tried your method in 11.1.2.3 and at first it didn't work. I tried couple of things and at last it started working. (I'm existed to see this working)

1. Logged off after setting the variable default in Calc Manager (By the way, don't use the button in top right corner to deploy the script, it does a validate and deploy, Actions -> Deploy)
2. Trying running the same calc in Essbase by hardcoding the value and then clearing it.
3. Logged off again (just for kicks)
4. Set the variable to a different intersection and test (it starts working)



Regards

Celvin

 
Scoring disabled. You must be logged in to score posts.Respond to this message   
Christian M.

217.192.13.243

It's working!!

No score for this post
October 14 2013, 1:29 PM 

Thank you!!! it's working correctly! The window desappeared now!
Actually this is the same option flagged in the old version but I did not find it in this new env! :P


Thank you!! Use my suggestion! don't worry!

 
Scoring disabled. You must be logged in to score posts.Respond to this message   
Cameron Lackpour

74.109.20.23

Here's how it works, a discovery, and a question

No score for this post
October 14 2013, 2:13 PM 

So, if you had Entity going down the rows and Segment in a drop down in the Planning sample application...

And you had a dynamic Planning user variable called "varUVEntity"...

And a Calculation Manager rule (Members, not Member, although I have to try that one as it doesn't make sense) variable called "varBRUVEntity" with a default value of &varUVEntity...

Your code would look like (and try not to think about why Account and Period are stored coz that's how Oracle rolls):
SET UPDATECALC OFF ;
SET AGGMISSG ON ;
SET EMPTYMEMBERSETS ON ;

/* SET CALCPARALLEL 4 ; */

FIX("HSP_InputValue", "FY12", "Local", "Plan", "Working")
FIX({varSegments})
@IDESCENDANTS({varBRUVEntity}) ;
@ANCESTORS({varBRUVEntity}) ;
ENDFIX
FIX(@IDESCENDANTS({varBRUVEntity}), @ANCESTORS({varBRUVEntity}))
@ANCESTORS({varSegments}) ;
ENDFIX
CALC DIM ("Account", "Period") ;
ENDFIX

That's it -- the holy (well, sort of) grail in focused aggregations in Planning forms. That is awesome. Beyond awesome.

Sort of semi-interestingly, you will see different values for the variable when you view this in the rule vs. the script. Here's what the rule looks like -- the value is getting replaced for the variable (I have just a script within a rule as I am Olde Skool). And THAT is what was one source of confusion for me -- what were the & characters doing there? They're only in the rule view of the script.
SET UPDATECALC OFF ;
SET AGGMISSG ON ;
SET EMPTYMEMBERSETS ON ;

FIX("HSP_InputValue", "FY12", "Local", "Plan", "Working")
FIX(BAS)
@IDESCENDANTS(&varUVEntity) ;
@ANCESTORS(&varUVEntity) ;
ENDFIX
FIX(@IDESCENDANTS(&varUVEntity), @ANCESTORS(&varUVEntity))
@ANCESTORS(BAS) ;
ENDFIX
CALC DIM ("Account", "Period") ;
ENDFIX

And now for the question: Christian -- how on earth did you figure this out? What led you to do this? Members instead of Member? That alone would have just killed me.

Regardless, that is beyond cool and I am writing this up right now (Celvin, we have coopetition on this one, we need to talk abou that EPMA one as Dan has made some real progress) for my blog. May this (hopefully) be the last Focused Aggregation blog post I ever have to write. Long live Hybrid Essbase!

:)

Regards,

Cameron Lackpour

 
Scoring disabled. You must be logged in to score posts.Respond to this message   
Cameron Lackpour

74.109.20.23

Just validated, must be Members instead of Member

No score for this post
October 14 2013, 2:19 PM 

Christian,

Again, I have to ask -- how did you figure this out? To say that this is not am intuitive approach is to understate it.

Why does Members work when only (if you look at my code you can see it) one single member is being used. Bizarre.

Did someone at Oracle give this away? If so, I have to wonder why it was promulgated more widely as it is huge in its BSO performance implications.

Regards,

Cameron Lackpour

 
Scoring disabled. You must be logged in to score posts.Respond to this message   
Celvin Kattookaran

199.248.185.22

i tried with member in calc mgr

No score for this post
October 14 2013, 3:11 PM 

The one I tried was using Member as Calc Mgr Variable in 11.1.2.3

Regards

Celvin

 
Scoring disabled. You must be logged in to score posts.Respond to this message   
Cameron Lackpour

74.109.20.23

So you're saying Member worked?

No score for this post
October 14 2013, 3:28 PM 

I tried that but the prompt kept on showing up, even though I had the variable set to hide.

Hmm, I'll have to try it again.

It still amazes me that this works. Why oh why oh why wasn't this documented before? Or is it and I never saw it?

Regards,

Cameron Lackpour

 
Scoring disabled. You must be logged in to score posts.Respond to this message   
Cameron Lackpour

74.109.20.23

Member worked in Smart View, but throws a dialog box in IE

No score for this post
October 14 2013, 3:46 PM 

But both Member and Members work in Smart View.

Go figure.

Regards,

Cameron Lackpour

 
Scoring disabled. You must be logged in to score posts.Respond to this message   
Christian M.

178.197.231.252

Re: Member worked in Smart View, but throws a dialog box in IE

No score for this post
October 14 2013, 5:54 PM 

... Sometime..if you desire something very hardly...you figure out the way to have it done by yourself!!! :)

Joking aside...nobody from Oracle gave me any info.

We had a very strong need to fix on the dimension we have on row and drive this from a user variable...

Im as well from the old school..OFA/express... so I started to make some tests and investigations and a bit of intuition helped as well.
Honestly I don t remember what made me think of trying this configuration but I saw that the system was using the &variablename somewhere... ah..yes.. I remember now... I think it s in the member selection menu in the planning app when u want to make a selection.. you will see &variablename :)
(Can t check it now)

then I did 2+2 = 4 :)

Regarding MemberS instead of Memeber to hide the window... just tried all the possibility as I was desperate!!! ;)

I agree with you that this can have huge impacts on systems design and,on the other side, I still did not understand why Oracle did not provide yet an official way of doing this.. it s a question I m asking since I saw hyperion the first time..

I honeslty think that it's a bug..but a good one this time.. :)


Christian



 
Scoring disabled. You must be logged in to score posts.Respond to this message   
Joe Aultman

97.77.163.170

How Exciting

No score for this post
October 15 2013, 8:28 AM 

Congratulations, Christian!

And Celvin!

And Cameron! You must be beside yourself. You've finally spotted that... unicorn?... white whale?... ivory-billed woodpecker? Choose your metaphor, I guess. Maybe you have already. I'm off to read your blog.

-- Joe

 
Scoring disabled. You must be logged in to score posts.Respond to this message   
Cameron Lackpour

74.109.20.23

Yes, it is a bit of the Holy Grail of Focused Aggregations

No score for this post
October 15 2013, 9:18 AM 

Joe,

Yes, it is like sighting the Great White Whale.

I'm working on the post right now and it should be out in a couple of hours.

I've just demolished the <Form Calculate> argument (it is super sad but, yes, I did have an argument with another consultant earlier this year re that in-built business rule) and am now working on the Focused Aggregation bit.

Then I have to send the post to the Calc Mgr PM so they don't remove the functionality. That would be most unfortunate.

Regards,

Cameron Lackpour

 
Scoring disabled. You must be logged in to score posts.Respond to this message   
Cameron Lackpour

74.109.20.23

Blog post is now up

No score for this post
October 15 2013, 12:57 PM 

http://camerons-blog-for-essbase-hackers.blogspot.com/2013/10/row-driven-focused-aggregations-in.html

Christian, without gushing too much, thank you so much for sharing this approach with world+dog. It is absolutely going to change the way I (and I suspect quite a few other people) do form aggregations in BSO Planning.

Thanks again.

Regards,

Cameron Lackpour

 
Scoring disabled. You must be logged in to score posts.Respond to this message   
Mike H

108.67.200.190

Thinking like Java ... passing objects

No score for this post
July 5 2014, 8:32 AM 

A member is an object that that methods and properties. An array of members is a different sort of object, a group of members. An array of a SINGLE member <> a member. The array is the member in a container that has different methods and properties.

Example - FY14 <> @RELATIVE(FY14,0), even though they are essentially the same thing to us humans. FY14 is a member of the year dimension. @RELATIVE(FY14,0) is that same member (since FY14 has no children) contained in a package.

The calc engine needs to be fed exactly what it needs ... members in an Array container.

 
Scoring disabled. You must be logged in to score posts.Respond to this message   
Mike H

108.67.200.190

Does @LIST() work with a member (not members) variable?

No score for this post
July 5 2014, 9:00 AM 

@LIST puts a comma delimited set of members into a Java array object. So @LIST(FY14) gives the same object as @RELATIVE(FY14,0). Using @LIST(&<SingleMbrVarName>), it may be possible to use a single member variable instead of the plural kind.

 
Scoring disabled. You must be logged in to score posts.Respond to this message   
 
< Previous Page 1 2 Next >
  << Previous Topic | Next Topic >>Return to Index  

RSS feed for this forum - http://www.network54.com/Forum/58296?xml=rss. Please email hypess (at) gmail.com, if you have any questions/feedback/issues.