Dynamic Formatting for Active Form Columns

[tm123]

Hi,

I need to build an active form for users to enter Forecast data into our Forcasting cube.
We populate the forecasting cube with Actual Data from all Periods Prior to Current Period, and these columns will be read only. For the rest of the Year, we want users to modify the data and they want all the columns of Forecast Periods to be formatted differently. And this should change automatically each month. So for example, in March, they want January and February in Gray background and From March to December in WHite background. One approach Im thinking is to apply conditional formatting in the Formatting area for each cell ( basically I have to repeat this 12 times, one for each month, by checking the attribute of my period dimension )

Is there any other way to do this?

Thanks

[declanr]

Why do you have to do it 12 times? Just hide the DBRA formula in a row and apply one set of conditional formatting in the active form formatting area that applies to the 12 columns.

[tm123]

Why do you have to do it 12 times? Just hide the DBRA formula in a row and apply one set of conditional formatting in the active form formatting area that applies to the 12 columns.

Thanks.
That's what i was trying to do, define the conditional formatting rule in the whole range ( with 12 cells) , but excel was fixing the cell with my DBRA formula ( with $d$12="forecast" so once i changed that, it worked.

One other question. Is there any way to conditionally lock the cells for Actual months? I know i can handle this with cell security rules but i was hoping i can do this in front end. VBA is not an option since this is a TM1 Websheet.

Thanks

[EvgenyT]

One other question. Is there any way to conditionally lock the cells for Actual months? I know i can handle this with cell security rules but i was hoping i can do this in front end. VBA is not an option since this is a TM1 Websheet.

Is there any reason why you want to use cumbersome approach vs designated TM1 capability?

[gstager]

One approach:
- say your basic data measure is 'amount', create a rule-based measure 'amount_locked' = amount
- then your actual columns use 'amount_locked' and the open columns use 'amount': the ruled columns are locked

[paulsimon]

To answer the original question, a conditional format is probably the best way to grey out the actual months of the forecast version.

To answer the point about using a rule based element to prevent entry into the actual periods of a forecast version, then sorry but I have to point out that this approach has some severe limitations. Unfortunately it has been used extensively on the system that I am currently working on, and it is causing severe issues.

One approach:
- say your basic data measure is 'amount', create a rule-based measure 'amount_locked' = amount
- then your actual columns use 'amount_locked' and the open columns use 'amount': the ruled columns are locked

There are various points to make about this approach :

a) It is specific to that report. If a user has direct access to the cube, then they can still alter the actuals. The only way to prevent users from getting direct access to the cube is to hide the navigation bar in TM1 Web and not give them any access to views which severely limits the functionality that TM1 Web can offer. The logic must be incorporated into every report that shows forecast data, which increases the development effort, and the risk that this will be missed.

b) The amount_locked will require feeding, which implies another copy of data which increases cube size

c) The rule based calculation will slow down response.

c) The underlying approach implies the need to copy actuals across to forecast via a process, which is effectively creating a copy of data. Rather than a process based copy, why not use a rule based copy, which then means that since the actual months of the forecast version are derived by a rule, then they cannot be overwritten. It also means that if historic actuals change for some reason, then the change will be automatically reflected and there is no need for a further copy from the actuals version to the forecast version. I have worked on so many sites where I have been told that once the month is closed actuals never change, but after a little time it comes out that there are still back postings so long as it is not a quarter end, etc. With this approach you get the copy from actuals to forecast and the prevention of entry into the actual months of the forecast version in one go.

d) If the underlying approach is that you do not have a separate actuals version and the actuals are only held in the early months of the one Actual/Forecast version, then this implies that you cannot load any actuals until the end of the month. In some cases this is OK. However, in many systems that I have worked on, people want to continue to load actuals for the open period, while they also enter forecasts for the open period to the end of the year, or beyond. This implies the need for two versions, actual and forecast, and then t as mentioned in (c) the actual months of the forecast version can just be pulled across by rules to the forecast version depending on a setting for the current period in a central information cube.

e) If it is not feasible to pull actuals across to the forecast via rules, ie if the actual periods in the forecast version have to be data for some reason, then, to prevent changes to the actual months of the forecast, I would recommend the approach suggested earlier which is to use security to change the access to those months to READ. This can be driven by rules in the security cube which reference the central information cube's current period. This is what I have implemented recently, and I was pleasantly suppressed to find that it did not even require a security refresh.

f) Another possible approach if Actuals must be copied to Forecast by a process is to use the ability to Lock Elements. The advantage of this approach is that data for a locked element cannot be changed accidentally by an Admin or a Process, whereas a READ only element can. The downside is that it can only be set from the UI, and cannot be automated via TI. It will also apply across all versions, which may mean that if you use a process to copy from one cube to another, then it will fail as the period will be locked.

h) Whether you opt to pull Actuals across to the Forecast version via a rule, or to use Security to set the actual periods to Read or Locked, the advantage is that users will be prevented from altering data in the forecast version for actual periods, regardless of how they access the data, be it by a view, or web sheet. By comparison the suggested approach needs additional excel formula in every web sheet which increases development effort, and it can easily be by-passed by anyone with direct access to the cube or a view.

Regards

Paul Simon

[gstager]

Thx for a comprehensive discussion of the design considerations around this issue, Paul. To be clear, we don't rule Actuals into Forecast for certain months, we rule the base input measure 'amount' into a second measure 'amount_locked' for all months. For us, this as worked fine. We've not suffered performance hits, feeding is simple, and Actuals remains read-only so is not updateable even in a direct cube view.

As you point out. lots to consider and every situation differs.