/ 4 min read / Data at Work, Using Holistics

How Anyone Can Automate Data Reporting: From Spreadsheets To Free Cloud Database (Holistics & ElephantSQL)

by Lovisa Johansson, Evan Tan

How Anyone Can Automate Data Reporting: From Spreadsheets To Free Cloud Database (Holistics & ElephantSQL)

Have you struggled with manually combining and merging spreadsheet files into a management report, or wasted hours checking through Excel files from your various teams? Those days are over! In this co-authored blog post, we show you how to build automated reports using scheduled imports of your spreadsheets files, with the Holistics platform for business intelligence, and ElephantSQL’s PostgreSQL database service.

For those of you new to the cloud who haven’t got a database, ElephantSQL offers a free tier of a small PostgreSQL cloud database instance! It literally takes less than 10 seconds to setup, and will let you use the range of Holistics features to automate your spreadsheet reporting processes.

The Time To Automate Is Now

Thanks to digitisation and the affordability of cloud computing, the way we work are being transformed for good. Yet what many don’t realise is how easy it can be to start off adopting these new tools. Companies that embrace the new digital tools will be far more agile and outperform companies that are stuck with manual processes that waste both time and resources.

The fear of the unknown and resistance to changing the way things are done, can easily be replaced by simple experimentation, as we show you how in this article.

How It Works

The cloud database allows you to have a single, scalable source to accept your various spreadsheet files in a tabular, structured format. This then allows you to manipulate the data stored in the database, to output automated reports, charts and dashboards, which can even be scheduled for delivery via channels such as email, Slack and more!

Merging spreadsheets with a cloud database

Setting Up Your Free ElephantSQL Cloud Database

Head on over to the ElephantSQL website at http://elephantsql.com, create an account and select the free tier. Now you just need to give your database instance a name, select the region and data center (typically one that’s closest to you), click create instance and you’re done!

Creating a free cloud database takes seconds

Connecting your new cloud database to Holistics requires just a few details. Click on your new database to pull up its details, and copy over the following information of

  1. Host,
  2. Database Name and Username,
  3. Password and
  4. Port.

Insert your database details to connect to Holistics

Importing And Merging Your Spreadsheets With Holistics

Now that your database is connected, you’re ready to begin merging your spreadsheets to create your Holistics reports! The two examples given in the video are:

  • Adding date and monthly price data from three different spreadsheets into a single report automatically, using Append mode in Data Imports. (Video at 1:10)
  • Updating existing records and adding new data from two different spreadsheets into a report, to update the order delivery status from Pending to Delivered or Cancelled. This uses the Incremental Import mode for Data Imports. (Video at 2:57)

Holistics Data Imports allows you to easily schedule and move data into your reporting database, with zero coding or data engineering experience required. This lets anyone easily migrate data from database tables, spreadsheets and CSV files, MongoDB, and popular data sources like Google Sheets and Folders, Facebook Ads, Google Analytics and Pipedrive, into your SQL database of choice.

Import data from multiple sources to your database

This enables regular users to run processes that would typically require technical expertise to manage extract, transform and load (ETL) data operations, giving you the abilities of a fully trained data engineer!

Once you’ve imported the spreadsheet data you need into a table in your ElephantSQL cloud database, you’re ready to begin automating your reports. The Holistics report editor allows you to point and click you way to generate the visualizations and charts you need, or to even edit the SQL query for greater flexibility.

In our second example in the video at 2:57, we created a delivery report to track orders with statuses Pending, Delivered and Cancelled, with conditional formatting to highlight rows such that

  • Delivered: Green
  • Pending: Yellow
  • Cancelled: Red

Automatically merge spreadsheets into a full report

Now, whenever this Holistics report is opened, it will fetch the latest data from your ElephantSQL cloud database to update the order statuses. And since the import of spreadsheet data has been scheduled to automatically run (i.e. on a daily basis), you can now have a report to monitor the movement of your orders on a daily basis, without having to manually compile a single spreadsheet!

You can read more about using Holistics Data Imports and Incremental imports for different data sources here at our documentation page, and learn how to use the Holistics Report Editor to point and click your way to building your reports on our feature page here.

This is just one example of how you can use Holistics and ElephantSQL to simplify your data work. We’ll have more examples of how you can use cloud solutions to take your business to the next level, so stay tuned and subscribe to us for more!

From SQL Queries To Beautiful Charts

Connect to your database and build beautiful charts with Holistics BI

Learn More
Grab Logo

"Holistics is the solution to the increasingly many and complex data requests from the operational teams"

Tang Yee Jie - Senior Data Analyst, Grab

Tang Yee Jie

Senior Data Analyst, Grab