Report Manager: Introduction

Created by Erik Åkerlund, Modified on Mon, 20 Feb 2023 at 02:29 PM by Erik Åkerlund

Report Manager - Introduction

The CRM Report manager contains functions for creating reports with a large number of different characteristics. There are six main report types:

Transaction List (transaction lists, accrued interest and FX gain/loss calculations).
Cash flow (cash flow, CF forecast, net present value calculations, yield curve simulations).
Balance/Position (balance sheet, unrealised interest and gain/loss, FX position and risk analysis).
Profit/Loss (Realised and unrealised interest, FX gain/loss and Interest rate gain/loss).
Market valuation (unrealised interest, interest rate gain/loss and FX gain/loss).
Interest Rate Gap. Interest period and credit period gaps

The report generating function consists of six sections (tabs) with which you design the report. The six tabs are: Selection tab, Design tab, Settings tab, Advanced tab, Simulation tab and Limit tab.

The Selection tab is used for selecting the transactions to be processed in the report. The selections are made with three criteria, i.e. Dates, Portfolio codes and Reference numbers. In the selection tab you also select the FX and interest rate registers to be used for the calculations in the report.

The Design tab is used for determining the desired columns for the report and to decide the sorting orders.

The Settings tab contains a number of switches and alternative ways that the system can treat the transactions when computed.

The Advanced tab contains options for yield curve simulations and transaction grouping into time buckets.

The Simulation tab contains options for budget simulation functionality in loan administration.

The Limit tab is used to set up amount limits.

When Selections, Design and Settings are completed for the report you want to generate it is all saved under a Selection name. The report can then be reproduced again by searching in the selection name box and running the report again. It is recommended that when you run the report again you only make minor alterations such as dates and FX / Interest table selections. If you frequently want to alter Portfolio selections, Column selections and settings, it is recommended to copy an existing selection name and save a new report under a different name.

To create a new report click on the create selection icon in the toolbar. Enter a name for the report and choose report type

Selection tab

Report type: Transaction list

Date as at, Measuring period, Deal date, Value date, Maturity date - are all report type determines date selection options.
Selection by status: If left blank transactions with status REG, CONFIRMED and COMPLETED will be selected. SIGNED = keep track of returned signed confirmations. DELAYED, PENDING and EXPORTED are all status regarding payment status.
Transaction list is for example used for seeing one single instrument with all its transactions or all FX deals done during a certain period (selection on Deal date from and to).

Report type: Cash Flow

Amounts "Due" only available in Report type cash flow. Date due automatically selects all cash flows falling due within the Date Due interval.
The 'Date as at' serves as selection criteria for active transactions available at the chosen date.

Report type: Balance/position

The 'Date as at' serves as selection criteria for active transactions available at the chosen date.
No realized amounts are shown in a report of type B/S, only unrealized amounts.

Report type: Profit/loss

Date as at and measuring period serves as selection criteria. Use this report when you want to analyze realized or both realized and unrealized transactions in the same report for a certain period. For analysis of unrealized transactions only use Report type Market valuation or report type Balance/Position

Report type: Market Valuation

The Date as at, serves as selection criteria for active transactions available at the chosen date. This report can be used for unrealized market values.

Report type: Interest Rate Gap

The Date as at, serves as selection criteria for active transactions available at the chosen date.
Report Columns started with Gap shall be used in the Design tab. Important to set up a period structure in Advanced tab. Choose as first sorting order, for example Int bound period.

Design tab

How to select columns

Sorting order is provided in five levels. Check Sum and Page break boxes at preferred sorting order levels. Sorting order headings are placed as left hand columns in all reports when check box ‘Print’ is checked.

If a folder path is selected in “Alt. Output folder”, the report will be saved in the selected folder when you click the button “run”.

An Alternative filename can also be set by typing the name in “Alt. Filename”. The report will be saved with the typed name when pressed button “run”.

Select reports columns. Choose and mark a row in Column Selection Available and click on the button or click on LOV (List of values) and choose report column from the list. In order from up and down the selected columns are listed in the report from left to right. Click on the Up or Down arrows to move selected columns in left /right direction in the report.

Parameter	Description
Report Selection	Activate / deactivate printing of report selection criteria in the report.
Headings	Activate / deactivate column headings in the report. Primarily useful when data is further processed in Excel reports
Output format	Select Text, MS Excel, Html, PDF or Report Viewer
Sorting order, Arrows	Select ascending/descending sorting order
Sorting order, Sum	Activate / deactivate sum totals
Sorting order, Print	Show/hide as separate column
Sorting order, Page break	Activate / deactivate Page break.
Sorting order, Limit	To set a limit definition. Run report and than enter the limits in Limit tab.
Column selection description	Column description. Displays help on selected column. Colored blue if the description consists of more than one row. Double click to see full content.
User defined column names (available for CRM Edit formatted and MS Excel):	Click to enter your own column headings (or right click on selected column). You can choose to enter the heading in 1, 2 or 3 rows. Each row can contain 30 characters. If a selected column has a user defined heading the icon is colored green.

Settings Tab for Report Manager

In the Settings tab you fine tune your report with special features that are specific for certain situations and instruments in different kinds of reports. The Settings tab will show different available check boxes depending on the Report type selected.

