Knowledgebase: HFM
Smartview does not scale HFM figures retrieved using formulas [ID 599051.1]
Posted by Priya Doyle on 11 November 2010 10:23 AM
 Modified 05-NOV-2010 


Smartview 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:
a) Choose Data -> Import External Data -> Import Data
b) Browse to the csv (or .app) file
c) In the first screen of the Text Import wizard, select Delimited and start importing at row  5
d) Choose Next. Select Comma, Space and Treat consecutive delimiters as one:           (N.B. if not done exactly this way trailing spaces in the first column will break the Lookup)   
e) Choose Finish and import into cell A1
f) Sort the table on column A so that the currencies are in order A-Z (again, required for the Lookup function).
Note: It does not matter that this imports additional columns that we will not use.

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.













Show Attachments Attachments

 Excel example (34.5 KB)
(0 vote(s))
Not helpful

Comments (0)
Post a new comment
Full Name: