Using Microsoft Excel Functions in IFS Business Analytics
IFS Business Analytics is a tool which will enable you to use Microsoft Excel
as a business intelligence tool which can be used to design and analyze reports
using information from IFS Applications. It is possible to use most of the Microsoft
Excel features within IFS Business Analytics. The purpose of this document is to
overview some of these features and provide you a guide on Microsoft Excel with
IFS Business Analytics.
This document includes some Microsoft Excel functionalities that are fully supported
by IFS Business Analytics, partly supported by IFS Business Analytics with limitations
or not supported at all. If you need more information on a certain Microsoft Excel
feature, refer Microsoft Office online help. This document is intended for use by
an advanced user of IFS Business Analytics.
Microsoft Excel functions in IFS Business Analytics
Microsoft Excel features in IFS Business Analytics
-
The Lookup function searches for a value in the first column of a table and
returns a value in the same row from another column in the table. The Lookup
functionality is supported by IFS Business Analytics in all relevant conditions.
Lookup is supported within the same sheet, between work sheets and between workbooks.
It is possible to use VLOOKUP (vertical lookup) and HLOOKUP (horizontal lookup)
within IFS Business Analytics.
Example: Retrieve the budgeted values for accounts from the fixed
table above to the table with the repeater (cell E15) for the respective
accounts. This formula can be given to a fixed table in the same sheet or
a fixed table in a different sheet.
VLOOKUP(A15,$A$5:$B$8,2,FALSE)
Example: Retrieve the budget values of an account from a repeater
to a fixed table.
VLLOKUP(E6,A:D,FALSE)
-
Lookup for a repeater in a different sheet
Example: Retrieve the budget values for specified accounts
from a repeater to a fixed table in a different worksheet.
VLOOKUP(A7,Sheet1!&A$12:$D$14,4,FALSE)
-
Lookup for a repeater in a repeater in the same sheet
Note that the lookup function is not possible to be performed for
a repeater in a repeater when the repeaters are in different sheets.
-
Lookup for a column repeater
-
The Offset function returns a reference to a range (specified number of rows
and columns from a cell or range of cells). The reference that is returned can
be a single cell or a range of cells. You can specify the number of rows and
the number of columns to be returned.
Offset and Sum Offset in IFS Business Analytics works similar to Microsoft
Excel. It can be given to fixed tables or a repeater in the same worksheet,
between worksheets or between workbooks. Always the reference cell of the formula
need to be a cell out side the design row/column.
Example: In the following figure, the Offset function is used to return
the value containing in a particular location given in the formula. For example,
OFFSET (D6,1,1,1,1) is interpreted as (start from cell D6, go down one cell,
go right one cell, take 1x1 area from the current location). The resulting cell
in this case will be E7 and the value returned, therefore will be 2000.
The Sum Offset function is used in the figure to return the total of the cells
included in a particular location. For example, SUM(OFFSET(D6,1,1,5,1) will
select a 5x1 area from cell E7 (same logic above) and return the total of all
the values in the cells included in that area.
-
The Match function searches for a specified item in a range of cells, and
then returns the relative position of that item in the range. A range is defined
as two or more cells in a work sheet and they can be adjacent or nonadjacent.
Match function in IFS Business Analytics works similar to Microsoft Excel.
This function can be given in the same worksheet, between worksheets or between
workbooks. Match function is used to identify the position of an item either
in one row or one column. Therefore, the Match function cannot be used in a
range that includes more than one row or column, i.e. it cannot be used when
a fact is given both a row repeater and a column repeater. When using
the Match function with inner repeaters, the formula needs to be given in a
way that the array includes the row before and after the design row.
Since sorting is not supported in IFS Business Analytics when executing the
report to get the closest higher/lower value, the formulas need to be given
in the end user mode. In the Designer mode, sort ascending/descending happens
as per the repeater row criteria. It is not possible to give further ascending/descending
instructions for any other column/row. Therefore, only exact matching is facilitated
via the designer mode. If you need to search for a value less than or more than
the lookup value then, you need to sort the data ascending/descending in the
executed mode or else you will only get the last matching item as the result.
Example: In the following figure, initially the Match function is
used to locate the position of an exact account balance. You can also see that
the Match function is then used to locate the position of an account balance
which is the closest to 1000.
When using to match with an inner repeater, the formula needs to be given
in a way that the array includes the row before and after the design row.
Example: In the figure below, the design exist from row 6 to 7, so
the Match function formula is given to include rows immediately above and below
the design rows (row 5 to row 8).
-
The Transpose function returns a vertical range of cells as a horizontal
range, or vice versa. Transpose must be entered as an
array formula in a range that has the
same number of rows and columns respectively. Use Transpose to shift the vertical
and horizontal orientation of an array in a worksheet.
The Transpose function in IFS Business Analytics works similarly to Microsoft
Excel. When you execute a report you need to go to the Transpose Result and
highlight the desired location where the result is required to be displayed.
Then press F2 and then press CTRL+SHIFT+ENTER to get the desired result.
Example: The following figures show how the Transpose function is
used to convert a column repeater displaying balances for the cost of premises
(each month's cost in separate column) to rows displaying the same (each month's
cost in a separate row) .
-
The Subtotal function is used to return a subtotal for a list. It is generally
easier to create a list with subtotals using the Subtotals command in the
Data menu. Once the subtotal list is created, you can modify it by editing
the Subtotal function.
In IFS Business Analytics, the Subtotal formula can be given in the designer
mode and executed, but the formula needs to be edited in a way that it will
only include one cell as the reference cell of the formula. When executed in
the end user mode it will expand appropriately and will be displayed in the
correct ranges.
Example: The following figures show how the Subtotals function is
used to calculate the amount totals per each account group and account type
in the accounts list.
Note 1: In order to get the summery rows below the details, go to
Data/Group and Outline/settings and change the settings accordingly.
Note 2: Grouping is also supported in IFS Business Analytics. i.e.
what is grouped in the designer mode shows as a group in the end user mode.
As mentioned in Note 1 the settings can be changed for grouping as well.
-
To prevent anyone from accidentally or deliberately changing, moving, or
deleting important data, you can protect a certain
worksheet or workbook elements, with
or without a
password. Microsoft Excel passwords can be up to
255 letters, numbers, spaces, and symbols. All passwords entered are case sensitive.
Excel protect sheet/cell or workbook is supported with limitations in IFS
Business Analytics. When you protect the design of a report, the end user mode
is also protected. Protection can be removed in both design and end user modes.
Protection will enable/disable editing for formatting and editing ranges. This
work similar to Microsoft Excel. Protection with password is not currently supported
in IFS Business Analytics.
-
Filtering is a quick and easy way to search and work with a subset of data
in a range. A filtered range displays only the rows that meets the criteria
you specify for a column. This is referred to as a filter criteria. The Auto
Filter function is not supported currently in IFS Business Analytics.
-
You can view two areas of a worksheet and lock rows or columns in one area
by splitting or freezing the panes. When you split panes, you will be able to
scroll in both areas of the worksheet, while rows or columns in the non-scrolled
area remain visible.
Freeze pane is completely supported in IFS Business Analytics. When freeze
pane is applied in the design mode and executed, the end user mode also
shows the applied freeze pane.
-
General Microsoft Excel formatting such as bold, color, underline, font,
border, alignment are allowed in IFS Business Analytics. However, When using
Microsoft Excel 2003 column repeater should not expand for more than 256 columns.
-
When you perform an action in Microsoft Excel either by entering some contents
into a cell or formatting of cells you can reverse this action by Undo or you
can re-perform an already undid action by using Redo. You can undo and redo
up to 16 actions in Microsoft Excel.
Undo and Redo is not supported in IFS Business Analytics designer mode except
for any formatting done such as bold and highlight. However when using Microsoft
Excel 2007 a single undo is supported in the designer mode. However, both Undo
and Redo are fully supported in IFS Business Analytics End User mode.
-
The formulas relating to the drag and drop contents can be viewed using this
feature. You can switch between displaying formulas and their values on a worksheet.
Following shortcuts can be used:
-
Press CTRL + ` (grave accent)
-
In Microsoft Excel 2003: Tools – Options – View – Window Options –
Formulas
-
In Microsoft Excel 2007: Excel Options – Advanced – Display options for
this worksheet – Show formulas in cells instead of calculated results
This feature is fully supported in IFS Business Analytics. In the design
mode when you press CTRL + ` (grave accent), it will show the display items
used in the design. Once executed it will still show in the formula mode. You
need to press CTRL + ` (grave accent) to revert to the normal mode.