I have recently come across some HsGetValue issues in Smart View where #Invalid and #Value were being returned in err from the HsGetValue function in spreadsheets being used for reporting. It took quite a bit of investigating to figure out what was going on in these cases, and below are some findings and best practices that should prove helpful in resolving these errors in Smart View should you come across them.

First, in versions prior to 11.1.2.5, using Member Names in some places of the HsGetValue arguments and using Aliases in other places can cause a return of #Value. This was fixed in 11.1.2.5.  For prior versions, I would recommend standardizing all HsGetValue statements on member name to prevent this. I would also recommended to use either the , or the & argument separator consistently throughout all tabs throughout the whole workbook.

Another issue that causes #Invalid and #Value values is when the same intersection or data point is pulled using different argument order. That is, the HsGetValue string that is used to pull the same intersection within the same workbook has the dimensions listed in a different order. For this reason, the exact same dimension argument order should always be used throughout an entire workbook when constructing a set HsGetValue cells.

For example, the below HsGetValue strings pull the same cells for arguments, they simply do so in a different order. This causes the cell edited most recently to refresh fine, and the other to display #Invalid.

How to fix HsGetValue Issues in Smart View

(click to enlarge)

If a repeated intersection is used in a formula, the cell will display #Value in the case when it is not the most recently edited iteration of the intersection.

For example =(HsGetValue($A$21,$B$13,$C$13,$A$1,$A$2,$A$3,$B$1,J$1)/1000)

If you’re having issues with HsGetValue or have any questions, eCapital is here with answers!

Have more questions? We can help:

Jon Harvey

Jon has been a part of the Oracle team at eCapital Advisors since 2009 and is passionate about educating and teaching clients and team members how to apply the technology to drive business value within organizations. An Oracle Certified Expert in both Hyperion Planning and Essbase, Jon oversees the technical aspects of EPM project delivery and managed services. In addition, he’s responsible for ensuring the continued growth of the practice’s technical capabilities and serves as the primary interface between eCapital and the Oracle user community.

Comments are closed.