Lines/page:
Number of lines per page.
Currency Prefix:
Net present value calculations in the Report manager for FX transactions. These curves must be available in the Interest/Price/Volatility tables for each currency included in the selection for the report.
The system requires that the interest code for each curve contains at least three positions representing a currency code i.e. USD, EUR etc. If you have labeled your yield curves DEPSEK, DEPEUR etc then your prefix equals DEP (type in Currency prefix field DEP).
Floating interest rate:
1=Print all registered floating int. transactions in int. rate swaps/amort.loans. 2=Like 1 but with forward interest from yield curve. (Setting ‘1’ or ‘2 =Variable amortisation loans - Capital amount discounted from final maturity date. Setting ‘blank’ = Variable amortisation loans, Capital amount discounted from next fixing date.)
Instrument categories:
Is a selection of the main instrument categories, i.e. Interest bearing, FX, Commodity, Energy, Forecast, Funds/Equity
Trans types:
Spot = On balance, Forward = Off balance, Total = Spot and forward, Prematurely closed
Express duration in:
Select how duration (DTM, DTFM), int. bound to, accrued days should be displayed in reports.
Years= Everything displayed in Years, E:G: 0,236 - 1,2 - 5,6
Days= Everything displayed in days, E:G: 185 - 720 - 3600
Days/Year = Up to one year in days. More than one year in Years, E:G: 85 - 327 -1,5 - 6,5
Bank account balances:
Available in Report Type Balance/Position. The selection activates inclusion of bank account balances if such accounts exist. Legal entity, Counterpart and Currency are the only selection criteria that can affect the bank account selection. Top+other’ includes balances for top accounts in cash pools and other accounts. ‘Sub+other’ includes balances for sub accounts in cash pools and other accounts. All includes all accounts. If only Bank accounts shall be included in the report, type in Reference no field 1 to -1.
FX Transactions:
Accrued interest: The program will allocate swap points in FX forward contracts to the measuring period in relation to accrued days in the period as proportion of total days in the contract.Normally used for hedging contracts held to maturiy.
Interest gain / loss: The program will find the market valued swap points to maturity in actual currency rate table, calculated from date as at, and compare with contracted swap points. The difference represents the market valued interest gain/loss in the contract. Normally used for trading contracts that need market valuation.
Acc int and Int G/L: The program will both allocate swap points as accrued interest and do a market valuation of swap points. Report columns accrued interest unrealised and Int G/L unrealised needs to be included in the report.
Note: You must choose one or the other method.

Parameters	Description
Totals only	Check in order to display Totals Only
Conversion alternative	Check if valuation shall be done at Spot at maturity of transaction if it exists.
Net present value	Check in order to get calculation of NPV, Net Present Value.
Closing transactions	Checked=Realised at maturity date. Blank=Realised at spot date
Suppress duplicates	Check to print sorting order headings only on the first transaction in the list for each new sorting variable.
Include bought/sold coupon	Check to include Bought/sold coupon in Accrued Interest.
Deal date only on forecast trans	Select on deal date only on forecast transactions.
Amortisation of bond prem/disc	When the box is activated the interest gain/loss calculation in the report will provide the amount to amortize in the measuring period. The system will not use interest rates from interest rate tables. The contracted yield in the transaction will instead be used at both start and end of measure period for the IR gain/loss calculation.
Use settlement power transactions	Checked = Market valuation for Electricity derivatives calculated using settlement transactions. Not checked = Market valuation for Electricity derivatives calculated on head transaction only.
NPV of all CFs with zero coupons	Checked = Market valuation is done by net present values of all cash flows. Zero coupon definitions must be defined in Register menu. Applied to Fixed floating rate loans, Credit Facilities, Leasing Loans, Interest rate swaps and Danish Mortgage Bonds.
Split syndicated loans	Checked = Include syndicated loans in the report where closing balance and interest will be distributed among the counterparties in the syndicated loan. Each counterparts share are entered on the instrument (Syndicate Group button).
Display NCU	Check to display national currency units for Euro currencies.
Coupons	Check to include coupon transactions.
Mid rates	Check if Mid Rates shall be used.
Multinational cons.	Use for forecast exposure. The Multinational consolidation switch will provide two separate lines “Non Local exposure” (currency of the forecast transaction) and “Local exposure” (home currency of the Legal entity).
Rate table	Check if realized FX G/L calculations should be done with Rates from a Currency rate Table instead of spot at maturity rate set in Back office confirm close.
Brent equivalents	Used when running reports with Commodity instruments
No spread discount	Checked = Margins (spread) on base interest for variable leg on amortization loans and IR swaps are not discounted in IR G/L calculations.
Excl. power fee tran.	Exclude fee transactions for power contracts.
ACE on derivatives	Checked = Average capital employed used on derivatives
Use last fixing	Use last fixing when calculating future interests for Caps and floors and variable loans and swaps.
All time buckets	Show time buckets even if there is no value.
Adj days to maturity	Check for adjusted days to maturity on fixed floating rate loans with amortisation schedule.
Calendar days DTFM	DTFM (displayed in years) calculated without considering interest convention denominator on the deal.
All bank acc balance	Check to include bank account balances with zero balance.
Ownership %	Function activated in System parameters. Function enables to set ownership % on Strategy codes. All amounts in reports adjusted according to percentage set on strategy codes.
Turnover	No signs on any number. All transactions are added irrespective of buy or sell amounts
Exact rates	If checked, no rounding of FX rates in calculations. Not checked = FX rates rounded according to settings in currency code/structure
FRN act int	Checked = Effective interest calculated from actual coupon interest and Interest % original is showing actual coupon interest.
Eff int as IRR	Checked = Effective interest on fixed instrument calculated as the internal rate of return for series of cash flow.
Caps/Floors adjustment	Checked = Caps/Floors are included in calculation of duration and DTM.
Print all totals	Checked = Total lines printed also when a sum total only includes one single transaction.
Include IRS nom amount	Checked = Nominal amount for interest rate swaps are included in Capital amount due column. Used for example in hedge reports if existing loans have amortisation schedule.
Excl deactivated bank accounts	Checked = Deactivated bank accounts in Portfolio Codes Details are not included.