9 Replies Latest reply on Apr 25, 2012 4:09 PM by Robb Salzmann

    SmartView HsGetValue returns 'error' in cell

      Hello experts.

      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 and Hyperion Smartview Fusion edition (Build 008), and local machines are either WinXP/Office07, or Win7/Office10.

      Any ideas would be greatly welcome?

        • 1. Re: SmartView HsGetValue returns 'error' in cell
          I'd be curious to find if there is a solution to this problem. We are on HFM and SV 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.
          • 2. Re: SmartView HsGetValue returns 'error' in cell
            We're having the same issue James is describing. No one knows of a solution?
            • 3. Re: SmartView HsGetValue returns 'error' in cell
              I am not sure what causes this issue, but we have found that if you change the calculation option in Excel to Manual calculation, once you retrieve and hit F9, you will see your data.
              • 4. Re: SmartView HsGetValue returns 'error' in cell
                Steve Fitchett
                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.

                • 5. Re: SmartView HsGetValue returns 'error' in cell
                  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.
                  • 6. Re: SmartView HsGetValue returns 'error' in cell
                    Steve Fitchett
                    Call it Free Form reporting if you like.

                    If you have the Excel Formatting option checked your canned reports will be ok won't they? What exactly is stopping you from doing that?
                    As odd as it may sound, we actually need (and expect) Hyperion to work.
                    Well it can be argued it's caused by restrictions in the OS but I guess it's open to argument. Which I don't intend to do!
                    • 7. Re: SmartView HsGetValue returns 'error' in cell
                      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?

                      • 8. Re: SmartView HsGetValue returns 'error' in cell
                        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.
                        • 9. Re: SmartView HsGetValue returns 'error' in cell
                          Robb Salzmann
                          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.

                          Robb Salzmann