Cameron Lackpour
Cameron Lackpour

6:13 PM - Oct 14, 2013 #11

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
Quote
Share

Cameron Lackpour
Cameron Lackpour

6:19 PM - Oct 14, 2013 #12

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
Quote
Share

Joined: 8:50 PM - Feb 24, 2012

7:11 PM - Oct 14, 2013 #13

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

Regards

Celvin
Quote
Like
Share

Cameron Lackpour
Cameron Lackpour

7:28 PM - Oct 14, 2013 #14

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
Quote
Share

Cameron Lackpour
Cameron Lackpour

7:46 PM - Oct 14, 2013 #15

But both Member and Members work in Smart View.

Go figure.

Regards,

Cameron Lackpour
Quote
Share

Christian M.
Christian M.

9:54 PM - Oct 14, 2013 #16

... 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


Quote
Share

Joe Aultman
Joe Aultman

12:28 PM - Oct 15, 2013 #17

But both Member and Members work in Smart View.

Go figure.

Regards,

Cameron Lackpour
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
Quote
Share

Cameron Lackpour
Cameron Lackpour

1:18 PM - Oct 15, 2013 #18

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
Quote
Share

Cameron Lackpour
Cameron Lackpour

4:57 PM - Oct 15, 2013 #19

http://camerons-blog-for-essbase-hacker ... ns-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
Quote
Share

Rajkumar
Rajkumar

11:00 AM - Jul 04, 2014 #20

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!!!!!!!






Hi Chris,

Need help to create a RTP variable in EAS 11.1.2.2 as per your step 2 @ essbase level, as i dont find any option of creating it. as it has completely moved to CalcMngr in 11.1.2.2.

Thanks,
Rajkumar
Quote
Share

Confirmation of reply: