Streamline your reporting process with Amazon QuickSight automation

Amazon QuickSight stands at the forefront of AWS business intelligence (BI) and data visualization offerings, enabling organizations to create and share interactive dashboards, perform one-time analyses, and glean actionable insights from their data. In today’s data-centric business environment, the ability to efficiently generate and distribute insightful reports across different segments or regions remains a critical challenge for many business.

Addressing this challenge, we delve into the automation of report processing workflows. For our use case, a real estate customer wants to send state-specific weekly real estate reports for each state to their regional agents. In this post, we show you how to use QuickSight, combined with its Snapshot APIs and other AWS services, to automate this process.

Solution overview

Our solution uses the following key services:

• Amazon EventBridge
• AWS Step Functions
• Amazon QuickSight
• Amazon Simple Notification Service (Amazon SNS)
• Amazon Simple Storage Service (Amazon S3)

The following diagram illustrates the high-level architecture of our solution. We use Amazon QuickSight Pixel-perfect Reports to create reports containing business-critical information. We use a parameter filter for regional segmentation. To generate PDF reports for various states, we use the QuickSight Snapshot APIs. A Step Functions state machine orchestrates the report processing using AWS Lambda functions, employing Distributed Map for parallel processing of the reports. We use Amazon SNS to notify stakeholders when the reports are successfully generated. Finally, we use EventBridge Scheduler to automate the report processing on a weekly basis.

The following diagram illustrates the solution architecture.

The following diagram illustrates the Step Functions workflow.

Prerequisites

To run this solution, you must have the following:

• A QuickSight Enterprise account. If you don’t already have one, you can sign up for one.
• A QuickSight account with the Pixel-perfect Reports add-on enabled.
• The AWS Command Line Interface (AWS CLI).

Create a QuickSight dashboard

In this section, we walk through the steps to create a QuickSight dashboard with a pixel-perfect report, customize it, and publish it.

Create a pixel-perfect report

To create a pixel-perfect report, complete the following steps:

1. Download the sample dataset.
2. On the QuickSight console, choose Datasets in the navigation pane.
3. Choose New dataset.
4. Choose Upload a file and upload the CSV file of the sample dataset.
5. Choose Next.
6. Choose Visualize.
7. Choose Pixel-perfect report from New sheet and choose Create.

Add a logo to your report header

To add a logo to your report header, complete the following steps:

1. Choose the HEADER section in the report layout.
2. Choose + ADD and then Custom visual content on the application bar.
3. Open the Format visual pane for Customize visual.
4. For URL under Custom Content, enter https://ws-assets-prod-iad-r-iad-ed304a55c2ca1aee.s3.us-east-1.amazonaws.com/cd8ebba2-2ef8-431a-8f72-ca7f6761713d/images/ac_logo_orange.png.
5. Choose Apply.

Add a title and date to the report

To add a title and date to the report, complete the following steps:

1. Choose the HEADER section in the report layout.
2. Choose Add text on the application bar.
3. Enter Weekly Sales Report into the text box and press Enter to add a new line.
4. Choose the calendar icon in text box toolbar.
5. Choose the date format (EEEE, MMMM d, yyyy) for the report.

Add a footer to the report

To add a footer to the report, complete the following steps:

1. Choose the FOOTER section in the report layout.
2. Choose Add text on the application bar.
3. Click anywhere inside the text box to add a page number.
4. Enter Page in the text box, choose the hash (#) icon in the text box toolbar, and choose Page number / Page count.
5. On the next line, enter Contact info: support@anycompany.com.
6. Select all the text in the text box, choose the align icon in the toolbar, and choose Align center.
7. Resize the text box to fit the full width of the footer.

Create a visualization for the sum of total homes sold

To create a visualization of total homes sold, complete the following steps:

1. Choose + ADD on the application bar and then Add Visual.
2. Choose Vertical bar chart from Visual Types.
3. From the Fields list, drag period_begin to the X axis, and change the aggregation to Week.
4. Drag total_homes_sold to Value and change the aggregation to Sum.

Create a visualization for sum of total active listings by city and state

To create a visualization for total active listings by city and state, complete the following steps:

1. Choose + ADD on the application bar and then Add Visual.
2. Choose Points on map from Visual Types.
3. From the Fields list, drag city to the Geospatial field.
4. Drag total_active_listings to the Size field and change the aggregation to Sum.
5. Drag state to the Color field.

Create a visualization for sales price by city and state

To create a visualization for sales price by city and state, complete the following steps:

1. Choose + ADD on the application bar and then Add Visual.
2. Choose Table from the Visual Types.
3. From the Fields list, drag state and city to the Group by field.
4. Drag median_days_to_close, median_sale_price, active_listings, and total_homes_sold to the Value field.
5. Change the Aggregation option.

Add a parameter field to the report

To add a parameter field to the report, complete the following steps:

1. Choose the Parameters section on the top and choose Create one.
2. For Name, enter pState.
3. Choose Create.
4. Choose Filter and choose ADD FILTER.
5. Choose the field state.
6. Edit the state filter, and for Applied to, choose All applicable visuals.
7. Change FilterType to Custom filter.
8. Select Use Parameter and select the parameter pState.
9. Choose Apply.

Publish the dashboard

To publish the dashboard, complete the following steps:

1. Choose PUBLISH.
2. Enter the name as realestate-weekly-report.
3. Choose Publish dashboard.
4. Note down the dashboard ID from the browser URL.
   
5. In the AWS CLI, enter the following command:

   aws quicksight describe-dashboard —aws-account-id <account-id> —dashboard-id <dashboard-id>

6. Note down the SheetId from the response.

Set up the environment

To set up the environment using AWS CloudFormation, complete the following steps:

1. Sign in to the AWS Management Console.
2. Choose Launch Stack:

3. Choose Next.
4. For Stack name, enter a name.
5. In the Parameters section, enter the required parameters.
6. Choose Next.
7. On the Configure stack options page, leave all values as default and choose Next.
8. On the Review stack page, select the check boxes to acknowledge the creation of IAM resources.
9. Choose Submit.

Wait for the stack creation to complete (approximately 2 minutes).

This template configures the following resources:

• S3 bucket qs-reporting-<accountid>-<region>
• Step Functions state machine QSReporting-StateMachine
• Lambda functions FireJobFunction and JobStatusFunction
• SNS topic QSNotification
• EventBridge Scheduler QSEventScheduler

You can subscribe to the topic from the email you received, if you wish to get notified when the reports are available.

Initiate report processing

Now you’re ready to test the solution.

1. On the EventBridge console, choose Schedules in the navigation pane.
2. Choose the schedule QSEventScheduler.
3. Choose Edit and enter today’s date and a few minutes from the current time.
4. Choose Skip and Save, then choose Save.

The Step Functions state machine starts running at the time you scheduled. Monitor the progress of the state machine and wait until it’s complete.

You can navigate to the S3 bucket to view the reports, and note that the reports are created for each state in their respective folders.

Clean up

To avoid incurring future charges, delete the resources created for the solution:

1. On the Amazon S3 console, select the S3 bucket created as part of the CloudFormation stack, then choose Empty.
2. On the AWS CloudFormation console, select the stack that you created and choose Delete to delete all the resources created by the stack.

Conclusion

In this post, we showed you how to build enterprise reports using QuickSight Pixel-perfect Reports and automate your weekly or monthly reporting tasks using Quicksight APIs and AWS serverless services. You can further extend this solution to FTP the reports using Amazon Transfer Family services. To learn more about QuickSight Pixel-perfect Reports, refer to Amazon QuickSight Pixel-perfect Reports.

About the Authors

Sumitha AP is a Sr. Solutions Architect at AWS. She works with customers and help them attain their business objectives by designing secure, scalable, reliable, and cost-effective solutions in the AWS Cloud. She has a focus on data and analytics and provides guidance on building analytics solutions on AWS.

Ahsan Zulfiqar is a Sr. Solutions Architect at AWS supporting SMB customers. His area of specialty is in Data Analytics, helping customers build robust, efficient, and scalable data platforms on AWS.

Sharadha Ravindranath is a Solutions Architect at AWS, helping SMB customers solve complex challenges to drive business transformations and unlock potential of the cloud to deliver exceptional results through innovation.