Smart View

Introduction: Using Smart View, you can view, import, manipulate, distribute, and share data from these data Sources in Microsoft Excel, Word, Outlook, and PowerPoint.

Smart View Components: The basic components of Smart View, from which you connect to your data source and access Smart View functionality, are ribbons and the Smart View Panel.

Ribbons You access Smart View functionality in Office applications through ribbon commands. The Smart View ribbon, which contains commands for common Smart View operations and for Reporting and Analysis operations, is always present.

Smart View Panel

From the Smart View Panel, you can manage data source connections, access data and task lists, Create reports. The Smart View Panel, opened from the Smart View ribbon, is displayed by default on the right side of the Microsoft Office application. You can move, resize, or close the Smart View Panel from the down arrow in the title bar.

The Smart View Panel contains the following panes: l Home: A panel that displays links to Shared Connections and Private Connections as well As a list of recently used items - ad hoc grids, forms, and tasks - that you can click to establish a connection. l Shared Connections: A drop-down menu of available connections from Oracle Hyperion Shared Services and a tree view of the contents of the currently selected connection. l Private Connections: A drop-down menu of available connections saved on the local

Computer and a tree view of the contents of the currently selected connection. You can also Enter a URL to connect directly to a data source here. l Task Lists: A tree list of tasks from which you can manage your tasks. This pane opens only when you select a task list from Shared Connections or Private Connections. l Action Panel: A list of operations available based on the selection in the shared connection. Private connection, or task list tree list.

Connections: You connect to data sources, manage your connections, and open grids, forms, and task lists all From the Smart View Panel. Depending on how the administrator configured Smart View, you may or may not be required To enter your user name and password as you change data providers and Office applications

Shared and Private Connections

You connect to data sources through shared or private connections. l Shared Connections Shared connections are stored in a central location and are available to multiple users Through the Smart View Panel. You cannot add, edit, or rename shared connections, but You can save them as private connections, which you can edit and rename. l Private Connections Private connections are those that you create by saving a shared connection to your local Computer or by entering a URL to a provider that is not configured for shared connections. When you create a private connection, it becomes the active connection.

Connecting to Private Connections

Financial Management: http(s)://servername:port/hfmofficeprovider/ hfmofficeprovider.aspx Hyperion Enterprise: http://servername:port/heofficeprovider/ heofficeprovider.aspx Planning: http(s)://servername:port/Hyperion Planning/Smart View Essbase: http(s)://servername:port/aps/Smart View Reporting and Analysis: http(s)://servername:port/framework/browse/ Listxml Financial Close Management: http://servername:port/fcc/servlets/ Smart view/fcmsvservlet Oracle Hyperion Strategic Finance: http://servername:port/StrategicPlanning/ Smart View

Dimensions and Members

Dimensions are data categories used to organize business data for retrieval and preservation of Values. Dimensions usually contain hierarchies of related members grouped within them. For Example, a Year dimension often includes members for each time period, such as quarters and Months. You can select members for the grid from the Member Selection dialog box available from the Data source ribbon, from the POV toolbar, or by entering the member name using free-form Mode.

Member Selector

You select members for a variety of purposes within Smart View: ad hoc grids, functions, the

POV Manager, and for taking Planning forms offline. The Member Selection dialog boxes in These locations may vary slightly from one another, and not all options are always available. You Can select members for one dimension at a time.

POV The POV is the default starting point for dimensions in a data source connection. From the POV Toolbar, you can select members and filters for the dimensions that you want to include in the Grid and move members to and from the grid. Each connection is associated with only one POV. However, the same connection to different Worksheets within a workbook may have different POVs.

Entering member in free form mode If you are familiar with the dimensions and members of your database, you can enter their names Directly into cells using free-form mode.

After connecting to a data source, you can enter member names as follows: l By entering a member name in a blank cell l By replacing a member name in a cell with a different member from the same dimension

Displaying both Member and Alias To display both member names and aliases: 1 From the Smart View ribbon, select Options, and then Member Options in the left panel. 2 Under General, for Member Name Display, select Member Name and Alias.

Copy, Pasting and Deleting POV Manager

