Best Solution: Using a String Cube to Store a Variable

Instead of hard-coding the month or using a function to retrieve the current month, it would be best to create a variable with a user-controllable value, so that you can specify the current month.

This can be done with a string cube that is just big enough to hold the variable.

For example, the cube CurrentMonth is comprised of two dimensions, aNumber and aString. Each dimension contains a single element, so the cube contains just a single cell that can accept the name of the month that you want to set as current.

An example cube with two dimensions, aNumber and aString

To retrieve this value, you can use the function:

DB('CurrentMonth', 'aNumber', 'aString')

You can then pass the DB function as an argument to the DIMIX function to determine the index value of the month as follows:

DIMIX('MONTH',DB('CurrentMonth', 'aNumber', 'aString'))

This returns the index of the month in the CurrentMonth cube.

You can now further refine the rule statement to incorporate this reference to the CurrentMonth cube.

['Planned Production Qty - Kgs']=IF (DIMIX('Month',DB('CurrentMonth',
'aNumber','aString')), DB('Production',!CakeType,!Month,'Quantity
Produced - Kgs'), DB('Plan',!CakeType,DIMNM('Month',(DIMIX('Month',
!Month)-1)),'Planned Production Qty - Kgs')*[Adj. % from last month']);

This statement says that if the month index for the Planned Production Qty -Kgs value being requested is less than the index of the month contained in the CurrentMonth cube, retrieve the value from the Production cube, otherwise calculate the value by multiplying the Planned Production Qty -Kgs value of the previous month by the Adj. % from last month.

You can now control the calculation of values in the Plan cube by changing the string in the CurrentMonth cube, which is part of the sample data for this guide.

This solution satisfies all the requirements of a well-written rule. It uses only one rule statement for all months, and it makes it unnecessary for you or other users to edit the statement on a regular basis. Additionally, this solution allows you or other users to set the current month as required.