Smart View for Excel 365 on the Mac or in a browser is less widely used (and less fully featured) than for Excel on Windows, but it is slowly creeping closer. As someone who works primarily on a Mac but continues to require a Windows virtual machine almost entirely because of Smart View support, it’s great to see new features coming to that version.
The new features below work in the currently supported connection types and are demonstrated with Planning using Office 365 in Chrome. There is no support for Essbase connections (yet?), unfortunately.
Using Smart View in these environments has some special instructions and prerequisites, including the deployment of a “manifest” file which controls the options available within the Add-In.
For full details see this link. A key point to note is that new options may not be selected by default when creating the manifest file; for example, the “Build Function” option for the Function Builder described below:
Improved Member Selection Dialog
The first new feature is an improved Member Selection dialog. In this blog I’m using Smart View in the browser (Chrome, specifically) so I have followed the required setup steps linked above already. I have also logged in to my EPM Cloud application – Planning, in this case - in a separate tab in Chrome. First click on the Home button, which brings up a dialog:
In this example, I’ll select the Plan1 Plan Type of the demo Vision application. After selecting the Plan Type, a further dialog asks for an option for the application. Here I’ll select Ad hoc analysis.
Once the worksheet is connecting, the Member Selection button the Smart View ribbon displays the improved dialog:
The dialog allows users to select individual members from a dimension. It also allows for the selection based on the relationship to a chosen member, using various standard hierarchical relationship functions:
- Siblings (including “left” and “right” siblings)
- Level 0 Descendants
All of the above besides Level 0 Descendants also permit inclusion or exclusion of the selected member itself (e.g. “descendants including the selected member” or “descendants excluding the selected member”). In addition to these relationship functions, the Member Selector also has options to select members according to:
- UDA (User-Defined Attribute)
The Member Selector also has a very usable search function. The search will find a string anywhere in a member name or alias, without having to use wildcard, but “*” (for one or more characters) and “?” (for exactly one character) are also available as wildcards:
The next new feature is a Function Builder, which provides a friendly interface to build HsGetValue, HsSetValue and HsAlias functions (themselves, useful to build highly formatted reports and forms that don’t necessarily follow ‘standard’ Smart View ad hoc layout or formatting. To use it, select the Smart View ribbon, and as before, click on the Home option and select the Plan1 Plan Type.
This time, choose the option Set as Private Connection for Functions, name the private connection – in this case, “VisionPlan1” and then click OK. Now that I have a named connection, I can go back to the Smart View ribbon, and choose the Build Function option under General.
The Function Builder dialog appears, with options to choose between the HsGetValue, HsSetValue and HsAlias functions. I’ll select HsGetValue.
The dialog now changes to show a series of fields – one for the Connection (where I can use the ‘VisionPlan1’ name created earlier), and one for the member list, comprised of one member from each dimension in the Plan Type. These can be manually entered in the format DimensionName#MemberName, or, by clicking the folder / magnifying glass icon immediately to the right of the field, via the improved member selection dialog:
Once the connection and all members have been entered (or, more easily, selected with the Member Selection dialog), clicking the Apply button inserts the formula into the currently selected cell of the current worksheet:
As normal, when inserting a new formula, the value shows #NeedsRefresh. Clicking the Refresh button on the Smart View ribbon retrieves the value from Planning.
The last new feature for this post is the HsAlias function. The HsAlias function returns the alias of a particular member taken from a specific alias table. This can be useful (as with HsGetValue and HsGetValue) for creating custom format reports and layouts that don’t necessarily follow standard Smart View ad hoc layouts, or if producing a report that mixes aliases from different alias tables.
The HsAlias function can be entered manually, or accessed via the Function Builder described above:
In this example, I’m returning the alias for the Period dimension member Dec but returned from the “English” alias table (I had to add this alias – Periods don’t have aliases in the Vision application!):
And sure enough, after the required refresh, the alias is returned into my worksheet:
There is still some way to go before the Mac and Browser version of Smart View could be said to enjoy parity with the Windows version. But it’s encouraging to see that development continues, and with each set of new features, using Smart View outside Windows become a more useful, more realistic proposition.
Many users will still need Windows-only features, and I’m not going to give up the Windows Virtual Machine I run on my Mac in the near future – but I could certainly be tempted to open Chrome instead of spinning up VMWare Fusion when the features I need are all there.