Clearing Blocks in a Reporting Application

One thing I'm learning about Hyperion is there seems to always be options on how to accomplish a task. But there is also usually a "best" choice that emerges. Sometimes, what appear to be options won't actually work given your setup. So, experimentation is the name of the game.

Since I am new to Hyperion, what you read in these posts may or may not be the best way to go about tackling a given problem. I will do my best to circle back and update posts if I find a better way of performing a given task. But the only way to get started is to pick a path and give it a whirl. So, here we go!



The Problem

For members of our account dimension with the "Time Balance Last" attribute, there were (erroneous) data values stored in periods beyond the period where we were loading new budget figures. This meant that the later values were superseding the values we wanted to have reflected, so we needed to clear them out.

Failed Attempt

The first approach to clearing up the problem was to use Smart View and submit #Missing to the intersections we wanted to clear. Unfortunately, submitting #Missing to a reporting application through Smart View actually ends up putting a zero at the data intersection! So, now our balances were showing zero instead of the balance we wanted to appear.

The Solution

The solution I chose to implement involved running a MaxL script through the EAS Console's MaxL editor. The alter database command, documented on page 646 of the Technical Reference Manual, was the critical component of the solution. Using MDX language to fix on the region to be cleared, I ran a script that looked something like this:

alter database 'MY_RPT'.'REPORT' clear data in region
'CrossJoin(
  CrossJoin(
            { (
               [View].[Per],
               [Version].[Current],
               [Year].[FY14],
               [Scenario].[Adopted],
               [Entity].[Total Entity]
              )
            },
            Descendants([Account].[Equity Accounts], [ACCT1000].level, LEAVES)
           ),
           Descendants([Period].[Q4], [Dec].level, LEAVES)
          )'
physical;

A few critical components of the statement to highlight are -

• The "physical" clause of the clear data command must be included in order for the data value to actually be removed.
• The "Descendants" function in the MDX syntax is documented in the Technical Reference Manual, as well. But the "nutshell" summary is that it gets all leaf members at the same level as the ACCT1000 member, for example, beginning at the Equity Accounts parent roll up.

This may not be the most elegant way of clearing the data, but it worked (and it performed quickly).

I hope this will help anyone else coming along who needs to solve the dilemma of clearing data blocks in a reporting application.