Adding a Custom Excel Based Report in SAS Solution for IFRS 17

1 Like

Introduction

SAS Solution for IFRS 17 uses pre-configured MS Excel based reports for reporting financial information for internal or external consumption. Authorized users are enabled to generate and review any (preliminary or definitive) disclosure reports in format of MS Excel as needed. The objective of the article is to provide developers or implementers an overview of mechanism of these reports and high level steps to add and test new reports. The article assumes that you have a basic knowledge of SAS Solution for IFRS 17 on SAS Risk Stratum and you know basic skills to update or modify SAS tables.

You can obtain more information on SAS Risk Stratum and SAS Solution for IFRS 17 through the following training programs.

Data Preparation, Configuration, and Administration in SAS® Risk Stratum

Business Overview of SAS® Solution for IFRS 17 in SAS® Risk Stratum

A Brief Idea of Excel Based Reports

The solution enables you to generate and review preliminary financial reports using the subtasks of Generate Initial Financial Reports and Review Initial Disclosure Reports under the main task of Prepare Trail Balance in the default workflow for a typical IFRS 17 based accounting cycle. A similar set of subtasks (Generate Financial Reports and Review Disclosure Reports) are available in Finalize Postings task of the workflow.

The mechanism of generating the reports is same in both the tasks. However, the

reports generated in the subtasks of Finalize Postings are based on approved and finalized accounting entries and hence the reports are definitive. There are various reports available. The following snapshot provides a list of reports that can be generated.

List of reports with names used in the solution.

Select any image to see a larger version.
Mobile users: To view the images, select the "Full" version at the bottom of the page.

In the solution a user can follow simple steps to generate these reports. Following is a brief summary of steps needs to be performed:

For Generate Financial Reports:

Sign on to SAS Solution for IFRS 17 as a user with appropriate role such as the IFRS17: User role.
Click the relevant cycle in the "My Tasks & Notifications" section of the homepage, or in the Cycle summary table of the cycles list, to view details of the cycle.
In the project details page, under Finalize Postings select Generate Financial Reports.
Change any Model Parameters if necessary:

Choose the Logging Level. The default value is INFO (minimum logging).
Enter the Script timeout. The default value is 3600 (seconds).
Select the Chart of Accounts that you wish to use to produce the disclosure reports.
Specify whether you want to re-execute the job flow instance. The default value is N.
Choose the Report Interval. The default value is YEAR.
Choose whether to leverage the reporting entity dimension to build a reporting hierarchy with the option “Use Reporting Hierarchy", the default is No.
Select the set of Reports to run. Select at least 1 report to be able to move on to the next task. Should you not want to produce any reports, select Mark complete from the workflow actions button.

Click the Workflow Actions button and select Generate Financial Reports to execute the task.

For Review Financial Reports

The user can analyze all the disclosure reports prepared in previous step and go back in the process if required. Otherwise, the step can be marked as completed.

Sign on to SAS Solution for IFRS 17 as a user with the IFRS17: User role.
Click the appropriate cycle used to generate reports in the "My Tasks & Notifications" section of the homepage, or in the Cycle summary table of the cycles list, to view details of the cycle.
In the cycle details page, select Review Disclosure Reports. Notice the links available under the Review tab. These are:

Initial Disclosure Reports – Clicking this opens an analysis run and each report can be downloaded individually.
Download all reports – Clicking this creates a Zip file containing all the selected reports.
Control Framework Report

Click the Workflow Actions button and select Generate Additional Reports to return to the previous step or Approve to continue.

Steps to Add a New Report

The solution is quite adaptable when making adjustments to these reports. Customizations are possible at various levels. You can choose to modify existing layouts of the reports or create a new report altogether. The steps that are required to be carried out are simple however the amount of work in each step depends on your requirements. The following graphic provides an overview of steps.

General overview of steps for custom reports

1. Create or Modify

At this step, you start with an existing Excel template. Quite often the required changes may involve only changing the layouts or titles in the report. Simple skills in Excel enables you to carry on the desired changes. Note that all the Excel templates follow a similar structure. Every Excel template has three sheets. These are :

Report - This sheet has the final report. It usually have simple or pivot tables that showcase the results calculated from the configuration sheet.
Input - This sheet has raw data populated by a SAS code.
Configuration - This sheet carries formulae and aggregation logic involving input data.

You can choose to make a simple changes in the Report sheet like changing the titles, adding a chart and so on. Or you may want to change the input data in the Input sheet or configuration logic in the Configuration sheet depending on your requirements. Here is the summary of skills required.

Target Sheet	Customization Possibilities	Desired Skills
Report	Report Layouts	Basic MS Excel skills involving tabulation, pivot tables and charts.
Configuration	Logic	Advanced MS Excel Skills involving application of simple and advance aggregation formulas across sheets.
Input	Data Extraction	Advance programming skills including SAS SQL, SAS Macros and SAS Data step programming.

The following snapshots provide an example for the three sheets.

A sample of Analysis of Revenue Report. The sheet involves simple tabulation of numbers related to revenue.

In the above snapshot the Report sheet presents a simple table showcasing relevant number for amounts related to liabilities for remaining coverage, recovery of acquisition cash flows and insurance revenue. The values are based on the logic defined in the configuration sheet. Notice that you are free to change the titles, colors , font and so on in the underlying template. You may add a chart or pivot table of your choice.

The configuration sheet for Analysis of Revenue report. The sheet involves formulae for reading and aggregating data from the input sheet.

The above snapshot illustrate the usage of SUMIFS Excel formula that reads and aggregates values from the Input sheet. This configuration sheet can be modified for its formulae. You may add more configurations based on your requirements.

An example of the Input sheet for Analysis of Revenue report. This sheet holds the raw data.

The above snapshot presents a portion of the input sheet. Note that it is based on the data extracted from SLAM mart tables of the solution. A predefined but customizable SAS code populates this input sheet.

Note that all the Excel templates can be found at a predefined location in the federated area. An example of such a location is as follows:

D:\SAS\Config\Lev1\AppData\SASIRM\fa.ifrs17.yyyy.mm\irm\report_templates

The following snapshot illustrates simple changes made to the template Insurance_Revenue.xlsx.

An example of simple customizations

2. Update

The SAS code responsible for populating the input sheets of the excel based report is available in the landing area. The name of the code is irmif_run_disclosure_reports.sas. An example of a typical location of this code is as follows:

D:\SAS\Config\Lev1\AppData\SASIRM\\fa.ifrs17.yyyy.mm\irm\source\sas\ucmacros

If you have created a new Excel template with your own inputs, you must provide the logic for extracting the data in the SAS code. If however, you have made changes only to the configuration or report sheet, then no updates are required in the SAS code. This code is long, complex and it creates many intermediate tables. The good thing is that it has got comments which help you to look at the portion of the code that matters to you. You need an advanced level of SAS programming skills to make, validate and test any changes.

For example, if you need to have additional column in the Exposure_to_Currency_Risk report. You must modify the KEEP statement in the data step for MKT_FX_1 table. Here is a snapshot of the required portion of the code. Refer to the %let statement followed by a data step in the second part of the code (lines are marked in boldface).

/* create the FX_MKT table for the Exposure_to_Currency_Risk report*/
%let where_clause_FX = %bquote((asofdate = input("&cycle_date.",date9.) or asofdate = 
&END_PREV. ) and ENTITY_ID="&ENTITY_ID." and workgroup="&workgroup." and to_currency_cd = 
"&curr.");
%irm_check_details(libref = SLAMMRT
, table_name = MKT_FX
, where_clause = &where_clause_FX.
, schema_version =&content_version.
, table_id = 0
, out_ds =MKT_FX_0);%let keep_fx_1 = From_currency_cd To_currency_cd Quote_dt TYPE quote_rt;
Data MKT_FX_1 (keep = &keep_fx_1. ) ;
retain &keep_fx_1.;
SET MKT_FX_0;
if quote_dt = input("&cycle_date.",date9.) or quote_dt = &END_PREV.;
format quote_dt date9.;
run;/*End of creating the MKT_FX table*/

3. Add

You must add your new report to the table REPORT_CONFIG.SAS7BDAT. The table is available in the static folder of your landing area This is an example of the location -

D:\SAS\Config\Lev1\AppData\SASIRM\fa.ifrs17.yyyy.mm\irm\landing_area\base\ifrs17.vmm.yyyy\static.

The following table shows the content of the table.

The configuration table. Notice the variable and their values.

You can edit the table to add a new entry for your custom report. Following is a sample of an entry.

Variable	Values for a New Report	Comments
REPORT_ID	Revenue_Chart	This variable stores the unique values for your report IDs. You must provide a unique value to your new or modified report.
REPORT_NAME	Revenue_Chart	This variable stores the unique names for your reports to be used in the UI.
TEMPLATE_NAME	Revenue_Chart.xlsx	This is the name of the template of the newly created or modified report template.
SOURCE_CODE	irmif_run_disclosure_reports.sas	This is the name of the code responsible for populating the Input sheet in your Excel template. The code is either remains unchanged in case of simple report customizations in the report sheet or gets modified in case of advanced configuration requirements with additional columns added to the input sheet.

The following screenshot illustrates an example of the update done to the REPORT_CONFIG table.

An example of an update made to the REOPRT_CONFIG table.

4. Execute and Test

The steps to execute and review the report is same as described in the previous section - A Brief Idea of Excel Based Reports . Execute the task by selecting your newly created report. Download and review for any errors or logical inconsistencies. Here is an example of a successfully executed report with desired numbers.

A custom report. Users must test the validity of numbers.

Post Activities

The four steps described above adds the new report to the solution. The new report is available to be used in any new cycle you create for testing or direct usage. It is recommended to test the report by creating a few new cycles on a test data.

Conclusion

The article presents a series of steps to add a new or customized Excel based report to the SAS solution for IFRS 17. These steps enable you to try out and test several new reports (or any customized existing reports) based on your requirements. Such steps might be beneficial in the development and implementation stages of SAS Solution for IFRS 17. For a working demo of above steps refer to the training program Technical Overview of SAS Solution for IFRS 17.

Find more articles from SAS Global Enablement and Learning here.