The CPUTNOTE formula is used to send note information from other Excel cells into the database based on the hierarchy information passed into 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.
CPUTNOTE (Trigger, Note, Datasource, Catalog, Cube, Hier1, Hier2…Hier20)
Note is sent to the database (
true) or not (
Specify the value of Note which you want to send to the database.
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 can specify up to 20 hierarchies.
To send note information from other Excel cells into the database based on the hierarchy information passed into the formula. These cells may contain static values, values from extracts, or values with underlying Excel formulas, and these values can be either numeric or text.
The CPUTNOTE formula is commonly used in conjunction with a CPUT function, so that the CPUT data is written back to the cube at the same time as the CPUTNOTE is written back to the database.
The trigger (
false) is used to control whether or not the
Note is sent to the database. For example, setting
false will allow you to continue designing a report (and calculating) without data writebacks occurring.
All arguments except
Hier2 ... Hier20 are required.
The notes in column I 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 K.
Column C represents the value currently appearing in the cube, and is a standard CGET formula. Likewise, columns E and G are using the CPUT formula.
true (set cell C12 to
TRUE) and recalculating the sheet results in the values being written (as shown in column K)