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:

Eric Milbrandt

Eric Milbrandt has been in Finance and Technology roles for over a decade, the latter half has been with eCapital since 2013 where he serves as a project lead and lead developer. Eric is Oracle certified in Hyperion Planning 11 as well as Essbase 11 and has experience working with resources in all corners of the globe through all phases of the implementation process. Eric has also delivered custom FDQM and FDMEE solutions and is passionate about creating dynamic low-maintenance solutions to achieve the most value for his clients.

Comments are closed.