Block Creation in Essbase – Part 2

We had previously gotten our Marketing expenses to populate for Budget and increased by 5% via DATACOPY. Now we’d like to do the same thing but only when Actual Sales are greater than 150. Based on this dataset, New Hampshire will be excluded from the calculation. It might be tempting to say something like:

Referring back to the original rules of block creation that we learned we know that assigning a constant to a Sparse dimension member will create blocks. Saying “New York” = 10; in a calc script will create blocks. Let’s use this to our advantage by setting our Budget Marketing Expense to 0 and then clear it out after as follows:

We’ve enclosed the calc lines within a FIX on Marketing, and then we set the value to 0, which creates the block, and then we set the value to #MISSING which leaves the block in place but sets it up as an empty block. Now we have a set of blocks created that we can write further calculations against:

Note: This technique does create blocks for anything that meets the criteria for the FIX statement(s), so we need to be tight with what we fix on. If we aren’t careful, we can inadvertently create lots of extra blocks. When testing this out, refer to the Database Statistics, and the Number of Existing Blocks before and after you run the calculation to make sure that you haven’t blown up the size of the database. I have also included a “CLEARBLOCK EMPTY” statement to remove any blocks that I created that don’t have any numbers in them.

Finished Calculation

Putting it all together the finished calc looks like this:

Our finished calculation produces this set of data:

 

Which is precisely what we are looking for.

 

If you are having difficulty getting your calculations to work the way you need them to, or if you need some assistance with creating calculations, reach out to PARC Consulting to see how our experts can assist you with your environment.