How to make business rule to dynamically adjust Scenario and UD4

[Carlos_S]

I am working on a cube view that will show balance sheet data for actualized months next to estimate months. I would like for my rows to reflect actual data for the months that have been actualized and then within the same row show my estimated future data for the estimate months.

The difficult part I am running into is that I need to use a different UD4 for my estimate months in order to allow the ability to overwrite the estimated amount.

I believe a business rule that would dynamically change the Scenario and UD4 would give me what I need.

[ChristianW · Accepted solution]

Isn't this working?

[Carlos_S]

My issue is that Col1 and Col2 wouldn't always be a static scenario/UD4. Based on how far along the fiscal year has gone then columns would move from forecast to actual.

My current solution is to build column templates where the user would have to select a parameter for last completed month and that parameter would select the correct column template. The column template would be pre-built to reflect Col1 as actual and Col2 as forecast and so on.

[db_pdx]

Hi Carlos_S: maybe the missing piece is that parameters you might need?  I'm not sure how your UD4 fits in, but the solution Christian proposed is exactly what we use and it works great.

To help reinforce his proposal, here is how we implemented it:

• A parameter in the Fixed POV that prompts a user to select the 'Month-End Close Period'
• Two columns in the layout section: one which provides all trailing year actuals, one that provides all future period forecast
• The T#[].AllPriorInYearInclusive and T#[].AllNextInYear is the secret sauce that makes it dynamic

Here are our components (very similar to what Christian listed):

And here is what it looks like what a user makes a selection for 'Month-End Close Period' (our |!Select_Time_Close!|)

When selecting 2022M5

When selecting 2022M6

You'll notice how the periods (and formatting) dynamically move with the time period selection.  Hope this helps and would encourage that you give @ChristianW proposal a try.  Cheers!   -db

[Carlos_S]

Thank you, I'll give it a try.