irf formulas

8/3/2019 IRF Formulas

1/21

1

IRF Business Objects

Using Formulas in Desktop Intelligence

July, 2009


2/21

2

Why use formulas?

Extend the objects in the universe

Take advantage of analyticalcapabilities Doing in BO what you used to do inExcel


3/21

3

Table of Contents

Calculations, Formula Toolbar and Editor

Using Formulas in Reports:

Creating titles and headers that combine query promptsand text

Using formulas to create local variables

Simple calculations (variances and percentages)

Running averages

For grouping data

To create a projection based on a running average

To setup Alerters

Using formulas to create dynamic Filters Caveats with using Formulas

Troubleshooting Formula Results


4/21

4

Calculations

You can create simple formulas by adding

calculations on a column;

Sum total of column values

Count count of unique values

Count All count of all values in column

Average average of column values

Minimum the lowest value of a column

Maximum the largest value of a column

Percentage adds a percentage of total in anew column


5/21

5

Formula Toolbar

Variable editor opens variable window ifcurrently selected cell contains a variable

Define as Variable defines current cell formulaas a variable, allows capturing of context

Cancel cancels current changes made to cell(if not validated)

Validate validates the formula in the currentcell (if valid saves cell)

Formula Editor opens formula editor window(if currently selected cell contains a formula

Formula Display shows the current formula(or text) in the selected cell, allowing forimmediate editing


6/21

6

Formula/Variable Editor


Always start a formula withan = sign (otherwise it is justa text constant)

Objects (and other variables)will have arrow brackets on

either end i.e.

String constants are enclosedwith double quotes i.e. Text

Numeric constants are notsurrounded by quotationmarks and should be enteredwithout any formatting otherthan a decimal place


7/21

7


Best Practices when creating local variables

or formulas

Always start a local variable name with My soyou can tell it apart from universe objects

Give local variables clear and easily understoodnames

Spaces between components makes it easier toread the formula and avoids errors

Carriage returns can be added to the formulausing Ctrl+Enter to make it easier to read

Document your local variable in the report, soothers can see the logic and/or process


8/21

8

Using Formulas in Reports

Creating titles and headers that combine

query prompts and text

Insert a prompt formula from the mainmenu:

Insert | Special Fields | Prompt Value

Add text in quotes and combine with a +or & sign

="Accounts: " + UserResponse ("ControlPage" , "C01.

SL Acct6 No(s) (nnnnnn;...):")

For multiple prompts, create a cell, insertthe prompt formula and cut and paste it intothe formula (deleting cell afterwards)


9/21

9


Using formulas to create a local variable;

For simple calculations in tables Insert a column in the table

From the menu Select Data | Variables

Click the Add button

Create the variable in the Editor;

My Proj % of Budget Spent

=/

To Handle #DIV/ 0 results, add an IF clause

= If = 0 Then 0Else /


10/21

10


Using formulas to create a local

variable;

To create running averages

Insert a column in the table




My Curr Mo Amt Running Average

=RunningAverage()


11/21

11


To create your own local variable to group

data (vs. creating a grouping)

Insert a column in the table




My Expense Type

=If ToNumber() Between (9100,9179) Then "InDirect" Else "Direct"

Add a Break and totals on the new variable


12/21

12


To create a Projection based on a running average;

Months remaining in FY year

My Months left in FY

=12 - ToNumber(Max())

Multiplied by the running average of the current

month amount, plus the FYTD Amt

My Projected FY End Amt

=*+

The Projected FY End Amt is a snapshot and shouldnot be totaled. The variance to the budget balancewould show the Projected FY End balance


13/21

13


To setup Alerters;

Example from

FR-GrantsAndContracts-ForDept.rep

Uses days between dates to show when aFinancial report is potentially out-of-date

MyDaysBetweenReportFileandDue

=If(Not IsNull()) Then

DaysBetween( ,) Else 0


14/21

14


To Setup Alerters;

For Alternating Row Coloring Create a Local variable

ALT-Row-Color

=Even(RunningCount() )

Select the entire row of data and add an alerterfor when the value of the variable is equal toone

In this example The object isused. If your table is based on sub accounts ordates you should use the appropriate object thatis in each row of your table.


15/21

15


Using formulas with Filters;

Use formulas with a filter to dynamically select data,

regardless of the current data in the report

Or when multiple filters need to be placed on the sameobject

Commonly formulas are used to;

Select a range of values Eliminate null, blank or zero values

Pattern match values

These filters are commonly kept as part of the reportand should be documented in the report tab name andtitles


16/21

16


Using Formulas with Filters (example);

Select Table to be filtered

From the menu select Format | Filters

Select the table to be filtered and click the Add button

Select the field to create a filter on and click the OK button

Click on the Define button and enter the formula to filter thedata

= Between ("5000" ,"5999")

=Not(=0)

=Not(IsNull()) And Not (=" ")

=Match( ,"*MAC*")

Note that account numbers and Calendar YrMo are stringvalues and must be in quotes

After you click OK to save the formula, the results that matchthe formula should be highlighted.


17/21

17

Caveats with using Formulas

On the report manager data tab;

When viewing by data provider, variables andformulas will appear in the data provider sectionthey are based on, if based on multipleproviders they will appear in the commonsection

Broken variables or formulas wont bedisplayed, you will find them in the variables list

(Data > Variables on the main menu) notatedwith a caution symbol (red triangle with anexclamation mark inside).


18/21

18

Caveats with using Formulas

Dates cannot be subtracted in Business

Objects. Use the DaysBetween() function.Note this will only return the whole daysbetween the dates, not the partial days

MATCH(string1,string2) When using thisfunction the documentation says to use %as a wildcard for any number of characters.

This is incorrect, use * instead.


19/21

19


If you get a results of#DIV/ 0 this indicates you aredividing by zero and should consider either adding aIF clause to the formula or if you are using the correctobjects in your formula

If you get a result of#MULTIVALUE this indicatesthere is more than one value that can be displayed inthe cell. If the values are the same for each row youmay need to add =max() or =min()to the formula. Otherwise you should consider addingthe object to the table that will create separate rows

for each value.

If you get a result of#COMPUTATION this generallyindicates that the formula cannot be resolved with thecurrent context.


20/21

20


Context (or qualification) by location

Measurement objects, formulas or localvariables will automatically aggregate by theirlocation;

On the page header or outside of the sections;

total for the report

In a section;

total for the section

In a table break footer;

total for the break level

In a table row;

total for the row

Unless specified in the Formula


21/21

21

Questions?

NSIT/Business Information Services Support

(BIS)

Email: [email protected]

or check out NSIT/BIS Website(http://nsit.uchicago.edu/groups/bis/)

For additional information check out chapter 16of the Data Access and Analysis (PDF) fromBusiness Objects on the BIS website
mailto:[email protected]://nsit.uchicago.edu/groups/bis/http://nsit.uchicago.edu/groups/bis/http://nsit.uchicago.edu/groups/bis/bo_docs/xir2_deski_access_analyze_data_en.pdfhttp://nsit.uchicago.edu/groups/bis/bo_docs/xir2_deski_access_analyze_data_en.pdfhttp://nsit.uchicago.edu/groups/bis/http://nsit.uchicago.edu/groups/bis/mailto:[email protected]

irf formulas

Documents