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  

Question About Create Block Calc Script

July 15 2011 at 12:36 PM
No score for this post
Josh Bremer 
from IP address 198.182.194.170

Hey all, I have a script that consolidates all the totals of one attribute into one of the organizations and zeros out the rest. The problem is that per usual, if the block doesn't exist, the calc doesn't run correctly. I'll give an example.

FIX((@DESCENDANTS("Company 19")
"Budget" = @SUM(@WITHATTR("Cost Center", "==", @attributesval("Cost Center")));
ENDFIX;

Example, I have ten organizations with the same cost center of 1000, 10-1000,11-1000...19-1000. I'd like to consolidate all of those amounts into the Company 19 amount. The problem is if the block isn't created for 19-1000 there's nothing I can do. Also, according to the calc above, "Budget" is dense so I can't use createblockoneq. We have loads of organizations, so I can't us the organization on the left side of the equation. Any idea? Can I run an if statement and set createonmissingblk on if there is an amount in one of the ten? I can't set createonmissingblk for everything because it will make the cube WAY too big.

Ideas?


 
Scoring disabled. You must be logged in to score posts.Respond to this message   
AuthorReply
Tim Young

161.254.5.251

Question About Create Block Calc Script

No score for this post
July 19 2011, 8:34 AM 

I have had success using SET CREATENONMISSINGBLK ON;

Good luck,

Tim

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

198.182.194.170

Too Slow

No score for this post
July 20 2011, 12:07 PM 

The command "SET CREATENONMISSINGBLK ON" does work, but it creates way too many blocks. I'd only there are around 10,000,000 possible combinations, but only about 40,000 entries in the GL. I was curious if there was a conditional way to turn that on.

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

189.4.213.97

option

No score for this post
July 20 2011, 12:09 PM 

And If you use:

SET CREATEBLOCKONEQ ON ;

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

198.182.194.170

Filtering by Dense entries

No score for this post
July 20 2011, 12:15 PM 

I have a dense member on the left, so that won't work either.

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

97.248.140.184

Why not use a cross-dim on the left side of the equation?

No score for this post
July 20 2011, 12:38 PM 

See Glenn's suggestion here:
http://forums.oracle.com/forums/thread.jspa?messageID=4014018

Note his comment that you will need to put the formula into a calc block to get it to work.

Regards,

Cameron Lackpour

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

198.182.194.170

This Forum Looks Perfect

No score for this post
July 20 2011, 12:49 PM 

Thanks so much!

 
Scoring disabled. You must be logged in to score posts.Respond to this message   
 
  << 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.