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 a 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.
CPUT (Trigger, Value, Datasource, Catalog, Cube, Hier1, Hier2…Hier20)
Specify whether data is sent to the database (true) or not (false).
Default value: true
Specify the value you want to put to the cell.
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.
(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 can specify up to 20 hierarchies.
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 (e.g. at least 1 heirarchy is needed), the other related arguments such as Hier2 … Hier20 are optional. Addtionally, these arguments are not nessesarily sorted and can be listed in random orders. If not being used, these arguments will be defaulted to their default memeber.
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 our 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 our 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 the Trigger set to either True or False), will result in this column updating, and now shows the written values in our CGET formula.