Create a custom scheduled report in Excel or PDF

In this tutorial, we will see how to create a first customized report, based on your data and sent monthly to two email addresses. One in Excel and the other one in PDF format.

Prerequisites

To create your first report, you will need at least one source on which you have already uploaded at least one year of consumption data.

You also need to have Microsoft Excel or an other program capable to create and edit .xlsx files installed on your computer.

Step 1: configure the report

In Opinum Data Hub, go to the Reports page and click on Actions, then Create Report

The report creation popup opens. You can now fill all the fields as follows:

General information

  • Name : choose a name for your report (e.g.: "Monthly Electricity Report")
  • Use file : leave on "no file" for now. We will see later how to create and upload a template.
  • Schedule type : you can choose to generate the report on a scheduled base or on demand. For editing and testing the report, we will first use the "On-demand" option.

Data sources

Here you can select the data to export in your report. There is two ways of selecting the data :

  • Choose each source manually
  • Select a list of sources based on a view

For the purposes of this tutorial, we will select one source manually. In the data selector, fill all the fields as follows:

  • Sheet name: we will export the data in Excel, so we have to choose a sheet name to insert the data in. Let's take "Electricity"

  • Site: select the site where your source is located

  • Source: select the source you want to make the report on

  • Variable: select the variable on which your data are stored

  • Granularity: you can chose to export raw data (as it is stored in our database) or to aggregate it. Aggregation helps to reduce the report file size by exporting only one data point per period chosen.

For the tutorial purposes, let's select the month granularity.

  • Aggregation: if you select a granularity other than raw, this field will let you choose how the data is aggregated within the selected period

For the tutorial purposes, as we work with consumption data, let's select the SUM granularity.

  • Period: choose the period to export. You can choose a fixed or a sliding period. For this tutorial we will export one year of data
Note

The last complete period plus the time till now will be exported. e.g.: on September 1st 2018, if you export 1 year of data, you will get the data from January 1st 2017 till August 31th 2018 included.

  • Unit: select the unit in which you want to export the data

3. Metadata

You can also add metadata linked to the sites and sources selected in the previous step. All these options are optional.

  • Sites: site details, including the forms
  • Sources: sources details, including the forms
  • Alerts: include an history of every alert related to data sources selected
  • Events: include an history of every event related to data sources selected
  • Invoices: include the invoices linked to the sites selected
  • Schedules: include the schedules configured for the sites selected

For this tutorial, we will only export the sites and sources details.

4. Notifications

Here, you can configure the report recipients:

  • Send via:
    • Email: send by email
    • Power BI: send the data to your Power Bi Online account
    • Storage: save the report in your Opinum storage
    • Azure Table Storage: save the data in your Azure Table Storage account
    • FTP: send the report through a FTP server

In this tutorial, we will only choose the Email option.

Tip

Learn more about how to send and receive reports

The following parameters to enter are:

  • To: select a mail recipient. You can either choose an Opinum user or enter a custom email address
  • As: select the format (Excel or PDF) and a method (Link or Attachment). The Link method will send a mail with a link to download the report. This allows you to send reports larger that the limits of the mailboxes.
  • Info: allows you to send the mail from your own SMTP Server

For now, let's leave these fields empty. We will come back to this section after having created our model.

Click on Save before exiting the report configuration window.

You can now see your report in the list and send it to your email by clicking on Run, then Get Excel to receive the file.

Note

The functions "Get Excel" and "Get PDF" will always send the report attached to a mail sent to your email address, regardless the notifications configured. These functions are made for report building and testing purpose.

Step 2: create a template

Now, we will create an Excel template that will be used to generate future reports in Excel and PDF. The first step is to open the email you received at the end of step 1.

Attached to this mail is our first report, generated without any formatting.

In the following steps, we will use it as a basis to create the template.

  1. Save the file on your computer with a name like "Monthly Electricity Report Template"
  2. In the file, you will see different worksheets:
    1. Sheet1: an empty sheet where we will create our first report.
    2. Electricity: here is the data. See that the sheet name is the one we defined on step 1.
    3. Sites: here are the sites details
    4. Sources: here are the source details
  3. Rename "Sheet1" with a name like "Report".
  4. Insert a chart on this sheet, using the data in the Electricity sheet as source. Let's use the columns DataTime and Conso as inputs.
  5. You will see that the dates are in raw excel format. To change that, go to the Electricity sheet, select the DataTime column and change the type to short date
  6. Now that we have our first chart, we will add dynamic information about the site. Find four empty cells to write "Name", "Street", "City" and "Country" and add a title on top of these four cells "Site information"
  7. In front of these four cells, use a simple formula to get the corresponding data from the Sites sheet. e.g.: "=Sites!A2"
  8. Then, it's time to customize this Excel to make it fit with your own style. You can add your logo, some text, your contact information, change the colors,... make it your own report!
  9. To convert this report to a template, delete all the content of the Electricity, Sites and Sources tabs.
  10. Now, hide the tabs that you don't want to see in the final report (typically Electricity, Sites and Sources) and set a printing area on the Report sheet to define the area that will we converted to the PDF.
  11. Save the file and you're done with the template creation.

Step 3: Schedule a report based on the template

Now that we know which data to export and that we have a nice template, we will schedule the automatic generation of our report.

Let's go back to Opinum Data Hub and click on your report name to open the configuration window.

  1. Click on Upload one to select the template file on your computer and upload it to Opinum Data Hub.

  2. Set :

    1. Scheduled type to Scheduled
    2. Scheduled mode to Simple
    3. Month : choose a day and an hour to schedule the next report generation
Tip

learn more about schedule

  1. Option: if you already have more than one source with data on the platform, you can test your report on another source by changing the Data Sources configuration

  2. Add a notification to send the report to yourself as link, in PDF and a to someone else by clicking on Add another notification

  3. Save and exit the configuration window.

  4. To see the result before the next execution of the report schedule, click on Run and Send PDF to [your name] to see the result in PDF.

And that's it !

Warning

Your template is saved in the storage. Please note that deleting the template means that all the reports using it are also deleted.