Question About Create Block Calc ScriptJuly 15 2011 at 12:36 PM
No score for this post
|Josh Bremer |
from IP address 126.96.36.199
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.
"Budget" = @SUM(@WITHATTR("Cost Center", "==", @attributesval("Cost Center")));
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.
Question About Create Block Calc ScriptNo score for this post
|July 19 2011, 8:34 AM |
I have had success using SET CREATENONMISSINGBLK ON;
Too SlowNo 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.
optionNo score for this post
|July 20 2011, 12:09 PM |
And If you use:
SET CREATEBLOCKONEQ ON ;
Filtering by Dense entriesNo score for this post
|July 20 2011, 12:15 PM |
I have a dense member on the left, so that won't work either.
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:
Note his comment that you will need to put the formula into a calc block to get it to work.
This Forum Looks PerfectNo score for this post
|July 20 2011, 12:49 PM |