CGETDATE¶
The CGETDATE formula is a variant of the CGET formula and is similarly used to retrieve a date value based on the hierarchy information passed into the formula. Where a hierarchy from the targeted cube is not specified, the default member for that hierarchy is used.
This formula behaves differently when it is published by giving the user a date picker on the cell to facilitate data entry of dates.
When a date is entered, the date is converted into the same format that is used in Excel, a serial date, and that number is written back to the cube.
Since you are writing values back to a cube, writeback must be enabled on the cube for this to work. If you do not have writeback enabled, you may receive writeback errors.
Important Note: The setup of Windows on your CALUMO Server can dictate the default date format that this formula uses to write back in Published Reports. If you notice your values being written back in a different format, have your I.T. Department investigate the following registry key on your CALUMO Server.
- HKEY_USERS\.DEFAULT\International\sShortDate (Set this to your desired format, e.g. dd/MM/yyyy)
Syntax
CGETDATE (Datasource, Catalog, Cube, Hier1, Hier2…Hier20)
Arguments¶
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. Up to 20 hierarchies are allowed to be specified.
Usage Scenarios¶
This formula is commonly used whenever a friendly date-picker interface to select dates from is required. For example, when building an employee report, and would like to capture (as a measure) the start or end dates.
Usage becomes apparent once published.
Examples¶
On publishing this becomes:
