Smartview Magic and an Inglorious Hack (well kinda…see notes at the bottom)

G’day All,

Those who know me well know I’m not exactly prone to fits of exaggeration nor exuberance when it comes to pieces of new functionality that interest me. I definitely don’t go running around grabbing clients and workmates and saying ‘come and look at this! Isn’t it awesome!’. I also definitely never would spend a hungover Saturday writing it up and sending it out.

So with that all out of the way. This is almost magical. Certainly awesome at the very least.

History: A Smartview Curmudgeon
Backtracking a little bit first. I was one of the holdouts with Smartview. Compared to the beauty and simplicity that was the excel add-in (particularly once you tweaked it a little), the bugs and functionality issues with early versions of Smartview (having to refresh before submitting??) held me off for ages. Even after changing over, I really wondered about some of the functionality / design choices that Oracle had made.

One of the ‘functions’ that always threw me was the integration of Smartview into Word. Certainly in the first few versions, this function had little to write home about – embedding Hyperion Financial Reports into word documents ‘kinda’ worked, but had issues: occasionally losing connections, and not being able to perform basic functions like resizing / cropping the embedded report without reconnecting all the sheets. As such, I’ve not seen an awful lot of it in the wild.

That said, there is definitely a user desire to bring some Hyperion data into Word. Month end reports are generally a combination of upper level datasets and textual analytics, and generally a lot of time and effort goes into ensure the data is verifiable and consistent – all of which can be wasted effort if the underlying data sets aren’t sourced correctly. So – the ability to integrate Smartview functionality into both Excel and Word is clearly desired. Let’s have a look at some of the options.

Reporting Reporting Everywhere, Reporting Reporting I Don’t Care

Really simply, this is what normally happens.

• You have a requirement to embed some reports in a end of month Word reporting pack
• You mock up the report exactly to spec, utilising sub variables where appropriate
• You walk the user through embedding the report, all goes swimmingly for 3 months
• The user changes their mind slightly, wants to add a new metric
• You then spend the next 3 months fixing formatting issues in a Word document, because it’s now a slightly different size
• You cry yourself to sleep at night due to the constant abuse of Word Styles

Right, so lets look at the other option.

Smart Queries and Dynamic Datapoints

Right, so we’ve got to get the users to do it themselves. Let’s walk through the process of building a query in excel and mapping it across.

First, build an adhoc retrieve in excel for the dataset. I’m not using a certain Michigan based soda dealership – mostly because I want the report to be a bit more complex. None the less, a nice simple report:

You’ll note the Year and Month in the POV, as well as a nice selection of columns.

From there, you want to select the components of the ad hoc retrieve that you wish to copy to Word.

As can be seen, I’m only selecting the first few columns – nevertheless this is going to copy ALL of the associated metadata for the table – a fact that has very interesting repercussions later!

Okay – then open your Word document->Smartview and click the Paste button

Ta-da! Excel ad hoc retrieve in Microsoft Word!

Okay, so the format is a little ugly…but after some basic formatting.

Awesome. Okay – now for those of you who were originally HFM users – the ‘#NEED_REFRESH’ will be of no surprise. Basically it’s rebuilt the query as discrete datapoints, everything is now HSGETVALUE with the full intersection. Hovering over the data item we can see further information as to what it’s doing – each cell has a direct link back, so you can change formatting, add columns, rows, headers etc and the individual queries will keep working.

Refreshing the query will bring back all the data.

This even extends to the ability to copy and paste a single datapoint directly into a sentence and have it dynamically refresh as needed:

 

Do you believe in Magic
So – I promised some magic.

Earlier on I noted that when we copy the cells across the we are actually copying ALL of the metadata of the table, rather than just the discrete members. The interesting repercussion of that is that if you select any of the data cells and click on the Visualize in Excel button – it will go back and dynamically rebuild the original query in its entirety!

I can just imagine the strokey beard meeting where that was decided!

