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.
(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!
Free eBook Download
In the eBook “Having a Conversation with Data”, learn what the current BI infrastructure has been and associated challenges with the traditional approach. How important the user experience is in order to best maximize data’s value (think visualizations!!) to your organization and how to gain a competitive advantage with modern analytics platforms.
