Smartview does not scale HFM figures retrieved using formulas [ID 599051.1]
Posted by Priya Doyle on 11 November 2010 10:23 AM
SymptomsSmartview does not scale figures retrieved from FM when using formulas
The Scaling option in Hyperion (Smartview)-> Options, Display tab, only affects Adhoc Queries.
The following workaround may be used.
1. Create a sheet with a list of all the application currencies and their scaling.
(Columns C and D may be easily combined.) It is possible to derive the entity name and currency from an FM extract (see below).
2. Create a new worksheet and name it Currencies.
Create list of application currencies and their scaling factors. The easiest way to do this is to save metadata from FM as an .app file (HFM Application Format) with a comma delimiter and only Currency selected:
Load the file into Excel as a CSV file:
3. In the first worksheet next to each entity, insert a column containing HsCurrency formulas
4. Insert a column containing a lookup function to return the scaling from the currency sheet that relates to the entity currency,
e.g. =10 ^ LOOKUP($B4,Currencies!$A$2:$A$4,Currencies!$B$2:$B$4)
Note: Both these columns may be hidden afterwards 5. Amend the HsGetValue formulas to scale by dividing by the Scale Factor.