To copy and paste a POV: 1 From the Smart View ribbon, select Function, and then Manage POV. 2 In the left window of POV Manager, expand Active, and then select the application connection that you Want to copy. 3 From the POV Manager Toolbar, click Member Selector, and then select members for the POV. 4 Save the workbook. 5 From the POV Manager Toolbar, click Copy. 6 In the left window of the POV Manager, expand Saved to select the workbook and worksheet (which Must be blank and unconnected) that you want to paste the POV into. 7 Click Paste. 8 Refresh the worksheet containing the copied POV.

For Delete To delete a POV that is saved in a workbook: 1 From the Smart View ribbon, select Function, and then Manage POV. 2 Expand the POV list. 3 From the POV drop-down list, select the worksheet that contains the POV that you want to delete. 4 Select the POV that you want to delete. 5 Click Delete. 6 Click Close. 7 To refresh the worksheet, select Refresh.

Retrieve data: In Excel, you can retrieve and refresh data for the current worksheet or for all worksheets in the

Workbook

Submit Data: You can update the data (any type) in the data source by submitting changed data from ad hoc Grids and forms.

Calculating Data

After you submit new or changed data, you need to calculate the data in the database to reflect Your changes. Your options for calculating data depend on your data source. To calculate data, You must have security access rights to the data.

To calculate data: 1 Select a cell or range of cells for which you want to calculate data. 2 From the data source or data source ad hoc ribbon, select Calculate then select one of these options: 36 Data and Data Cells l To calculate the selected cells, select Calculate. l Force calculation to run for all selected cells regardless of cell status, select Calculate Then Force Calculate.

Data Sources: Essbase In Essbase, you use a calculation script to calculate the database. Calculation scripts are created By your administrator for your specific system. To select a calculation script: 1 From the Essbase ribbon, select Calculate. The Calculation Scripts dialog box is displayed. 2 Under Cube, select a database from the list of databases that belong to this application.

3 Under Calculation Script, select a script. 4 Click Launch. A status message tells you whether the calculation was successful or not. If the calculation was not successful, contact your Essbase administrator.

Translating: In ad hoc grids, if you have security access rights to the data, you can convert, or translate, Values from one currency to another. To translate data: 1 Select a cell or range of cells. 2 From the data source ad hoc ribbon, select Calculation, then select one of the following: l To translate the selected cells, select Translate. l To force translation to run for all selected cells, select Force Translate.

Translating currency for planning app

To enter cell data in a local currency other than the base currency for the cell: 1 In a form, select a local currency member for the cell. 2 Optional: To look up the currency's code, select View, then Currency. Available Currencies shows the application's currencies. Note the Currency Code for the Currency you want to work with, and close the window. 3 In the right column, HSP_InputCurrency, type the new Currency Code in the data cell. Typing the currency code in the data cell overrides the base currency for the entity. 4 Click Submit to submit the new currency code to the Planning server. 5 Enter the currency value in the left column, HSP_InputValue, of the data cell. 6 Click Rules on Form and select the Calculate Currencies rule to calculate and save the new currency Value.

Adjust the data

You can adjust the value of one or more data cells by a specified number or percentage if the cells contain numerical data. If you adjust the value of a cell that contains an Excel formula, the Adjusted value overwrites the formula. To adjust data values: 1 Click the data cell that contains the value to adjust. 2 From the data source ribbon, select Adjust. 3 From Adjust Data, select an option then enter the number or percentage by which you want to adjust the value of the cell. 4 Click Adjust Data.

Comments on Cell's Cells in ad hoc grids, forms, Smart Slices, and the Query Designer can contain multiple Comments. Comments within a cell are are differentiated by their labels, which are defined in Financial Management. You cannot create labels in Smart View. You select from these defined labels to add and view in Smart View. You cannot edit or delete Labels, but you can edit and delete comments. Viewing and Adding Cell Comments To view or add cell comments in Financial Management: 1 Select a cell in the grid. 2 From the ribbon, select Cell Comments. Any comments currently associated with the cell are displayed in the Cell Comments list. 3 To add a comment, from Cell Comments, select a label from the drop-down menu. 4 Click. The selected cell text label is added to the list of labels. 5 Click in the field under Cell Text and add a comment. 6 Repeat as needed to add other labels.

