CPUTNOTE¶
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.
Syntax
CPUTNOTE (Trigger, Note, Datasource, Catalog, Cube, Hier1, Hier2…Hier20)
Arguments¶
Trigger : boolean
(required)¶
Specify whether Note
is sent to the database (true
) or not (false
).
Default value: true
Note : string
(required)¶
Specify the value of Note which you want to send to the database.
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 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 (true
/false
) is used to control whether or not the Note
is sent to the database. For example, setting Trigger
to false
will allow you to continue designing a report (and calculating) without data writebacks occurring.
All arguments except Hier2 ... Hier20
are required.
Examples¶
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.
Changing Trigger
to true
(set cell C12 to TRUE
) and recalculating the sheet results in the values being written (as shown in column K)