The CSPREAD formula is used to create a drop-down menu in published reports that allows users to select user configured data spreading types across an unlimited amount of members in a hierarchy. Used in conjunction with SPREADING HOLDS, it is possible to spread values across multiple combinations of data points.
Since data is to be written to the cube, writeback must be enabled.
CSPREAD (Spreadrange, Totalrange, Leavesrange, Datasource, Catalog, Cube, Hier1… Hier20)
Specify the spread range
Specify the total range
(Required) Specify the leaves range
(Required) Specify the Analysis Services server that you want to connect to.
(Required) Specify the catalog of cubes on the datasource that you want to perform your query on.
(Required) Specify the cube that you want to perform your query on.
(Required) Specify the name of a valid member in the cube’s first hierarchy.
Specify the name of a valid member in the cube’s nth hierarchy. You are allowed up to 20 hierarchies
To create a drop-down menu in published reports that allows users to select data spreading types across an unlimited amount of members in a hierarchy.
Input of values of Spreadrange, Totalrange, Leavesrange, Datasource, Catalog, Cube, and Hier1 are compulsory, while other arguments such as Hier2 … Hier20 are optional.
There are a number of components to spreading, so to simplify we will break down into components
SpreadRange or Spread Rules¶
The spreading rules are defined in cells E27 to R35, and define not only the drop down that will appear on the web, but also the basis of proportion (All spreading is completed using % proportions). For example, should Even spread be selected and assuming no holds are in place, the user would expect 1/12 (or 8.33%) of the total number to be allocated to each month. Should Quarterly spreading be required, the user would expect 1/4 (or 25%) of the total to appear in each quarter (in this instance Sep, Dec, Mar and Jun each year. A similar methodology is followed for each remaining spreading rule.
To extend the rule type, a user would define a discrete name, input the required values which will determine % and extend the SpreadRange to cover as well.
In the example of Last Year (row 34), it is reading the value in row 24 (which uses a CGET formula to pull values for Operating Profit, although it could be any account the user requires) with the Excel OFFSET formula
The CSPREAD formula is in cell F20, and is
It will spread the value entered into cell E20 across cells G20 to R20, according to the rules outlined in cells E27 to R34
On publishing the report will resemble
Spread rules have been left unhidden to facilitate understanding of spreading occurring. In production, these rows would be hidden.
Operation – Spreading¶
Determine and enter the spread amount, and how you which method you would like to use in spreading.
In this example we would like to spread 10,000 using the 4-4-5 method. This will mean we should see a pattern of 4/52, 4/52 and 5/52 four times over the year.
Clicking F9 to recalculate, we obtain the expected results:
Changing to 5-4-4 method, we obtain:
Each of the following provide spreading as expected
Last Year (all appeared in Jan):
Holding Values and Spreading¶
Having completed the spread calculation, a user may decide they need to increase a value.
Using the Even spread as an example, the user may decide they need Nov and Mar to be 1,000, and not the spread 833.
To enter, the user would simply type these values in the required cells
And recalculate (press F9)
An alternative requirement may be to hold certain cells, and recalculate the others. This can be achieved using the SPREADING HOLD icon
Identify the values to be held and click on them (use the tab key for non continuous cell ranges). In this example we will hold Sep, Nov and Mar.
To clear a hold selection, click on CLEAR SPREADING HOLD icon
You will need to start the selection process again should you clear selections.
Having selected the hold values, enter the new value and/or spread method. We will use 20,000 as the new value, which will be spread based on last year (for ease of use of confirmation calculation is happening as expected). If calculation occurs as expected, we should see:
|Jan:||17,166.66 (20,000.00 – 833.33 – 1,000.00 – 1,000.00)|
All other months should be zero.
This is what we observe: