Untitled Document
table {
border: thin solid;
}
Most Smart View users probably appreciate that they can use just one add-in
to access data from
the different sources they might work with, like Oracle Essbase, Oracle Hyperion Planning, Oracle Hyperion Financial Management and others. But not all of them are aware of
the options
to integrate data analyses not only in Excel, but also in MS Word or Power Point. While in
the past, copying and pasting single numbers or tables from a recent analysis in Excel made
the pasted content a static snapshot, copying so called Data Points now creates dynamic, updateable references
to the data source. It also provides additional nice features, which can make life easier and less stressful for Smart View users.
So,
how does this option work: after building an ad-hoc analysis with Smart View as usual in an Excel worksheet, any area including data cells/numbers from
the database can be highlighted in order
to copy data points - even single data cells only.
TIP
It is not necessary
to highlight and copy
the row or column descriptions
Next from
the Smart View ribbon select Copy Data Point.
Then transfer
to the Word or Power Point document into which
the selected content should be copied. Note that in these Office programs you will find a menu item Smart View;from it select
the Paste Data Point icon.
The copied details from
the Excel report will be pasted, but showing #NEED_REFRESH in
the data cells instead of
the original numbers.
=After clicking
the Refresh icon on
the Smart View menu
the data will be retrieved and displayed. (Maybe at that moment a login window pops up and you need
to provide your credentials.)
It works in
the same way if you just copy one single number without any row or column descriptions, for example in order
to incorporate it into a continuous text:
Before refresh:
After refresh:
From now on for any subsequent updates of
the data shown in your documents you only need
to refresh data by clicking
the Refresh button on
the Smart View menu, without copying and pasting
the context or content again.
As you might realize, trying out this feature on your own, there won’t be any Point of View shown in
the Office document. Also you have seen in
the example, where only a single data cell was copied, that there aren’t any member names or row/column descriptions copied, which are usually required in an ad-hoc report in order
to exactly define where data comes from or
how data is queried from
the source. Well, these definitions are not visible, but they are transferred
to the Word or Power Point document as well. They are stored in
the background for each individual data cell copied and can be made visible by double-clicking
the data cell as shown in
the following
screen shot (but which is taken from another context).
So for each cell/number
the complete connection information is stored along with
the exact member/cell intersection from
the database. And that’s not all: you have
the chance now
to exchange
the members originally selected in
the Point of View (POV) in
the Excel report. Remember, at that time we had
the following selection:
By selecting
the Manage POV option from
the Smart View meny in Word or Power Point…
…
the following POV Manager – Queries window opens:
You can now
change your selection for each dimension from
the original POV by either double-clicking
the dimension member in
the lower right box under POV: or by selecting
the Member Selector icon on
the top right hand side of
the window. After confirming your changes you need
to refresh your document again. Be aware, that this will update all (!) numbers taken from one and
the same original Excel sheet, even if they appear in different locations in your Office document, reflecting your recent changes in
the POV.
TIP
Build your original report already in a way that dimensions you might want
to change from within Word or Power Point are placed in
the POV.
And there is another really nice feature I wouldn’t like
to miss mentioning: Using Dynamic Data Points in
the way described above, you will never miss or need
to search again for your original Excel sheet from which values were taken and copied as data points into an Office document. Because from even only one single data cell Smart View is able
to recreate
the entire original report content with just a few clicks:
Select one of
the numbers from within your Word or Power Point document by double-clicking.
Then select
the Visualize in Excel option from
the Smart View menu.
Excel will open and Smart View will rebuild
the entire original report, including POV settings, and retrieve all data from
the most recent actual state of
the database. (It might be necessary
to provide your credentials before data is displayed.)
However, in order
to make this work, an active online connection
to your databases on
the server is necessary and at least read access
to the retrieved data. But apart from this, your newly built Excel report is fully functional for ad-hoc analysis and can be used in
the common way for drilling, pivoting and all
the other known functions and features.
So far about embedding Dynamic Data Points into Office documents and linking them back into Excel worksheets. You can apply this in
the described way with ad-hoc analyses directly on Essbase databases or using Hyperion Planning and Hyperion Financial Management ad-hoc web forms.
If you are also interested in other new features and smart enhancements in Essbase or Hyperion Planning stay tuned for coming articles or check our training courses and web presentations.
You can find general information about offerings for
the Essbase and Planning curriculum or other Oracle-Hyperion products here (please make sure
to select your country/region at
the top of this page) or in
the OU Learning paths section , where Planning, Essbase and other Hyperion products can be found under
the Fusion Middleware heading (again, please select
the right country/region). Or drop me a note directly:
[email protected] .
About
the Author:
Bernhard Kinkel started working for Hyperion Solutions as a Presales Consultant and Consultant in 1998 and moved
to Hyperion Education Services in 1999. He joined Oracle University in 2007 where he is a Principal Education Consultant. Based on these many years of working with Hyperion products he has detailed product knowledge across several versions. He delivers both classroom and live virtual courses. His areas of expertise are Oracle/Hyperion Essbase, Oracle Hyperion Planning and Hyperion Web Analysis.
