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.
Specify the leaves range.
Specify the Analysis Services server that you want to connect to.
Specify the catalog of cubes on the datasource that you want to perform your query on.
Specify the cube that you want to perform your query on.
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
and Hier1 are required, while other arguments such as
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 percentage 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 percentage 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, as well as the method of 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.
Pressing 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: