AWS Business Intelligence Blog
Automate financial statements using Amazon QuickSight Snapshot Export APIs
Financial statements are a set of documents that contain formal records of financial activities of a business at a specific point in time. For example, a profit and loss (P&L) statement summarizes the revenues, expenses, and resulting net profit or loss of a business. It is a snapshot of the organization’s financial performance, starting from the organization’s total revenue, followed by the deductions for the costs of goods sold and operating expenses. By analyzing these profits, business leaders are able to review the health of the organization, which helps determine where the business is now and where it can go in the future.
As transaction volume and revenue data increase over time, organizations need ways to self-regulate, design, and generate financial reports to help stakeholders make the right business decisions. Reporting is challenging due to the amount of time and resources it takes to generate and store the right documents. Bookkeepers face this risk when reporting on an organization’s performance data, having to make sure records are timely, accurate, and reliable.
In this post, we go over how to automate the creation and storage of highly formatted financial statements for business users to catalog and review. The solution uses Amazon QuickSight, a serverless and cloud-native business intelligence (BI) service; Amazon Simple Storage Service (Amazon S3), an object storage service; AWS Glue, a serverless data integration service that makes it easier to discover, prepare, move, and integrate data from multiple sources for analytics, machine learning (ML), and application development; and Amazon Redshift, a fully managed data warehouse service. Although this post focuses on a P&L statement, the approach can be generally applied for all reports generated in batch, such as running reports repetitively for different filter values.
Snapshot Export APIs
To generate, catalog, store, and archive these reports automatically, we use the recently released QuickSight Snapshot Export APIs feature, which allows developers to automate the multi-step process of curating data, customizing reports, generating reports, and storing them to a secure location. The APIs are as follows:
- StartDashboardSnapshotJob – Starts an asynchronous job that generates reports for a dashboard. In one API call, you can request up to five CSV reports for table or pivot table visuals and up to one paginated PDF report.
- DescribeDashboardSnapshotJob – Describes the status and configuration of the snapshot generation job started with the StartDashboardSnapshotJob API.
- DescribeDashboardSnapshotJobResult – Describes the result of a completed snapshot job. If the job has not finished running, this operation returns a message that says Dashboard Snapshot Job with id <SnapshotjobId> has not reached a terminal state.
Prerequisites
For this post, the following prerequisites are required:
- Author access to a QuickSight Enterprise account with the paginated reports add-on enabled.
- AWS Identity and Access Management (IAM) permissions are needed in order to run the APIs. The following is the IAM policy that allows IAM users access them:
To create highly formatted paginated reports in QuickSight, use the instructions in the following workshop and proceed to the automation outlined in the Automate the generation of reports section.
Use case overview
Generating monthly financial statements are crucial for organizations to comply with regulations, communicate with stakeholders, maintain financial control and transparency, evaluate performance, and plan for the future. As data volume grows, organizations face operational and performance challenges in generating accurate month-end accounting and financial reports for all their financial entities in a timely manner.
P&L statements are shared with various stakeholders of the organization. They are shared with upper management for internal decision-making and determining the strategic vision and to business suppliers to demonstrate financial stability and build partnerships. Although the structure of these documents remains consistent, there are variations in the requirements, level of detail, and presentation tailored to the audience. Each variation of a financial report adds to the cumbersome effort of manual data collection and aggregation, complex calculations, and distribution to each recipient.
With the new features within QuickSight, we can now streamline and automate the workflow and reduce manual effort by utilizing the APIs to generate and store reports. Automation is key to reducing maintenance, improving efficiency, and increasing accuracy of financial statements. What could take days or hours to build manually can now be done in minutes. These digitally formatted documents can also be placed in a centralized repository where they are securely stored, versioned, archived, and accessible for further processing, auditing, or retrieval.
Solution overview
The solution involves building out a serverless application that will use the financial data stored in Amazon Redshift to create, generate, and store reports programmatically using AWS Cloud-native services. The following steps are involved:
- Data is extracted, cleansed, transformed, and loaded from data sources using AWS Glue, Amazon S3, and Amazon Redshift. For this post, we assume that curated financial data is already stored in Amazon Redshift.
- Create a standardized, highly formatted P&L statement with sections and relevant financial metrics.
- Automate the generation of reports for all financial entities with the APIs.
- Catalog, store, secure, and archive financial statements in a centralized repository (Amazon S3).
The following diagram shows a reference architecture.
Create P&L statements with data from Amazon Redshift
In this section, we demonstrate high level steps on how to create P&L statements with data from Amazon Redshift.
Identify source tables and views from the data source
The following code is a structure of the sample aggregate table as it resides in Amazon Redshift for our use case:
Create a dataset for the report
Complete the following high-level steps to create your QuickSight dataset:
- On the QuickSight console, navigate to the data source that has access to the financial income data and create a new dataset.
- Make sure you have all the columns pulled from the table with accounting hierarchy, and change the data type if needed.
- Create the calculations required for the income statement. For our sample report, we created the following:
ActualVal = {Actual} * {AccountType}BudgetVal = {Budget} * {AccountType}
- Change query mode to SPICE.
- Save the dataset and wait for the dataset to be refreshed with all the data for the income statement.
- Create an analysis and choose the paginated reports option.
Create report calculations, parameters, and filters
The following is a high-level overview of creating the QuickSight calculations, parameters, and filters that we use in the income statement report:
- Calculations – Create the following calculations:
Variance = sum(ActualVal)-sum(BudgetVal)Variance % = ( sum(ActualVal)-sum(BudgetVal) )/ sum(BudgetVal)Region_Code_Desc = concat({Region-Num},'_',{Region-Desc})
- Parameters – These are required parameters that will be used by the APIs to pass values for report generation:
- pRegion – String type and static default value has been set.
- pClosingMonth – Integer type and static default value has been set.
- Filters – Create filters and map them to the required parameters. This will generate reports based on the values sent by the APIs during their run.
Create report headers
The following is a high-level overview for creating QuickSight paginated report headers:
- In the report, the header logo is displayed using the URL in custom visual content
- You can create the report title using text visual or Insights visual
- The report run date is displayed using the print date in the text visual
- Report parameters are displayed in the subtitle using parameters
Create Section 1 (Actual Vs Budget Summary)
Complete the following steps to create the financial statement summary section:
- Set the section title as “Actual vs Budget Summary” using the text visual.
- Create a pivot table and set all accounting levels and associate sorting fields with the appropriate levels. For example, Account level 2, Account level 3, Account level 4 in the Rows field wells, year in Columns, and Actual, Budget, Variance, and Variance% as Values.
- Format the header, cells, total, and subtotal as per the design requirements of your financial statement, adhering to your enterprise style guides.
- Make sure the “page break after” feature is turned on for Section 1.
Create Section 2 (Actual Vs Budget Account Details)
Complete the following high-level steps to create the financial statement detail section:
- Create a new section for the detail report.
- Set the section title as “Actual Vs Budget Account Details” using the text visual.
- Create a pivot table and set all accounting levels and associate sorting fields with the appropriate levels. For example, Account level 4, Account level 5, Account level 6, and general ledger account number in the Rows field wells, year in Columns, and Actual, Budget, Variance, and Variance% as Values.
- Format the header, cells, total, and subtotal as per the design requirements of your financial statement, adhering to your enterprise style guides.
Create a report footer and publish dashboard
Complete the following steps to create a QuickSight paginated report footer and publish the report:
- Add a footer below the detailed report section.
- To add a page number using the text visual, choose the hash sign in the text box toolbar and choose the Page number / Page count option.
- In the next line, add confidential notes or contact information as deemed necessary to meet your requirements template.
- Choose Share and then Publish dashboard.
- Provide the dashboard name as “Financial Income Statement” and choose Publish dashboard to publish the dashboard and launch it in the current browser window.
You can now use this published report as a dashboard to run via APIs on demand with the required parameters.
Optionally, you can generate a PDF report or schedule the report to run at a desired frequency.
The following screenshots show the summary and detail pages of the income statement generated using QuickSight paginated reports.
Automate the generation of reports
The following figure shows the flow and steps required to automate the report generation process.
On the QuickSight account, complete the following steps:
- Use the
StartDashboardSnapshotJobAPI to generate reports for the dashboard. In one API call, you can request up to five CSV reports for a table or pivot table visual and up to one paginated PDF report. - The AWS account ID, dashboard ID, sheet ID, and snapshot job ID are required for report generation. Note that the snapshot job ID has to be unique if used within the 24-hour period.
- Associated parameters for report filters can be passed for on-demand report generation.
- The generated output can be stored in the S3 destination bucket. If the S3 destination is not provided, a presigned S3 URL will be generated.
Create an input file similar to the following example and name it anycompany-financialreport-call.json:
With the input JSON file, run the StartDashboardSnapshotJob API call:
aws quicksight start-dashboard-snapshot-job --cli-input-json file://anycompany-financialreport-call.json
A successful API call will result in the following response:
Run DescribeDashboardSnapshotJob API to describes the status and configuration of the snapshot generation job started with the StartDashboardSnapshotJob API:
A successful API call will result in the following response along with the JobStatus of the snapshot job:
Use DescribeDashboardSnapshotJobResult API to describe the result of a completed snapshot job:
The response includes the S3 URI when a customer S3 bucket is specified; otherwise, a presigned URL is generated for the generated reports or error information, if any:
Catalog, store, and archive reports
The following figure shows the flow and steps to catalog (rename files to business-friendly names) and store the reports in Amazon S3.
The reports can then be versioned and archived using Amazon S3 storage lifecycle management. Use the Amazon S3 APIs to copy and delete objects in Amazon S3.
Key considerations
To successfully create, generate, and share accurate financial statements, businesses need to make sure their data meets the requirements of financial statements:
- Understand the chart of accounts – Understanding the chart of accounts ensures that you know how the records are being categorized and grouped. This helps with tracking how money is flowing in and out of the organization.
- Identify categories and levels – Every entry belongs to a specific classification that helps with identifying how transactions are being broken down.
- Define sort keys and signs – Know what sort keys are needed to filter and join between the data to accurately represent the exchange of cash-based events.
- Complete verification and validation of statements – It’s best to verify the mathematical accuracy when examining your source documents and ensuring that those transactions took place. When you consolidate the financial data, the statements should validate the accuracy and completeness of entries.
In addition to data, it’s important to keep in mind these API considerations:
- API selection – Choose the appropriate APIs to provide the necessary functionality. Reducing the level of complexity, redundancy, and dependency will help reduce ongoing maintenance and updates. It is better to consolidate and have a well-planned workflow to minimize points of failure.
- Job automation – AWS offers several services around APIs and workflows, such as AWS CodePipeline, AWS Lambda, and AWS Step Functions to automate repetitive tasks and reduce the amount of error and scaling business operations. There is freedom in selecting any option, but it’s important to evaluate each option based on the needs and constraints of the workflow.
- Report customization – Take advantage of templates, layouts, and formatting options for building custom reports in QuickSight. The predefined structure simplifies the development process and makes it easier to tailor for specific clients. Also, make sure to use the appropriate calculations for financial reports.
- Data security – To protect financial information from unauthorized users, it’s important to implement strict access controls, encryption, and secure storage. Setting up role-based access control and row-level or column-level security will differentiate the information relevant to each user, whether they are an investor, executive, and so on. There are also several compliance standards that govern the integrity of financial statements, so follow the Shared Responsibility Model by setting up the right controls that are available on AWS.
- Error handling and logging – Proper error handling and logging can enable teams to identify the root cause and troubleshoot issues.
Conclusion
In this post, we went over the use case of a financial services organization looking to generate financial reports for multiple stakeholders. We accomplished this by taking the recently released set of QuickSight Snapshot Export APIs to develop and store financial statements programmatically. By implementing an automated workflow, financial teams can streamline the reporting process, reduce manual efforts, ensure data integrity, and accelerate delivery. Although this post was focused on the P&L statement, the step-by-step process can apply to multiple scenarios and different types of documents or reports.
Join the Quicksight Community to ask, answer and learn with others and explore additional resources.
About the Authors
Bhasi Mehta is a QuickSight Specialist Solutions Architect for all small to medium businesses in the US. She has been part of AWS for three years after receiving her Master’s degree in Information Systems with a concentration in Business Intelligence and Analytics. Her main focus is on data management, machine learning, and analytics tools. In her free time, Bhasi enjoys teaching yoga!
Rahul Easwar is a Senior Product Manager with Amazon QuickSight. He is the product leader for Amazon QuickSight Paginated Reporting responsible for the product launch in 2022 and continues to focus on new product innovations. Rahul has over 15 years of experience implementing and leading global Analytics programs for organizations across various industry verticals.
Neeraj Kumar is a Senior Solutions Architect for Amazon QuickSight, AWS’ cloud-native, fully managed BI service. Neeraj started his career as software engineer building software applications for automotive, manufacturing and telecom companies, he further progressed as specialist and while working at Cognizant he was responsible for designing and developing end-to-end Business Intelligence and Analytics solutions for major Insurance companies.
Salim Khan is a Specialist Solutions Architect for Amazon QuickSight. Salim has over 16 years of experience implementing enterprise business intelligence (BI) solutions. Prior to AWS, Salim worked as a BI consultant catering to industry verticals like Automotive, Healthcare, Entertainment, Consumer, Publishing and Financial Services. He has delivered business intelligence, data warehousing, data integration and master data management solutions across enterprises.