7 Click OK. The labels in the list are now associated with the cell

Adhoc Analysis

In ad hoc analysis, you use Smart View functionality with Excel spreadsheets to retrieve and Analyze data by selecting members, using functions, and performing a variety of operations, Including formatting, to design your reports. You can perform ad hoc analysis in Essbase, Planning, Hyperion Enterprise, and Financial Management.

Zooming members

To zoom in to a selected level: 1 Select a member. In Essbase, you can select a range of members. 2 From the data source ribbon, click the down arrow next to Zoom In, and then select one of the following Options. l Next Level to retrieve data for the children of the selected members l All Levels to retrieve data for all descendants of the selected members l Bottom Level to retrieve data for the lowest level of members in a dimension l Same Level to retrieve data for all members at the same level as the selected member (Essbase only) l Sibling Level to retrieve data for the siblings of the selected members (Essbase only) l Same Generation to retrieve data for all members of the same generation as the selected Members (Essbase only) l Formulas to retrieve data for all members that are defined by the formula of the selected Member. The formula can be a member equation or a consolidation to the parent (Essbase only).

Formulas while zooming To propagate formulas: 1 From the Smart View ribbon, click Options, and then select Member Options in the left panel. 2 Under Comments and Formulas, ensure that Preserve Formulas and Comments in ad hoc operations (Except pivot) is selected. 3 Select Formula Fill. 4 Click OK.

Pivoting You can pivot a dimension between rows and columns if there are two or more dimensions in The row or column that contains the dimension that you want to pivot. You can also pivot a Member; if you do so, the other members in its group are also pivoted. To pivot a dimension or member: 1 Select a dimension or member. 2 From the data source ribbon, click Pivot. Row dimensions are pivoted to the topmost column dimension. Column dimensions are pivoted to the leftmost row dimension

Working with Forms in Smart View

Forms are grid displays in which you can enter data into the database from Excel and view and Analyze data or related text. Certain dimension member values are fixed, giving you a specific View into the data. Using Smart View, you can work with Planning, Financial Management, and Hyperion Enterprise forms in Excel.

In forms opened in Smart View: l You can modify data values but not the form structure in forms. l Values submitted to the database from Excel must be non-formatted data. l If a form is currently loaded in Excel and the administrator changes the form definition on The server side, Oracle recommends that you close the form and reload it. This action ensures That the newest form definitions are displayed.

Enable and Disable of Smart View Disabling Smart View within Smart View To disable Smart View for all Microsoft Office applications (including Outlook): 1 From the Smart View ribbon, select Help. 2 Select About. 3 Clear Enable Add-in to disable Smart View the next time you open an Office application. To disable Smart View for Outlook only: 1 From the Smart View ribbon in Excel, select Options, then advanced in the left panel. 2 Under Others, select Disable Smart View add-in in Outlook.

Creating Task List Data Source Types: Planning, Financial Management To review the status of your process, you can create a detailed report of one or more task lists in an application in PDF or Excel worksheet format. To create a task list report: 1 From the Smart View Panel, open a task list. 2 Right-click a task and select Create Report. 3 In Report Wizard, use the arrow keys to move all task lists to be included in the report from Available Task Lists to Selected Task Lists. 4 Click Next.

5 Use the arrow keys to move the users whose status you want to view from Available Users to Selected Users. 6 Click Next. 7 Select options to create your report. 8 Click Finish. The report is created in PDF or Excel.

Functions in Smart View:

If you are familiar with the contents of your database, you can use the Smart View functions Described below to perform operations on specific data in Excel cells. l HsGetValue: Retrieves data from a data source. l HsSetValue: Sends values to the data source. l HsCurrency: Retrieves the entity currency for the selected members. l HsDescription: Displays the description for the default member. l HsLabel: Displays the label for the default member. l HsGetText: Retrieves cell text from the data source. l HsSetText: Sends cell text to the data source. l HsGetVariable: Retrieves the associated value for a substitution variable. l HsGetSheetInfo: Retrieves detailed information about the current worksheet.

