| Smart View Formula Becomes Invalid When Sharing Workbooks from Another User | |||||
|
|
|||||
| Modified 26-MAR-2010 Type PROBLEM | |||||
Applies to:
Hyperion Essbase - Version: 9.2.0.0.00 to 11.1.1.3.00 - Release: 9.2 to 11.1Hyperion Enterprise - Version: 6.5.0.0.00 to 6.5.1.0.00 - Release: 6.5 to 6.5
Hyperion Financial Management - Version: 4.1.0.0.00 to 11.1.1.3.00 - Release: 4.1 to 11.1
Hyperion BI+ - Version: 9.0.0.0.00 to 11.1.1.3.00 - Release: 9.0 to 11.1
Hyperion Planning - Version: 9.2.0.0.00 to 11.1.1.3.00 - Release: 9.2 to 11.1
Information in this document applies to any platform.
Excel2003, Excel 2000, Excel 2007
Symptoms
SmartView functions contain the full path of the Smart View Addin when opened. The contents of some particularly complex cells may become invalid when this occurs.Cause
Excel saves the location of the SmartView Add-in with each function. If the stored SmartView add-in location is different from the client's SmartView installation location, the original full path name appears in front of every Smart View formula in the workbook. For example, if a workbook is created on a machine where SmartView is installed to C:\Hyperion\SmartView and the workbook is opened on a machine where it is D:\Oracle\SmartView, Excel will display C:\Hyperion\SmartView\bin in front of each SmartView function.This can cause the formulas to exceed the maximum character length of the cells (255 in Office 2003 and earlier).
Normally SmartView will automatically remove the unwanted path name, but the corruption of very long cells may still occur. If SmartView does not automatically remove the path, refer to Note 827523.1.
Solution
As a best practice, it is recommended that Smart View be installed in the exact same location on all machines to prevent this issue from occurring.Secondly, ensure all cells are less complex. You should ensure that cells are less than 250-(length of pathname of SmartView Add-in).
Suggestions:
- Do not specify all members in every function but rely on the background Point of View (as defined in POV Manager) for most dimensions. Only include dimensions that differ from the background Point of View. This will also improve performance.
- Use cell references rather than hardcoded member names.
- Do not have more than one SmartView function in each cell.
- Split complex calculations over more than one column. Intermediate calculations can be hidden.
Example:
=If(HsGetValue("MyConnection","Scenario#Actual;Year#2010;Entity#Wolverhampton;Account#Sales;...")=0,"Zero",HsGetValue("MyConnection","Scenario#Actual;Year#2010;Entity#Wolverhampton;Account#Sales;..."))
Avoid repeating the function (which will hit performance) by placing the function in, say, C2 and in D2 have:
=If(D2=0,"Zero",D2).
Hide column C.
References
NOTE:827523.1 - SmartView functions are preceded by the full path to HsTbar.xlaNOTE:745097.1 - Functions Saved in a SmartView Worksheet Include the Path of the File HsTbar.xla of the original machine.
GKontos
Comments