1 2 3 Previous Next 35 Replies Latest reply: Sep 24, 2013 3:07 PM by mjw Go to original post RSS
      • 15. Re: Excel automatically changes formula to smart view
        Matt Milella-Oracle
        Thats the thing you should be able to have 2 machines with different install paths sharing the same workbook; we have tested this and it works. With that people are seeing issues so we want to try to get to the bottom of it.

        Can you tell me what functions you are using and what the data source is (HFM, Essbase, etc.)
        • 16. Re: Excel automatically changes formula to smart view
          user9954238
          @Matt:
          I'm not sure I understood what you wrote correctly because you first write that you tested it with diffrent install paths on different machines and it worked, but then you say that users are seeing issues: Did you get it to work?

          I would really be interested in how this was achieved because I think the bevaviour standard Excel behaviour.
          Test: Try saving a HsGetValue-file on the drive where SmartView is installed, close Excel, move the saved file to a network drive and you should have a wrong addin-path in front of each formula, even on the same machine (as in point 2 of my earlier post).

          The only way I can imagine Sabrina's environment to work is to have the AddIn installed on ALL local machines AND the Citrix server in the same location (ideally the default path on all machine's c:\-drive, I'd imagine, because not everybody will have named the network drives equally).

          If that can't be ensured, someone will always have to live with having to replace the whole '\\.xls'!-thing with nothing for all sheets.

          But I'd be glad to hear of a workaround,

          Fred
          • 17. Re: Excel automatically changes formula to smart view
            616578
            I am not able to replicate this behavior. I tried the same file on machines with Smart View installed on different drives. I tried the same file on various drive letters and on a network drive.

            It is true that Excel stores the path with any XLA-based functions like HsGetValue. But I am observing that if Excel is set to "Disable Automatic Update of Workbook Links" under Excel Settings|Trust Center, the inproper path is ignored and the function works properly so long as the HsTBar.xla file is accessible by Excel.

            Unfortunately, I do not have access to a Citrix environment. We need to be able to replicate the behavior before we can address the issue any further.
            • 18. Re: Excel automatically changes formula to smart view
              SabrinaG
              Mike,
              Disabling Automatic Update of Workbook Links did not help, unfortunately. I open the file, that path is still inserted into each formula, and the formulas are unusable until that path is removed.

              Thanks anyway,

              Sabrina
              • 19. Re: Excel automatically changes formula to smart view
                SabrinaG
                Thats the thing you should be able to have 2 machines with different install paths sharing the same workbook; we have tested this and it works. With that people are seeing issues so we want to try to get to the bottom of it.
                Can you tell me what functions you are using and what the data source is (HFM, Essbase, etc.)>

                Matt, I'm using the HsGetValue function with Essbase. We are on version 11.1.1.3.

                Thanks,

                Sabrina
                • 20. Re: Excel automatically changes formula to smart view
                  769815
                  I have the same issue. I have Smartview installed on D:\ drive. If I open the file from D-drive it works fine, if I copy to my desktop on C:\ drive it can't find hsTbar.xla and I get "='C:\Hyperion\SmartView\Bin\HsTbar.xla'!hsgetvalue" in front of the all the Hyperion functions.

                  -Mike
                  • 21. Re: Excel automatically changes formula to smart view
                    769815
                    Matt Milella wrote:
                    OK last thing for you to try.... In the workbook in question (the one without the path in the formulas) add a VBA module and add these declarations:

                    Declare Function HsGetValue Lib "HsAddin" (ParamArray MemberList() As Variant) As Variant
                    Declare Function HsSetValue Lib "HsAddin" (ByVal Value As Variant, ParamArray MemberList() As Variant) As Variant
                    Declare Function HsGetText Lib "HsAddin" (ParamArray MemberList() As Variant) As Variant
                    Declare Function HsLabel Lib "HsAddin" (ParamArray MemberList() As Variant) As Variant
                    Declare Function HsDescription Lib "HsAddin" (ParamArray MemberList() As Variant) As Variant
                    Declare Function HsCurrency Lib "HsAddin" (ParamArray MemberList() As Variant) As Variant
                    Declare Function HsSetText Lib "HsAddin" (ByVal text As Variant, ParamArray MemberList() As Variant) As Variant

                    These are the cell function declarations that SV is looking for. I am not sure this will work, it is a long shot, but I am thinking that if it can find these declarations locally then it will not try to path to the xla file where they are stored...

                    Remember it is a long shot...
                    This does work, you need to put "Public" in front of "Declare Function", like "Public Declare Function". Also unload the Hstbar.xla addin and move it our of the smartview bin folder, then go in Excel and only load the COM addin. The problem with the .xla is that Excel treats the UDFs as normal file links so when you move the file to a different drive it breaks the links. Using only the COM addin fixes the problem. The COM addin has both Excel 2003 and 2007 toolbars so there is no reason to use the .xla at all.

                    Just tested moving an updated file from C:\ to D:\, no broken link issues.

                    The other benefit is that UDFs in .xla run far slower than those in COM addins, so templates should work much better using the COM addin only.

                    -Mike
                    • 22. Re: Excel automatically changes formula to smart view
                      SabrinaG
                      What is the COM add-in? I don't see any reference to that in my add-ins list.

                      Sabrina
                      • 23. Re: Excel automatically changes formula to smart view
                        769815
                        The COM addin file name is HsAddin.dll, it should be in your Smartview\bin folder.
                        • 24. Re: Excel automatically changes formula to smart view
                          720770
                          Hi,

                          we're facing the same problem using Hyperion Financial Management and SmartView. Can you tell me if the proposed solution with the COM-addin and the Declare function VBA module worked for you? Or do you have another solution?

                          Best regards

                          Thomas
                          • 25. Re: Excel automatically changes formula to smart view
                            user9952742
                            Hi,

                            I tried to add the hsaddin.dll but it tells me that hsadd.dll is not a valid add-in... does anyone ever encountered this issue?
                            • 26. Re: Excel automatically changes formula to smart view
                              632833
                              We had convinced users to live with the Find and Replace method, but now it seems with Excel 2007 it places a tilda in the filepath name because it is too long. So a formula looks like this:

                              ='C:\Progra~1\HyperionSmartView\Bin\HsTbar.xla'!HsGetValue("databasename","Entity#"&$B$4&";Account#"&H$6&";Product#"&$F24&";Data_Type#"&$H$5&";Currency#"&$D$3&";Period#"&$B$3&";Scenario#"&$C24&";Version#"&$G$2&";View#"&$D$2&";Year#"&$B24&"")

                              Find and Replace does not work now. Currently, our suggested workaround around the workaround, is to change it in one cell and copy to others (but you have to be super careful to make sure all your cell references stay correct). Not a great solution.

                              Any other ideas or suggestions to fix this? None of the recommendations in this thread have helped. We are on 11.1.1.3 and the issue happens with Office 2003 and 2007, and both on local and Citrix Excel.
                              • 27. Re: Excel automatically changes formula to smart view
                                Matt Milella-Oracle
                                Check out Mike's blog on this topic he found a creative way to locally address all functions and eliminate this issue. I have tried this and it does work.

                                http://essbaselabs.blogspot.com/2011/03/broken-links-workaround-for-smart-view.html
                                • 29. Re: Excel automatically changes formula to smart view
                                  632833
                                  Yes, thanks Matt and Mike!