Anyway, as a piece of functionality it’s staggering. Basically you can build a full Management P&L report with detail in Excel – embed only the important lines in the Word document – but each of those lines contain the metadata to rebuild the entire report from scratch for further analytical queries! (And yes – even the inline text fields can do it).

That’s all Fine and Dandy….What About Next Month?

One of the nice features of Smartview in Word is the POV Manager. From here we can update the POV of the queries and it will refresh the data – this is reverse mapped to the Analyse in Excel function as well, so if you update to the next month in the POV Manager it updates in the full report. The POV manager is very simple, only allowing you to select a new member, and not giving you access to functions like SubVariables, user variables or even UDAs.

So what’s the Catch

Well – after I’d sent this out to a few people I had some feedback along the lines of

• That’s awesome
• Unfortunately you can’t mass update the POV – every discrete report has it’s own setting
• Therefore at best, it’s rather frustrating to update for big report packs
• The risk of ‘stuffing this up’ makes this not really a viable option
• Still – that analyse thing is pretty awesome. Imagine that strokey beard meeting where they decided to do that!

Right – so we need a workaround.

We Can’t Stop Here…this is Hack Country

So – back to Excel. One of the ‘new features’ in Smartview (v11.1.1.3) which offered a benefit over the excel add-in was the ability to use a Subvariable in an adhoc query – basically you could write a sub variable name instead of a member name (prefixing it with an “&”) and it would re-render to be the variable value.

The big issue with this was that the query would be updated at runtime, and be replaced by the member as a text field. If you went back and later updated the substitution variable through EAS, it wouldn’t then go and update the smartview retrieve (thus, basically defeating the entire purpose of the function in the first place). Fortunately, in a later version (v11.1.2.1?), Oracle added the HSGetVariable member function. This is a very useful little function, as it provides the ability to add an on demand sub variable to an ad hoc query, but have it persist while retrieving.

So – to use it within a query:

Select the member that you wish to turn into a variable, goto the Connection Panel and select Build Function

From there, you want to select the HSGetVariable Function, and then add the Variable.

The yellow highlighted box will actually pull all the members into a list for you to select from.

Click Validate to test the variable, and then Okay to return to Excel. Returning to excel gets you the very familiar ‘#NEED_REFRESH’ text – but clicking refresh brings back the month correctly (and all the data) but you’ll see that the member has not reverted to ‘Apr’ but is instead still showing the HSGetVariable Function!

Now updating the sub variable will automatically update this member and therefore the retrieve. Awesome. Let’s use that function to get around the problem! We’ll define a view that uses the variable, copy that across to Word and Bob’s your mother’s brother!

And…..it doesn’t work

So, back to the query, Smartview tab->Copy, Open Word, Smartview tab -> Paste! And…..it doesn’t work.

To see why, let’s have a look at the associated SmartTag in Word (alt-f9 or right click and ‘view field codes’)

When it brings the member across, it redefines every member in the query as a discrete HsGetValue. This is great in one sense, because it means that each cell is fully rendered every time. You don’t have to worry about keeping all the headers etc in place, because the cell knows what value it is querying. I’ll break it down in a bit more detail below:

This does give a bit of a clue as to the work around. There is nothing stopping us from nesting into the period query the function that we used before to query the sub variable. As such, if we replace period reference with a subvariable reference, it should all work.

And…..it doesn’t work Take 2

And it does!

Or at least…it did. Until 11.1.2.5.

So yes. I’ve just spent about 10 hours trying every combination of string possible to man trying to replicate my testing from 6 months ago. But it turns out that they’ve changed the way it works, so now it…er…doesn’t work. I’ve replicated it perfectly in 11.1.2.2, but as soon as I move to a later version of Smartview.

You can still reference HsGetVariable dynamically within Excel – but not within Word.

Since I’d written all this down, I’ve decided to post what I’ve got anyways and come back to it once I’ve found another hack for it. If anyone had any ideas – please leave me a note at the bottom!

Cheers

Pete