Report Automation
This solution uses a robot to trigger Excel automation functionality enabling consistent and efficient generation of regular standardised reports.
Project Summary
Purpose:
- Automate regular reporting tasks incorporating data transformation, filtering, customisation and formatting.
- Facilitates changing client requirements and incemental improvement.
- Easily amend steps to automatically filter out non-required/placeholder data.
Tech:
- This solution combines
- Microsoft Excel – built in automation functionality
- Microsoft Power Automate Desktop
Benefits:
- Efficient and scalable solution that can be applied to a range of regular reporting requirements.
- Improve report output consistency without using team resources.
- Scope to extend automation to include database export process and circulation.
Demonstration
Prepare
Takes a pre-defined reporting template and combines it with refreshed database exports using an automation flow.
Execute
The robot captures the data export and combines it with the template by refreshing connected data queries. It then saves the report with pre-defined customisation and formatting.
Continuous Improvement
The in-built data actions can be amended and tweaked to reliably and easily capture any future changes or improvements required.
Solution
(Initial Set Up) Template report and data manipulations
- The solution is based on connecting a template report to data export files
- The template report is therefore set up in Excel with built in data queries (similar to macros) created for the steps required to transform, manipulate or format the data.
- This creates a traceable ‘recipe’ that can be tweaked in the future, thus enables the process to be efficiently improved or changed.
Database exports are generated and filed in a target folder:
- In this example, three reports need to be merged for the output report
- It uses a consistent identifying field from the data exports to join the data.
- For future reports, the refreshed data is saved in the same folder, overwriting the existing in advance of the robot being triggered.
Robot is triggered
- The Power Automate Desktop robot is triggered.
- It opens the template report and refreshes the underlying data connections and pre-programmed data transformations.
- It saves the file with a date time stamp.
- The robot could be expanded to include the database export process and also report circulation if required.