Steps to create function: 1. You can create functions manually or by using the Function Builder.

To create Function To create functions using the Function Builder:

1 Ensure that your connection is a private connection. If it is a shared connection, save it as a private Connection before connecting. 2 Click the cell in which you want to enter the function. 3 From the Smart View ribbon, select Functions, and then Function Builder. 4 From Select Connection, select a data source. 5 From Select Function, select a function. 6 From Select Member, select a dimension. 7 In the Member column, click Select Member to select a member. (If you want to use the POV default Members for the dimension, do not select a member.) You can also double-click in the column and type in a member name or cell reference. 8 Double-click in the Member Type column. From the drop-down list, select Member or Cell Reference (Cell references cannot be used with HsLabel). When you select Cell Reference, the value in the referenced cell is used in the function. 9 HsSetValue only: Select Data or Cell Reference and enter the value to submit. 10 HsGetText and HsSetText only: l Select Comments or Cell Reference, and then enter the cell text to submit l Select Cell Text Label, and then select a label from the drop-down menu. 11 Repeat step 6 through step 10 as needed. 12 Click Add to Function. 13 Optional: To add another function, click Add to Function. 14 Optional: If you have edited the function in the Function field, to validate the function syntax before Inserting it into the worksheet, click Validate Syntax. Note: Validate Syntax validates only the syntax you are using for the function. It does not Validate the members you have selected. It is enabled only if the syntax is invalid or if the syntax has not already been validated. 15 Click OK to insert the function in the selected cell.

The OK button is enabled only if you have first selected Add to Function. 16 To execute the function, follow the procedure in Running Function"

Creating Functions Manually In Excel 2003, functions can contain a maximum of 255 characters. See Microsoft Documentation and support site for information about character and other Excel limitations. To create a function manually: 1 In Excel, click the cell in which you want to enter the function. 2 Enter = (equal sign). 3 Enter the function name, HsSetValue, for example. 4 Enter parameters for the function, using the information specific to each function in Function Descriptions on page 141. Connection parameters can have these values: l Empty: the default connection l HsActive: the active associated connection l The user-defined name for a private connection 5 To refresh the worksheet, from the Smart View menu, select Refresh.

Syntax Guidelines See Function Descriptions on page 141 for the syntax of individual functions. l The POV is composed of dimension#member pairs, for example, Entity#Connecticut. l If you specify a connection name, it must precede the POV. l Parent-child relationships are designated by a period, for example, Entity#UnitedStates.Maine. l The connection and POV can be grouped as one parameter, for example My_connection;Entity#UnitedStates. Alternatively, they can be split up into multiple function parameters, for example, My_connection, Entity#UnitedStates, Account#Sales.

l If the connection and POV are in the same parameter, the connection and each Dimension#member pair are separated by a semi-colon (;), for example, My_connection;Entity#UnitedStates;Account#Sales

To run functions and retrieve values: 1 Open the worksheet that contains the functions you want to run. 2 Do one of the following: l For HsSetValue, from the Smart View ribbon, select Submit Data. l For other functions, select one: To run functions and update all worksheets in the workbook, from the Smart View Ribbon, select Refresh all Worksheets. m To run functions and update only the active worksheet, select Refresh.

HsGetValue Data sources: Financial Management, Hyperion Enterprise, Essbase HsGetValue retrieves data from the data source for selected members of a dimension. When HsGetValue retrieves no data, the value specified for the #NoData/Missing Label replacement option is used (see Table 9, Data Options, on page 130.) When users select Refresh or Refresh All, only HsGetValue is called. When users select Submit, HsSetValue is called first, HsGetValue is then called only if HsSetValue returns successfully. Syntax HsGetValue(Connection,POV) Example In this example, HsGetValue returns the value from the HFM01 application for the default POV. HsGetValue(HFM01;Scenario#Actual;Year#2004;Period#July;View#YTD; Entity#UnitedStates.Connecticut;Value#USD;Account#Sales;ICP#[ICP

None];Custom1#GolfBalls;Custom2#Customer2;Custom3#[None];Custom4# Increases)

