Smart View
Purpose: Use this document as a reference for the Smart View add on for PBCS
Audience: Budget Development Staff
Smart View provides a common Microsoft Office interface for PBCS. Using Smart View allows users to view, import, manipulate, distribute, and share data through Microsoft Excel, Word, and PowerPoint interfaces
Smart View benefits include:
- A common Microsoft Office interface for all Planning users
- Provides ad-hoc query capability to users in a familiar Excel environment
- Allows users to dynamically access the live data and develop their own queries
- Allows users to pull PBCS reports to manipulate in Excel
How can you tell if you Smart View is downloaded in your system?
- When Smart View is downloaded in your system, it creates a Smart View ribbon in Excel.
- If this ribbon is not found, either Smart View is not installed or the process of installation requires additional steps
If you are having troubles installing Smart View, please contact SBCTC Admin.
In order to use Smart View you will need to install this on your local computer.
Navigation: Open a new Excel Workbook, while on active sheet, click on Smart View ribbon and click on the Options icon
Under Options, click on Advanced. Click once in the Shared Connections URL box to select it.
Copy and paste the following URL in the box
UAT: https://planning-test-a563320.pbcs.us2.oraclecloud.com/HyperionPlanning/SmartView
PROD: https://planning-a563320.pbcs.us2.oraclecloud.com/HyperionPlanning/SmartView
Also, uncheck “Disable Smart View in Outlook” under Display section
In the bottom, right hand corner, drop down the arrow next to OK
button and select “Save as Default Options”
There are two connection options to PBCS via Smart View - Shared Connection and Private Connection.
Shared connections are stored in a central location and are available to multiple users through the Smart View Panel.
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.
Creating a Shared Connection
To connect to PBCS, open a new sheet in Excel. Click the Smart View tab on the top menu. Select the panel icon within the Smart View tab on the ribbon to bring up the Smart View Panel on the right side of the screen.
Within the Smart Panel choose the Shared Connections icon, then type in your user ID and password and click Sign In. We recommended using the company sign in.
1. Click on Smart View ribbon
2. Click on Panel
3. Click on Shared Connections
4. Enter your Oracle user ID and password in the Login window and click on Sign In
Note: If you don’t have a log in, please create a service desk ticket
From the Connection Panel screen, click the dropdown arrow and select EPM Cloud.
Click the + next to the server number
Then expand the SBCTCFIN application.
Click the Finance member and either click Connect from bottom or right click and select Connect.
You are now connected to the Finance database
If you will be connecting to this Finance database on a regular basis, you may add this connection as a Private Connection. Click the Add to Private Connections link.
Creating a Private Connection
On the Save as Private Connection screen, type the following information Name and Description information in the highlighted fields and click OK.
Once the Private connection is created, you can see verify the connection.
Click Home button and Select Private Connection.
You will see you Finance Connection private connection that you have given the name.
To disconnect the connection, Select the down arrow button near to Home Button and then select “Disconnect All”.
UAT Test Case
Confirm you are able to connect Smart View
Click Smart View tab from the Excel ribbon then Click Options icon
Member Options:
Click on ‘Options’ to bring up the pop-up window
- Within ‘Member Options’ on the left-hand side you will see the below fields and options
Click Member Options and make the following changes as desired:
- Zoom In Level Set to Next Level
- Indentation Select Subitems, None or Totals
- Ancestor Position Select Top or Bottom
Leave rest of the options “As Is”. Once you are familiarized with these features, you can change these options to your convenience
Data Options:
Select Data Options and note the options:
- Suppress Rows - Repeated Members Check the box if you do NOT want to see the same member name in each row if there are repeated member names. Leave unchecked if you always want to see member names in each row.
Note: Leave the other boxes under Suppress Rows unchecked in order to be able to start an ad hoc query. Once a query is started, you can come back and choose to suppress rows at that time.
- Replacement - #NoData/Missing Label Set to #NumericZero
This allows for Excel calculations to be used in queries
Formatting:
Under Formatting, enable the automatic adjustment of Excel column width to accommodate the contents of member and data cells.
If ‘Adjust column width’ is not selected, you can adjust the width of columns manually.
Once the Smart View Options have been set to your liking, click the drop down arrow next to the button.
Click Save as Default Options:
UAT Test Case
Confirm you are able to Setup Smart View Options
This feature can be used for retrieving data into excel on an ad hoc basis. It can be for one-time use or the Excel file can be saved and reused to refresh data later. The following instructions show how to use the Ad Hoc features available
Connect to PBCS in the Shared Connections Panel: (Please refer QRG 3.2 Connective to Smart View)
Click the + icon next to SBCTCFIN and highlight Finance (if you open through Private connection then it will show the name that you have save the database name)
Ad hoc analysis should now be showing on the bottom of the panel. Click on Ad hoc analysis and the beginning of a new grid will appear on the Excel sheet:
Planning Ad Hoc Ribbonallows you to perform certain functions that helps accelerate working with smart view while using ad hoc analysis
#No Access may appear because the dimensions have not been specified and are pointing to the top-most level.
Dimensions will be located in the Rows, Columns, or Point of View (POV) (Floating/Toolbar). The POV is located in the POV menu box), and the members selected in these dimensions will apply to the entire grid. There must always be at least one dimension in the Rows and one in the Columns. You can move Floating POV to POV toolbar.
The location of the dimensions can be moved where desired. To move dimensions from the POV to the row or columns, left click, hold, and drag the dimension as shown below:
To move a dimension to the POV, click the dimension and use the Pivot button to “swap” dimensions between columns and rows or between the grid and POV. You must have at least two dimensions in rows or columns before you are able to pivot a dimension out.
Example: Moving BusinessUnit dimension from row to POV
We can also simply delete a dimension from the rows or columns and hit Refresh to send it to the POV. Alternatively, we can select a dimension member and click Pivot > Pivot to POV.
Method One: Delete a dimension from row
After hitting refresh, you will see BusinessUnit dimension in POV
Method Two: By clicking Pivot menu.
First select dimension in the row and then click “Pivot to POV”.
Moving Dimension from POV to Row/Column:
A row or column can also be inserted into the Excel sheet and a dimension member from the POV can be typed in.
Click Refresh and that dimension will now be in the grid and not the POV.
After refresh, the fund dimension no longer in the POV
The Planning Ad Hoc ribbon is available once a user connects to the Finance or Workforce database. It provides features and functions available for ad hoc analysis. This section will review the buttons users will most frequently use.
Zoom In & Out of Dimensions
- Zoom In: Allows you to drill down into lower members within the selected dimension hierarchy
- Zoom Out: Allows you to drill up one level higher within the selected dimension hierarchy
You have options to Zoom In at different levels:
Keep and Remove Members
- Keep Only - Allows you to keep only specified selected member(s)
- Remove Only - Allows you to remove only specified selected member(s)
Note: This only works when selecting members from one dimension at a time.
Refresh
The grid will need to be refreshed when a change has been made to the ad hoc grid. The latest data will be pulled with every Refresh. Click Refresh when you perform the following actions:
- Change dimension members
- Change dimension layout
- Change any Smart View options
Change Alias
All members have an actual member name and alias (description). To toggle between the member name and alias, click the Change Alias button and choose between None or Default.
- None displays the member names (shorthand names you can type into the ad hoc grid to retrieve the member)
- Default displays the alias (full descriptions of member names)
Note: If the user selects the None option, the Alias table for members will not be used.
Dimension Selection
Now that you understand the dimensions and the layout of the ad hoc grid, it is time to select members within the dimensions and see some data.
Please note that all dimensions must specified for data to show (e.g. Select FD001_101 for Fund dimension, selecting the Fiscal Year FY21 etc). There are several ways to select dimension members for the Rows, Columns, and POV:
- Click on the dimension name and then click Member Selection to select from the dimension hierarchy
- Type in the exact member name in the cell or POV field containing the dimension name
- Select members in the POV by using the POV toolbar
- Drill in and out of a dimension using the Zoom In button (or double click the cell) and Zoom Out button until you find the desired member(s) within a dimension. Use Keep Only and Remove Only to simplify your grid.
Note: Once you are familiar with the hierarchy and names of the members, typing member names into cells will simplify your ad hoc experience tremendously. Always hit Enter or click out of the cell after typing in a member/alias name and then click Refresh to view correct data for that intersection of members, otherwise an error will appear.
Member Selection - Option 1 POV toolbar selection
Click on the drop-down arrow to the right of a dimension in the POV toolbar.
Next, Click on the ellipsis “…” in the drop-down to select a member from the dimension:
The Member Selection window will appear:
Find and check the box next to the member(s) that you want to use and click on the right-pointing arrow to move it to right-hand (selection pane) section. Click OK.
Remove anything from the right-hand section you do not want by highlighting the member and clicking the left-hand arrow (you can leave more than one member in the right-hand section). Click OK.
Select the member you selected from the drop down menu in the POV menu. If you chose more than one member for a dimension, both options will appear in the drop down menu, but you can only choose one to apply to the grid.
You can search the member from member selection window:
You can also type in the member name directly in the POV toolbar as long as you know the exact spelling
Option Two – Direct Import dimension member
The member name can be directly entered into the spreadsheet cell containing the dimension.
NOTE: When using this option, the member name must be entered in exactly as it is displayed in PBCS, otherwise it will not be recognized. It is not case sensitive.
Option Three – Member Selection
Highlight the member in the spreadsheet and click on the ‘Member Selection’ from the Planning Ad Hoc ribbon. A dialog box displaying the available selections within the highlighted dimension will then be displayed.
- Check the box of the member to select. Then move to the right side by clicking on the right arrow.
- The drop-down arrows next to the movement arrows allow you to select if you want a relative of that member, such as “Descendants Inclusive.”
Click Ok
Once you are done entering all dimension members, Click Refresh in the POV menu to see the data with the newly selected member.
As mentioned earlier, we can also drag and drop POV members from the POV menu to the grid
Click and hold on the drop down arrow next to the dimension name and drag it from the POV menu to the rows or columns of the grid.
Cascade
Cascade allows you to create separate grids for a selected POV member in the grid into a current workbook, new workbook or different workbook. The grids are populated in separate sheets of the Excel document. Sheets will be named with the member name used to cascade.
The purpose of cascading is to create one standard format for a report and reuse it across the dimension being cascaded.
The benefit is to standardize and reduce the amount of effort and maintenance required to produce a booklet of reports.
Excel Formulas and Conditional Formatting
You can insert formulas and format like you normally would in any other spreadsheet. Common examples include: bold, cell color, fonts, cell borders, and math functions.
Depending on your Formatting settings under Options, Smart View will preserve all formatting when you save and reopen the sheet.
UAT Test
Test Case: Retrieve PBCS data using Ad hoc analysis
Method One: Opening data form manually in Smart View.
Connect a new sheet to SBCTCFIN application and expand Library -> Folders and right click on the desired form and select “Open form”.
(Please refer Connecting to Smart View to connect Smart View)
Make member selections in the POV bar and click Refresh.
Enter the data for Budget and YearTotal at leaf level intersection (Yellow Cells)
Grey Cells Read Only
Yellow Cells Write-able cells
Click Submit Data menu to send entered data in PBCS.
Method Two: Open Web data form in Smart View.
From any web data form, Click on the Action drop down.
Click on Open in Smart View
The prompt appears, the user has the option of saving the file or opening it.
Click on Open with to continue.
Excel will open. Click to open a Blank Workbook.
Provide your credential. Once the login credentials are accepted, the form will open in Excel as shown.
UAT Test Case
Open any web data form in Smart View and Submit the data in PBCS
0 Comments
Add your comment