This Essbase discussion board is provided as a free service and dedicated to all the Essbase professionals out there!

# Question About Create Block Calc Script

July 15 2011 at 12:36 PM
No score for this post
Josh Bremer

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