reference.
3. Member Type Column - Select Member or Cell Reference from the drop down to match the member type you entered in the Member Column. Make all the POV selections necessary for your report. You can use the Select Member button to open the Member Selection Window for assistance with your entries.
Member Selection
You can use the Member Selection dialog box to select dimension members for ALL of your reporting dimensions.
This is the same Member Selection Functionality you have already learned about with 2 important differences.
First, you can make selections for more than one dimension by using the Dimension Drop Down List.
Second, because you are able to select members from multiple dimensions, the Selection area looks different. It has 2 columns, one for the Dimension Names and one for your Member Selections. Some dimensions will have the top member listed by default.
Make the POV selections that you need help with here and click OK.
Add to Function
Now that you have selected the members you need to report the Total Operating Expense in Thousands from the First Quarter of the 2010 Fiscal Year for Global Large Enterprise, you can create our HsGetValue formula.
Simply click the Add to Function button and your formula will be created in the Function area of the interface for your review.
To build the formula, Function Builder pulls your selections from the Connection, Functions, and Member areas.
Review & Edit
Your Function is displayed in the Functions window of the interface.
You can edit the function directly here if you want to make any changes. For example, you can delete the dimensions from the formula that are not necessary to pull your data.
Validate & Paste
If you have manually edited the function in the Function text box, click the Validate Syntax button to check the formula's structure.
Note: Validate Syntax only verifies that the structure of your function is correct. It does not validate the members you have selected.
If the formula is correct and has the proper format, click OK to paste the function into the worksheet.
Refresh
The finished Formula is pasted into the target cell.
The cell will display the #Need Refresh message.
To execute the function and pull the data from your source, click Refresh in the Smart View ribbon.
Using Cells
The formula we just created used member selections.
However, you can also use Function Builder to create referential functions.
1. Double-click in the Member Column next to the desired dimension and enter a cell reference. You can use the dollar sign ($) to properly anchor your cell.
2. Select Cell Reference from the drop down in the Member Type Column.
3. Click the Add to Function button when you have entered all your references.
4. Replace the name of your data source with a cell reference in the Function box and perform any other edits.
5. Click OK when your edits are complete.
Common Function Error Codes Descriptions
Here are some common error codes that might be displayed as you work with Function formulas:
#INVALID - Invalid metadata. These cells are displayed in red in data grids. Invalid cells that contain a value display the value as zero.
#INVALID DIMENSION - An invalid dimension is specified in the function.
#INVALID MEMBER - An invalid dimension member name is specified in the function.
#LOCKED - The cell is locked.
#NAME - Microsoft Excel doesn’t recognize text in a formula. When you forward a spreadsheet that contains functions to a user who does not have Smart View, users can view the same data as the functions remain displayed on the spreadsheet. When the user edits the function or selects Refresh, the function changes to #Name.
#NEEDS REFRESH - Data needs to be refreshed.
#NO ACCESS - You do not have access to this data.
#NO CONNECTION - You are not connected or logged on to a data source.
#NO DATA - The cell contains No Data. You can select to display zeros instead of NoData. Cells without data use the Replacement text that you specify in the Options dialog box.
Converting Functions to Grids Introduction
Let's take a moment to review the Functional reporting topics covered so far.
You should understand:
1. The basic concepts of functional reporting.
2. The syntax of the HsGetValue Formula.
3. How to create the formula manually and how to create it using the Function Builder tool.
4. The difference between reporting by Member Name and reporting by Cell Reference.
We've covered this information to help you work with existing reports. Now let's take a few moments to show you how to convert your functional reports to Ad Hoc Grids, the preferred reporting method.
Process Flow
The process of converting from a Formula Based report to an Ad Hoc Grid report is conceptually easy:
1. Determine the required POV or POVs.
2. Determine whether to build a new report or convert the existing report.
3. Build the report or do the conversion.
POV
A shared element of both Functions and Grids is the POV.
Determining the POV of a Function Formula is especially easy when you are in Edit Mode in Excel.
Click in a formula cell and then click in the formula bar or double click in a formula cell if you have not set the double click function to perform Grid operations.
Once in edit mode, you can clearly see which dimensions are used in the formula as well as which values are being referenced.
The POV Dimensions in this example are: Years - Periods - Views – Accounts
Double Check
Be sure to check for elements that seem out of place.
As we review further down the report, we discover that there is a different point of view used in the formulas contained in one row.
The POV now includes the Entity Dimension in addition to Years, Periods, Views, and Accounts.
Questions to Ask
The decision to build or convert is a matter of personal choice. A few factors that might help you in your decision include:
Time - Which method will be the fastest to provide you with a usable Grid report?
Building
This report is not complicated and would be fairly easy to build from scratch. You already know the POV Dimensions and the emphasis appears to be account values by time.
Arranging the dimensions by frequency of changing members results in a grid with Years, Views, and Periods flowing down the column and Entity followed by Accounts flowing across the row. You can obtain the actual member selections from the original report and enter them here using the free-form entry method.
You can add your labels, calculations and formatting as you build out the grid. You can also validate your results if you build the report in sections and refresh the data as you build.
Converting Part 1
The other option is to convert the existing report to a Grid. If you notice, the placement of the POV members in this Function based report already resembles a Grid. Performing the following steps will convert this report from a formula driven report to a Grid:
1. Move any labels out of the reverse intersecting cell.
2. Delete any unnecessary descriptive columns (column C in the graphic).
3. Remove the database reference from your column dimensions.
4. Update any incorrect Dimension Member selections (This report required the Entity selection instead of TOPLF).
5.
Delete all of your HsGetValue formulas. Be careful not to delete other excel formulas that perform report calculations.Converting Part 2
Your report should look like the above graphic when you are through.
• The areas highlighted in Yellow are your POV Member Selections that you preserved or updated from the original report.
• The areas in Gray are your text labels that you moved or preserved and the calculations that you preserved from the original report.
• The area in Blue contained formulas but now it contains the data pulled from your source.
Compare this graphic to the one located on the "Building"
tab. Although the reports were created using different methods, they are nearly identical.
Removing Formulas
Some Functional Reports have many standard calculations intermixed with the HsGetValue formulas making it difficult or tedious to remove all of the functions. Using the standard Excel Find and replace functionality can greatly assist in this process.
1. Click Find and Select in your standard Excel toolbar.
2. Select Replace in the menu.
3. Type =hs* in the Find What field.
4. Leave the Replace With field blank.
5. Click the Replace All button.
This will effectively delete any formula beginning with =Hs.
Support
Remember, if you are using Functions in any of your reports, you are strongly encouraged to update the reports to the Ad Hoc Grid format.
It does not matter whether you choose to convert the report or build a new one; both methods are equally acceptable.
If you need assistance with this process, it is recommended that you work with a mentor from your group or seek support from the Systems Control Group (SCG).
Course Progress
We have covered a lot of information today and are almost through the course. Now that you have learned the basics of Smart View, we have a few recommended next steps for you to take.
Next Steps
As you can see, having a clear understanding of the
reporting dimensions available to you in each of the Essbase cubes will be critical to your success.
It is recommended that you take the following steps:
1. Determine with your manager which system or systems your job will require.
2. Take the course or courses that will teach you more about that specific application. The courses are listed here in the slide.
3. Apply for the appropriate access. The instructions are provided during the courses listed here.
4.
Get a mentor who can guide you as you begin using Smart View within your role.Course Progress
Congratulations! You have reviewed all of the course content and are ready to proceed to the assessment.
Remember, you will need to pass the assessment with a score of 80% correct to receive course credit.
Good luck!
NOTES:
NOTES:
NOTES: