Create a report in TS from a template, run it, and change its settings: the date, the selection, the columns, the rates and how the report runs.
Create a report
Go to Middle Office › Reports › Reports.
- Select Add Report.
- In Create New Report, enter a name in the Report Name field.
- Choose the Category the report belongs to.
Select a template from the Template list.
- Select OK.
Report templates
A template is a report with predefined options, depending on the template: filters such as dates, deal types and tags on the Selection tab, and result columns on the Columns tab. It gets you started, and you can then adjust the report, for example by adding or removing columns or changing the dates and other settings.
These standard templates are included in TS from the start. You can change them and add your own templates.
- Template Balance Position: the outstanding position of deals on the balance date you enter, with the accrued interest and expenses and the unrealised profit and loss at month end. Compare it with your balance sheet.
- Template Profit and Loss (Allocated): the result of the previous month. Compare it with your profit and loss statement.
- Template Cash Flows: all cash flows for all financial deals between the cash flow dates you choose.
- Template Currency Exposure: the currency exposure, or position, per day in each currency. With the Pivot report viewer, each currency is shown in its own column.
- Template Maturing Deals: all deals that mature by the deal end date.
- Template Expected Interest expenses: the expected interest expenses between the start and end of the measuring period, based on the yield curve you choose at the start of the period.
- Template Market value: all future cash flows for FX, interest rate swaps and loans (amortisations and interest payments), with the discounted cash flow, the implied forward interest rate and the total market value.
- Template Market value FX: the market valuation of FX transactions, with the market information.
- Template for Multiple Bank Balance: historical and forecasted bank balances for a specific period.
- Template for Accounting Report: an empty template for an accounting report.
- Template Cash Flow Forecast: a report template for a cash flow forecast.
- Template Foreign Exchange Exposure Report: an empty template for a foreign exchange exposure report.
- Template Risk Simulation Report: an empty template for a risk simulation report.
Categories
Every report belongs to one of these system categories:
- General
- Risk
- Position
- CashFlow
- MonthEnd
- Reconciliation
You choose the category when you create the report. To move a report to another category later, drag it there. Categories only help you group and find reports; they don't change what a report can do. You can also set up your own categories for reports.
You can see all system reports here, including the special reports that other views use, such as Confirm, All Transactions and All Cash Flows. Be careful if you change them from here.
Run a report and change it
- Select Run. The report is shown in the grid.
To change the columns or the selection, select Columns or Selection in the toolbar. Report Settings opens on that tab.
- Make your changes and select Save.
You can also remove a column straight from the grid: right-click the column name and remove it.
Report Settings has five tabs: Overview, Selection, Columns, Rates and Settings. They are described below.
Overview tab
On the Overview tab, set or change the date or period for the report; which dates you set depends on the report type. The tab also shows the report type and a summary of the options you have selected. Choose how the report is shown in the Report Viewer field, for example Grid, Pivot or Power BI.
Report types
- Profit and Loss: income and expenses allocated to the measuring period. Shows both open and matured transactions within the period.
- Balance Sheet: all open transactions on the balance date. Matured transactions aren't shown.
- Cash Flow: the cash flows for all transactions during the cash flow period.
- Transaction List: all transactions. More than with the other report types, you choose which transactions to include in the report.
Report viewers
- Grid: the report as a table. You can sort, change the column order, and group and sum on different levels.
- Pivot: group and sum on both the X and the Y axis.
- Power BI: exports all the report's data to Power BI. There you can build a report layout with tables, graphs, text boxes and slicers.
The Power BI dataset gets the same name as the report in TS, and you can't change the dataset's name in Power BI. If you rename the report in TS, it can't find its dataset when you run it.
Measures (your own calculations) are only available in Power BI Desktop, not in Power BI online. If the report needs a measure, start by importing the data into Power BI Desktop and build the report there.
Selection tab
On the Selection tab, choose what the report includes. You can select on dates, company, deal information, transaction information, tags, ID number, and the entity's and the counterparty's bank accounts.
Columns tab
The Columns tab lists all the columns in the report, with each column's settings, such as header, format, allocated, consolidated, realisation mode and aggregation type. To add or remove a column, select Add Column or Remove Column.
- Allocated: when selected, the column shows the result for the chosen period, between the start and end of the measuring period. It applies only to profit and loss columns and to the two date columns, period start date and period end date.
- Consolidated: choose a currency here to show the column's amounts converted into that currency. If it's empty, amounts are shown in their original currency. To show both, add the column twice with different settings.
- Aggregation Type: the kind of total the column uses. The most common are Sum, Average, Min, Max and Weighted Average.
Weighted average
Weighted Average is calculated as:
SUM(column * Weight Column) / SUM(Average Denominator)It uses two more column settings, which apply only when the aggregation type is Weighted Average:
- Weight Column: the column each value is multiplied by (weighted against).
- Denominator Column: the column whose sum the weighted sum is divided by. This is normally the same as the weight column.
If you change a column that uses Weighted Average, select Save and then Refresh to load the new setting.
Weighted average in Power BI
Only the data is exported to Power BI, not the aggregation formulas such as Weighted Average, Sum or Min. To show a weighted average in a Power BI report, add a measure in Power BI Desktop. For example:
Weighted Average Interest = SUMX('Weighted average interest';('Weighted average interest'[Nominal Interest]*'Weighted average interest'[Base Amount])/SUM('Weighted average interest'[Base Amount]))Rates tab
The report uses the default rate tables for the dates on the Overview tab (the start and end of the measuring period). If needed, choose other rate tables in the lists on the Rates tab. To use mid rates, select the mid rates checkbox.
Settings tab
Trade date or settlement date
- Settlement Date (the default): on-balance instruments, such as loans, deposits and bank accounts, are shown from the cash flow date (settlement date). Off-balance instruments, such as interest rate swaps and FX forwards, are shown from the deal date (trade date).
- Trade Date: on-balance instruments are also shown from the trade date (deal date).
To show interest rate swaps from their start date, like loans (the day interest starts to accrue), instead of from the deal date, set Deal Begin Date to "same as balance date". Loans and interest rate swaps then appear only from their begin date, while FX deals still appear from their deal date.
Report technology
There are two report technologies: Server report and Reports.
Server report: runs on Treasury Systems' servers and exports all the data. A server report can calculate on information that isn't included in the report. These columns work only in a server report:
- Facility used
- Facility unused
- Default Limit, which uses Facility used, only when the report includes credit facility and calculated fee transactions TS calculates the used amount of a credit facility on the server, so it doesn't need to send every tranche to your computer. Power BI reports are always server reports.
- Reports: runs on your computer. To get new deals and real-time prices without selecting Refresh, run the report in real time (see below).
Real time transactions enabled
When Real time transactions enabled is selected, the report updates as soon as a deal is entered or changed, so you don't have to run it again. This is useful for position reports, today's deals or real-time market value reports.