Troubleshooting
Problem
Users types in a value to a consolidated cell (C: element), which was 0 (zero) originally.
User expects that (instead of the default proportional spread) the engine automatically should use the 'Equal Spread to Leaves' option.
- Therefore users could simply type in a value to a C: element, and the even spread would be done automatically
- This is the behaviour of older versions of TM1/PA
However, this is not what happens in this Technote's scenario.
- Instead, if a user tries to type in a value to a C: element (which was 0 / zero before) then an error message appears.
Symptom
System.Exception: SpreadTotalZero (191)
at Cognos.Office.Tm1.OData.Providers.CommitProvider.Update(String server, String cube, Element commit, CancellationToken token, IDictionary`2 properties)
at Cognos.Office.Tm1.OData.Providers.CommitProvider.Update(IResultSet resultSet, String searchPath, IList`1 values, CancellationToken token, IDictionary`2 properties)
at Cognos.Office.Reporting.DataSource.Query.ConnectedReport.Commit(IList`1 values, CancellationToken token)
at Cognos.Office.Reporting.UILayer.Presenters.GridPresenter.Commit_DoWork(IList`1 values, CancellationToken token)
at Cognos.Office.Framework.Threading.Tasks.PoolTask`1.Invoke()
at Cognos.Office.Framework.Threading.Tasks.AsyncTask.Run()
Cause
TM1 instance's configuration file (TM1s.cfg) had the following setting:
ProportionSpreadToZeroCells=F
This prevented the equal spread.
More Information:
By default, "ProportionSpreadToZeroCells" is enabled (True)
- Therefore, if your TM1S.CFG does not include the parameter, then it is assumed to be true (T)
This means that (by default):
- if all the leaf cells are currently zero:
- It allows you to complete a proportional spread operation without an error
- Cognos TM1 converts the typed entry of "P###" to "LS*###" and applies the spread as an equal spread.
- If any of the leaf cells currently contain data:
-
the behavior is the same as previous versions of Cognos TM1 when performing a proportional spread
-
If you change "ProportionSpreadToZeroCells" to be disabled (False) thenan error displays when you try to perform one of the following proportional spreading operations:
- In TM1 Contributor - Enter a number in a consolidated cell where all of the leaf cells for that consolidation contain zeros.
- In TM1 Contributor and other TM1 clients - Perform a proportional spread operation by either entering a spreading code and number such as "P###" in a cell, or access a proportional spread from the right-click menu or TM1 menu (TM1 Perspectives only) when the leaf cells for that consolidation all contain zeros.
These operations make the TM1 server perform a proportional spread, but the operation fails because all of the leaf cells contain zeros.
Environment
This could occur in many different environments.
- Example: It has been seen in PA for Excel v39, PA 2.0.6.
Resolving The Problem
Modify the TM1 instance's configuration file (TM1s.cfg) to have the following setting:
ProportionSpreadToZeroCells=T
Related Information
Document Location
Worldwide
[{"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSD29G","label":"IBM Planning Analytics"},"Component":"","Platform":[{"code":"PF033","label":"Windows"}],"Version":"","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]
Was this topic helpful?
Document Information
Modified date:
20 September 2021
UID
ibm10885538