We're having an intermittent issue (that is becoming quite frustrating) when refreshing HsGetValue formulas.
When doing either a single sheet refresh or Refresh All, sometimes the result in the cell(s) comes back with just 'error'. All users seem to experience this at some time or other, and both for regular users as well as admin users. The odd thing though is that by selecting that cell and hitting F2 (to edit cell) followed by enter often clears the issue and it switches to displaying the values as it should.
We're running essbase 220.127.116.11 and Hyperion Smartview Fusion edition 18.104.22.168.500 (Build 008), and local machines are either WinXP/Office07, or Win7/Office10.
Any ideas would be greatly welcome?
I'd be curious to find if there is a solution to this problem. We are on HFM and SV 22.214.171.124 and have the same problem (Win7/Office 2010 machines). It's not consistent when it happens and if you hit refresh a couple more times (or use the F2 method) all of the cells will eventually populate. Additionally, it never puts anything into the SV error log to help with figuring out the problem.
Using the HSGetValue formula is very inefficient as it makes many more calls to the database then a simple retrieve. Therefore it is likely that you're running out of ports during the refresh phase.
I would recommend avoiding the use of the formula as I always found that although it looks pretty and seems straightforward, it caused more problems then it solved.
When you say "simple retrieve" are you referring to an ad hoc analysis? Or is there some other function that can be use to retrieve data from the cube? I'm hoping it's the latter, in which case can you please elaborate?
Relying only on ad hoc is not an option for us; we have too many canned reports that are reliant upon pulling data into a pre-formatted template. As odd as it may sound, we actually need (and expect) Hyperion to work.
We had one similar issue where HSGetValue was having two times Account# as it was referring to another column to get the value for accounts. Even in that case, when we hit F2 the value got populated.
Question: Are there any dynamic link eg. Account# & A12 and A12 is a link to an external sheet?
I am having the same problem when using HSGETVALUE in HFM. The error pops up and when you do the refresh again sometimes the error goes away and data is displayed but some times you have to refresh several times and the "error" stays there.
I agree with Steve here.
Use HSGetValue if you have a few cells that need data with lots of formatting around them. If you have an entire grid of HSGetValue cells then you're using this technology in a way it wasn't intended and you should be using free form mode.
Addressing the issue here by turning off autocalc is not the answer and really only moves the problem.
Your spreadsheet should favor rows over columns. Minimize the use of columns and maximize your use of rows. This will help the effieincy of the sheet and might help the hsgetvalues.
If you refresh the sheet several times and the error is still there, or there intermittantly, then you're asking Excel to do something it can't and need to review the design of the sheet.
If you simply must have many HSGetValues cells in perhaps a highly formatted report, then consider spreading those retrieval cells across several background sheets that the top sheet then refers to. Create a simple VBA to refresh the background sheets one at a time. This spreads out the function work for excel and gives smartview time to address all requests.