HsSetValue Data sources: Financial Management, Hyperion Enterprise, Essbase HsSetValue sends a data value from a worksheet to a data source selected members of a dimension. To send data to a data source, you must have the appropriate load rule and write access for the data source. Syntax HsSetValue (dollar amount,Connection,POV) Example In this example, HsSetValue sends the value from cell H4 to the HFM01 application. HsSetValue (H4, "HFM01","Scenario#Actual;Year#2004;Period#"&B$2&";View#<Scenario View>;Entity#UnitedStates.Connecticut;Value#<Entity Currency>;Account#"&$A4&";ICP#[ICP None];Custom1#GolfBalls;Custom2#Customer2;Custom3#[None];Custom4# Increases")

HsCurrency Data sources: Financial Management, Hyperion Enterprise HsCurrency retrieves the currency value of the specified dimension member. Entity and Value are the only valid members for the HsCurrency function.

Syntax HsCurrency (Connection,Entity;Value)

Example In this example, HsCurrency retrieves the entity currency where the currency for the East Sales

Entity is USD, and the currency for the UKSales entity is GBR. The EastSales entity displays USD, and UKSales displays GBR. HsCurrency(Comma,Entity#EastRegion.EastSales;Value#<Entity Currency>.) HsCurrency(Comma,Entity#EastRegion.UKSales;Value#<Entity Currency>.)

HsDescription Data sources: Essbase, Financial Management, Hyperion Enterprise HsDescription displays the alias of the specified dimension member. Syntax HsDescription (Connection,Dimension#Member) Example In this example, HsDescription displays the description for Custom 4. HsDescription("HFM01","Custom4#Increases")

HsLabel Data sources: Financial Management, Hyperion Enterprise HsLabel displays the default member label for the specified dimension member. Syntax HsLabel (Connection,Dimension#) Example In this example, HsLabel function retrieves the label for the Scenario dimension in the Comma application: HsLabel (Comma,Scenario#) HsGetText Data sources: Financial Management HsGetText retrieves cell text from the data source for dimension members, cell references, the default POV, or a combination of all three.

Syntax HsGetText (Connection,POV,CellTextLabel) Example In this example, HsGetText returns the cell text from the HFM01 data source for the default POV. HsGetText(HFM01,"Scenario#Actual;Year#2004;Period#"&B$2&";View# <Scenario View>;Entity#UnitedStates.Connecticut;Value#<Entity Currency>;Account#"& $A3&";ICP#[ICP None];Custom1#GolfBalls;Custom2#Customer2;Custom3#[None];Custom4# Increases") HsSetText Data sources: Financial Management HsSetText sends cell text to a data source. You can use all dimension members, cell references, the default POV, or a combination of all three. Syntax HsSetText(Cell Text Comments,Connection;POV) Example In this example, HsSetText sends the text from cell H3 to the HFM01 application. HsSetText(H3,HFM01;Scenario#Actual;Year#2004;Period#"&B$2&";View#<Scenario View>;Entity#UnitedStates.Connecticut;Value#<Entity Currency>;Account#"&$A3&";ICP#[ICP None];Custom1#GolfBalls;Custom2#Customer2;Custom3#[None];Custom4# Increases")

Common Function Error Codes Some common error codes displayed in functions: #NO CONNECTION - You are not connected or logged on to a data source. #INVALID - Invalid metadata. Invalid cells that contain a value display the value as zero.

#LOCKED - The cell is locked. #NO ACCESS - You do not have access to this cell. #NO DATA - The cell contains NoData. You can select to display zeros instead of NoData. Cells use the Replacement text that you specify in the Options dialog box. #INVALID INPUT - The HsSetValue data value is not valid, for example, a text string. #READ ONLY - This is for the HsSetValue function only when the cell is Read-only. #NO ROLE ACCESS - You do not have the Financial Management LoadExcelData security role. #NEEDS REFRESH - Data needs to be refreshed. #INVALID DIMENSION - An invalid dimension is specified in the function. #INVALID MEMBER - An invalid dimension member name is specified in the function. #NAME - Excel does not recognize text in a formula. When you forward a worksheet that Contains functions to a user who does not have Smart View, they can view the same data as the functions on the worksheet. When the user edits or refreshes the function, it changes to #Name.