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

(Required)

The Trigger (True/False) is used to control whether or not data is sent to the database.

Default value: true

Note

(Required)

Specify the value of Note which you want to send to the database

Datasource

(Required)

Specify the Analysis Services server that you want to connect to.

Catalog

(Required)

Specify the catalog of cubes on the datasource that you want to perform your query on.

Cube

(Required)

Specify the cube that  you want to perform your query on.

Hier1

(Required)

Specify the name of a valid member in the cube’s first hierarchy.

HierN

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 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 input of arguments Trigger, Note, Datasource, Catalog, Cube, and Hier1 are compulsory, the other arguments such as Hier2 … Hier 20 are optional.

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 our 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 our trigger to True (set cell C12 to True) and recalculating the sheet results in the values being written (as shown in column K)