CPUT¶
The CPUT formula is used to “put” or write a numeric value (data) into a multidimensional cube at the given intersection point as determined by the member information contained within the formula.
It is mostly used when loading spreadsheet budget or forecasting data.
Rather than processing each cell individually, the use of Trigger allows multiple cells to be populated and written once satisfied.
Writeback stored procedures must be in place for CPUT to write the values back to the cube.
Syntax
CPUT (Trigger, Value, Datasource, Catalog, Cube, Hier1, Hier2…Hier20)
Arguments¶
Trigger : boolean (required)¶
Specify whether data is sent to the database (true) or not (false).
Default value: true
Value : number (required)¶
Specify the value you want to put to the cell.
Datasource : string (required)¶
Specify the Analysis Services server that you want to connect to.
Catalog : string (required)¶
Specify the catalog of cubes on the datasource that you want to perform your query on.
Cube : string (required)¶
Specify the cube that you want to perform your query on.
Hier1 : string (required)¶
Specify the name of a valid member in the cube’s first hierarchy
HierN : string¶
Specify the name of a valid member in the cube’s nth hierarchy. You can specify up to 20 hierarchies.
Usage Scenarios¶
To “put” a numeric value (data) into a multidimensional cube at the given intersection point as determined by the member information contained within the formula. It writes to the specified cube, and must have a minimum of one hierarchy and a maximum of 20 hierarchies.
CPUT can be used to send values into the cubes from other excel cells. These cells may contact static amounts, numbers from extracts or amounts with underlying excel formulas.
Attempting to place a string value in will result in an error.
The Trigger (true/false) is used to control whether or not data is sent to the database. For example, setting the trigger to false will allow you to continue designing a report (and calculating) without data writebacks occurring.
While the input of argument Hier1 is compulsory (i.e. at least 1 hierarchy is needed), the other related arguments such as Hier2 ... Hier20 are optional. Addtionally, these arguments are not nessesarily sorted and can be listed in any order. If not being used, these arguments will be defaulted to their default member.
Examples¶
The values in column E are to be written to the accounts listed in column A for each of the additional members (cells A5 to A10) for December 2012 (cell C14) The value will not be written whilst Trigger is set to false (cell C12), and this is shown by the value FALSE appearing in column G.
Column C represents the value currently appearing in the cube, and is a standard CGET formula.
Changing Trigger to true (set cell C12 to true) and recalculating the sheet results in the values being written (as shown in column G). Notice that due to the order of operations on the sheet, our CGET formula (column C) still appears as the original value, even though the value has been written to the cube.
Recalculating the sheet again (with Trigger set to either true or false), will result in this column updating, and now shows the written values in our CGET formula.
