For users with SmartView installed locally, this works great.
But, we have a new twist to this issue. Some users access SmartView via Citrix. They are opening a shared workbook on Citrix, the macro works, but instead of just deleting the filepath, it inserts the name of the file before =HsGetValue.
The Refresh seems to work, we get numbers, but my concern is the filename could negatively impact the formula. Any suggestions on how to not have the filename inserted?
Thanks for any suggestions!
We get this problem when SmartView has been disabled.
1. Open up help - About Excel check for any disabled items.
2. Open up Tools - Add-Ins and make sure that SmartView has a check.
This usually does the trick with our uses. We don't know why this is happening since it happens to random users and we don't think it has anything to do with patches/upgrades of Excel. Anyone care to shed some light over this??
I haven't tried the Macro solution mentioned here, but we are also using a Citrix environment and get this issue. There is another way to solve it without the find and replace method. Follow these steps:
a. Select Edit, Links from the Excel menu bar in Excel 2003, or Data, Edit Links in Excel 2007/2010.
b. Click on “hstbar.xla” in the list of sources, and click “change source”.
c. Navigate to where hstbar.xla is located on the Citrix server (or whatever you're using). You'll want to talk to the Citrix admin about this. In our setup, the add-in is installed here: “C:\Hyperion\SmartView\Bin\Hstbar.xla”, but when a user connects to Citrix apparently something occurs that makes C: appear as M: and you can't navigate to it. In order to solve our issue, we had to manually type in the path above.
d. Click OK. When you have done this once, in the future you should be able to just select the down arrow in the filename box and select the path you typed in previously.
This will update the links/formulas in the entire workbook and you should be off to the races. However, this is annoying to do with every file. After reading through this thread I'm tempted to try the macro solution and see if it works for our Citrix environment.
We have resolved the problem by using a VBA solution to write, refresh and remove HSGetValue formulas using a defined-format "read" worksheet.
Because the formulas are temporary and are removed after refreshing, this allows the report to be portable and reviewable between users regardless of where Smart View is installed, or even if it isn't installed on a recipients local machine. It eliminates the risk of senior managers seeing "#NEEDS REFESH" where they were expecting to see numbers.
The layout prevents the completion of Ad Hoc Enquiry operations, so users' row suppression settings cannot compromise the integrity of reports using the solution. It contains built-in validations to ensure all 12 HFM dimensions are specified in the POV, and the client has the option of specifying whether a dimension is a column or row dimension.
The final published format report table exists on a separate output worksheet which references the read sheet with linking formulas.
No VBA knowledge is required for report editors to use the solution. They just unlock the workbook, edit the POV, tweak the output table format and re-run the refresh.
Results are only updated when the Refresh macro is run. This ensures that point-in-time snapshots of results are retained, and provides greater scope for document version control.
Seems to be really popular with the Group Finance senior user base, who are actively promoting its use to other business units.
Edited by: user612550 (John Hawkins) on Jul 18, 2011 8:18 PM
The solution below works for us....
- No Citrix (Oracle Cloud)
- Smart View 126.96.36.199.310
- Excel 2010
User opens a file and has the local HsTbar.xla path imbedded at the front of the Smart View Function: "='C:\Oracle\SmartView\Bin\HsTbar.xla'!HsGetValue($B$8,"Scenario#"&$....). We also see this if a user opens a file directly from SharePoint, but the imbedded path will have the SharePoint server URL in it. As you know, the Smart View function will not work in this state.
- In Excel, with the problem file open, go to Data > Edit Links.
- In the Edit Links dialog box, click the Change Source button.
- Browse to where Smart View is installed locally - for us it's C:\Oracle\SmartView\Bin (NOTE: you still need to follow these steps, even if the imbedded path is the correct path for the HsTbar.xla).
- Select the HsTbar.xla file and Click OK.
- Click the Close button.
At this point you should see the imbedded path is gone, allowing you to Refresh as normal. Also, you should be able to Save the file and re-open it without the imbedded path from returning.