InfoView: Formulas and Custom Variables
Formatting, Custom Variables and Formulas What is a Formula? A formula is a complex calculation that you create to display data that is not retrieved by existing objects in the universe. Use a formula when you require a calculation for a table and do not need to use it again. What is a Custom Variable? There are many objects available to you in the Data Warehouse. Sometimes it is necessary to create your own Objects within a report. These are called Custom Variables. They are Formulas that have a defined name. Since they are created and exist within a report, they must be re-created for new reports. Why bother to give Formulas a name (Custom Variable)? • • • •
Custom Variables can be used as a sort or break or as a basis for some other functionality Custom Variables can be easily re-used elsewhere within the report (on any tab) Custom Variables can be used in making other Custom Variables Custom Variables can be used as an X or Y axis in a chart or graph
What would I use Formulas and Custom Variables for? Custom Variables and Formulas are valuable when editing a report to meet specific business needs. They allow flexibility in displaying data for many purposes, including: •
Cosmetic Preferences - headers and titles, grouping variables into one, etc. o
•
Mathematical Functions - sums, balances, count, percentage, etc. o
•
Example: Adding Encumbrance Amount and Reserve Amount so that only one column is necessary to represent the Liens of a FOAPAL.
Unique Sorting - by using one or more custom variables (in combination), you can create a unique sorting order specific to a report o
•
Example: Instead of having one column display payroll title code and another column display payroll title, they could be combined to display "7253 Budget Analyst" in one column.
Example: Displaying financial information by account code pool codes in a specifically desired order
Altering Data - markups, rounding, renaming, etc. o
L. van Doorn 10/15/2014
Example: A report for a manager could display dollar amounts rounded to the nearest thousand Page 1 of 22
InfoView: Formulas and Custom Variables
Tools We will be using the Variable Editor to create variables
To open the Variable Editor click on New Variable on the Data Objects sub-tab within the Data Access tab
Certain editing functions in InfoView require that Java be enabled. We do not have a comprehensive list of these functions as we are still discovering them. Some of these functions are: Creating/editing Alerters, creating sub-queries, Excel as a data source and changing data source. •
From Preferences select Web Intellingence on the left. In the Web Intellingence window select Applet in the View as well as the Modify section.
L. van Doorn 10/15/2014
Page 2 of 22
InfoView: Formulas and Custom Variables
Getting started Open the report titled “IV Exercise 1 – Starting Point” which has been prepared as a starting point for this exercise. It already contains data, so it will not need to be refreshed. Click on Design in order to be able to edit the report. Recap / General InfoView Knowledge •
Bring in a blank cell to the right of the Last Refresh Date cell (Cell sub-tab in the Report Elements tab)
•
Type: “Last Refresh Date:”
•
Align the two cells horizontally (Position sub-tab in the Report Elements tab)
•
Format the Last Refresh Date as: Month DD, YYYY
•
Remove underlining on section name (Title Level 3 & 4) ( Border sub-tab in the Formatting tab)
•
Center Fiscal Year in column header (Alignment sub-tab in the Formatting tab)
•
Remove blue background in Acct Pool Title (Style sub-tab in the Formatting tab)
•
Resize cell so full name of Acct Pool Title appears
•
Format number to remove decimals
•
Add a sum to the columns (Functions sub-tab in the Analysis tab)
•
Add Page Number /Total Pages to report footer (Cell sub-tab in the Report Elements tab)
•
SAVE
L. van Doorn 10/15/2014
Page 3 of 22
InfoView: Formulas and Custom Variables
Your report should look something like this:
Using the Pred-Defined Cell “Prompt” (Cell sub-tab in the Report Elements tab) The Pre-Defined cell named “Prompt” replaces the need to create a “User Response” custom variable. Clicking on the Pre-Defined cell “Prompt” will bring up a list of the prompts used in your report
When you select a prompt you will be “prompted” to insert a cell in your report. The data in the cell will be whatever the response was to the prompt when the report was run i.e. user response.
L. van Doorn 10/15/2014
Page 4 of 22
InfoView: Formulas and Custom Variables
Example: The GL Fiscal Year (Start) for this report was 2006. When I insert the cell after selecting that prompt the following will appear. defined cell the formula would look like this:
If I want to add “Fiscal Year” in front of the pre-
="Fiscal Year "+UserResponse("Enter GL Fiscal Year(Start):") Repeat for the Fiscal Year (End) adding “To Fiscal Year” in front the of the pre-defined cell formula. ="To Fiscal Year "+UserResponse("Enter GL Fiscal Year(End):") The Header of your report should look like this:
Conditional Formatting (Formerly know as Alerters): Conditional Formatting enables you to create rules to highlight results that meet or fail criteria set by you. You can create a Formatting Rule to highlight high or low results with a specific color or with a text comment You can define Formatting Rules to activate the following formatting changes to the selected table columns/rows or cells: • text color, size and style • cell border colors and style • cell background display – specific colors, images, or hyperlinks to web pages For this exercise we will highlight in red and bold any results with a negative value. First we need to enable Java. Conditional Formatting can only be created or edited while in Java (Applet) mode.Switching from HTML to Applet (Java) •
From Preferences select Web Intellingence on the left. In the Web Intellingence window select Applet in the View as well as the Modify section. Once you have completed your task switch back to HTML. REMEMBER TO SWITCH BOTH THE VIEW AND MODIFY BACK TO HTML.
L. van Doorn 10/15/2014
Page 5 of 22
InfoView: Formulas and Custom Variables
From the Conditional tab within the Analysis tab select New Rule The Formatting Rule Editor will open Name your Alerter: Negative Value
Select Format > Text in order to format your result
L. van Doorn 10/15/2014
Page 6 of 22
InfoView: Formulas and Custom Variables
Click OK to create your Conditional Formatting To apply the Conditional Formatting click on Formatting Rules within the Conditional tab click inside of your report and select the rule that applies. In this case: Negative Value. Your report should look like this:
***Remember to switch back to HTML mode.*** Creating a simple variable for convenience: Take a look at the page number. We could bring in a blank cell and type in “Page:” like we did for the “Refresh Date:” or we could create a custom variable that can be used on every tab within the report. To do this we will click on New Variable
L. van Doorn 10/15/2014
and create the following variable:
Page 7 of 22
InfoView: Formulas and Custom Variables
="Page "+Page()+" of "+NumberOfPages()
Simple Report Filters: You can set up a quick filter on selected objects within your report. Click the sub-tab within the Analysis tab. Click on the icon to add objects to be filtered
on the Interact
From the drop down list select the Art Department (OH Title Level4), Academic Salaries (AH Account Code Pool Title) and 2006 (GL Fiscal Year) To display the filtered criteria on your report select from the Cell sub-tab within the Report Elements tab. Click at the bottom of the report header to insert the cell. Expand the cell to view all of your criteria. L. van Doorn 10/15/2014
Page 8 of 22
InfoView: Formulas and Custom Variables
Your report should look like this:
Another way to add filters to your report is to click on Analysis tab.
on the Filters sub-tab within the
Right-click on the “Start” Tab. From the drop down menu select: Duplicate Report. Remove the filters. The Report Filter will open. Click on
Select the same objects as you did for the drill filters: OH Title Level 4, AH Account Code Pool Title and GL Fiscal Year. The filter will open. Select your criteria and click Apply and OK. L. van Doorn 10/15/2014
Page 9 of 22
InfoView: Formulas and Custom Variables
Grouping Some of the Account Code Pool Titles have names that many people on campus either do not understand or do not like. A few of the names include the word "Budget" which can be misleading. Therefore, a popular data alteration is to rename some or all of them. •
Right Click on the report tab to insert a new report
•
Drag the following objects on to the report: Account Code Pool and Account Code Pool Title
• • • •
Click in the Acct Code Pool Title column From the Display sub-tab within the Analysis tab click Group Name the Group: ACCT POOL TITLES Select CAPITAL EXPENDITURE-BUDGET
• • •
Click on Group Type the new name for the Account Code Pool : EQUIPMENT Repeat for: o NON-CAPITAL EXPENDITURE-BUDGET = GENERAL EXPENSE o RECHARGE REVENUE POOL BUDGET ACCT. = RECHARGE REVENUE o REVENUE POOL ACCOUNT = RECHARGE REVENUE o UNALLOCATED-BUDGET = UNALLOCATED o YE CARRY FORWARD OFFSET BUDGET ADJ = YE CARRY FORWARD
L. van Doorn 10/15/2014
Page 10 of 22
InfoView: Formulas and Custom Variables
• Click Apply and then click OK The report should look like this:
Please note the “Group” now acts like a Custom Variable and can be used throughout the report. The Group function replaces the Custom Variable created with“If/Then/Else” statement If you wanted to create a custom variable to do what the previous grouping is doing this is what you would do:
Click on New Variable on the Data Access tab
to create the following variable:
=If[AH Acct Code Pool]="B03000" Then "GENERAL EXPENSE" ElseIf[AH Acct Code Pool]="B04000" Then "EQUIPMENT" ElseIf[AH Acct Code Pool]="B08000" Then "UNALLOCATED" ElseIf[AH Acct Code Pool]="R00000" Then "REVENUE" Else If[AH Acct Code Pool]="B080YE" Then "YE CARRY FORWARD" ElseIf[AH Acct Code Pool]="B09000" Then "RECHARGE REVENUE" Else([AH Acct Code Pool Title])
L. van Doorn 10/15/2014
Page 11 of 22
InfoView: Formulas and Custom Variables
Creating a Custom Variable with If/Then/Else statement that uses “IsNull” function and “Between” operator Exercise: Display the relevant Title for either the Activity, Fund or Org Right Click on the report tab to insert a new report From the Available Objects drag the following objects on to your report: Org Code, Fund Code and Activity Code-blanks last
Create the following New Variable:
=If IsNull([VH Actv Code - blanks last])And([FH Fund Code]>="20000"Or[FH Fund Code] Manage Breaks Check the box for “Break footer” Repeat for the Undergrad/Grad labels. Click in the cell with the Student ID Count object. From the Functions subtab within the Analysis tab Click on the Sum icon. Remove the extra “Sum” row at the bottom of the report Change “Sum” to be Subtotal or Total where applicable.
L. van Doorn 10/15/2014
Page 18 of 22
InfoView: Formulas and Custom Variables
Your report should look like this:
Training Objective 2: Add a percentage that represents the % of headcount for Upper/Lower Division by Undergrad/Grad label by Registration status for the entire campus Insert Column to the right of Student ID Count column Create a formula by clicking in a cell the newly in created column and clicking on the formula editor
=[R Student ID count]/Sum([R Student ID count]) ForAll ([REG STATUS]) Add this formula to the Subtotal and Total cells as well. Format the number to display as %. Numbers sub-tab in the Formatting tab. Add the word “Percent” to the column header Your report should look like this:
L. van Doorn 10/15/2014
Page 19 of 22
InfoView: Formulas and Custom Variables
Training Objective 3: Add a percentage that represents the Headcount % of for each level within its respective Undergrad/Grad label. Insert Column to the right of the campus total column Create a formula by clicking in a cell the newly created column and clicking on the formula editor =[R Student ID count] ForEach([RCC Upper/Lower Division Label])/Sum([R Student ID count]) In ([RCC U/G label]) Add a label to the column “ % of UG /GR”
L. van Doorn 10/15/2014
Page 20 of 22
InfoView: Formulas and Custom Variables
Your report should look like this:
Look at the “Total” and “Subtotal” cells in the % of UG/GR column. In order to insert a % for the UG and GR labels you will need to insert the following formula: =[R Student ID count]/Sum([R Student ID count]) In ([RCC U/G label]) Your report should look like this:
L. van Doorn 10/15/2014
Page 21 of 22
InfoView: Formulas and Custom Variables
Training Objective #4: Isolate information: Only display Freshman counts. Create a formula by clicking in the R Student ID Count field and clicking on the formula editor =[R Student ID count]Where([RCC Level Name]="Freshman") Your report should look like this:
L. van Doorn 10/15/2014
Page 22 of 22
