Custom data sources - The beginners guide

The custom data sources feature gives you the flexibility to report on any third party data along with existing Reporting Ninja data connections such as Facebook, Google Ads and more. Just upload your csv files to an Excel file or to a Google spreadsheet, connect your data via the our custom data sources feature, and start using Reporting Ninja as your all-in-one comprehensive data reporting solution for reports and dashboards.

In this beginner's guide, we'll walk you through everything you need to know about how to use your custom data in Reporting Ninja.

How to access the custom data sources feature

Reporting Ninja allows you to use your own custom data sources and include in your reports KPIs, graphs and tables presenting data coming from:

• Excel files (.xlsx) hosted on a remote location like a DropBox folder
• Google Sheets
• Embedded spreadsheets in the widgets where you can copy or enter your own data

In order to use your custom data, you only need to edit your regular reports or templates and drag & drop a KPI, graph or table from the palette into the report canvas, and select "Microsoft Excel (xlsx)" or "Google Sheets".

Before reviewing how to proceed in case you want to use an Excel file, a Google Sheets spreadsheet or an embedded spreadsheet, it is important to understand to your custom data needs to be prepared.

Data preparation

No matter if you intend to use an Excel file (.xlsx), a Google Sheets spreadsheet or an embedded spreadsheet, your data needs to meet the following requirements:

• In the first row of your data, you must specify what labels you would like to use for your dimensions and metrics.
• Dimensions must appear before metrics.
• You must also specify the type of value your labels will represent by appending [TYPE] at the end of the label.
• Allowed types for dimensions are:
• TEXT – for regular dimensions
• DATE – for dates
• Allowed types for metrics are:
• NUMBER – for regular metrics
• CURRENCY – for metrics representing currencies
• PERCENTAGE – for metrics representing percentages,
• TIME – for metrics representing seconds
• In the case of a remote Excel file or a Google Sheets spreadsheet, the content type of cells for dates must actually be a date, not simply a text with a date format. In the case of embedded sheets, it will be text according to the specified date format in the widget settings.
• For metrics representing currencies or percentages, do not include the currency symbol or the percentage symbol. Please make sure the values for theses types of metrics are just plain numbers without any symbol.
• In your columns representing metrics, you can use formulas with functions or mathematical expressions. However, only mathematical expressions will be used when presenting totals or aggregating values by time dimension.
• There must be at least one dimension and one metric.
• There can only be one DATE dimension.
• There can be a maximum number of 16 columns.
• There can be a maximum number of 5000 rows.
• In the case of table widgets, you can use multiple dimensions but there can only be a date dimension. If you use a date dimension with other dimensions, the date dimension must be the first one
• In the case of trend graph widgets, there can only be one dimension and it must be a date dimension. There can also be multiple metrics.
• In the case of pie chart widgets, there can only be one dimension but multiple metrics.
• In the case of common chart widgets (line, area, column, bar), there can only be one dimension but multiple metrics.
• In the case of KPI widgets, there must be a date dimension. There can be multiple dimensions and metrics.

How to use an Excel file (.xlsx)

In order to use an Excel file (.xlsx), it must be hosted in a remote location like a DropBox folder, so it can be accessible for our product. So the first step is getting a direct URL for Excel file.

Assuming you are using DropBox, follow these steps to get the URL of your file:

• Sign in to dropbox.com.
• Hover your cursor over the .xlsx file you'd like to use and click Share when it appears.
• If a link hasn't been created, click Create a link.
• If a link was already created, click Copy link.
• The link will be copied to your clipboard. You can then paste it in your widget settings.

You can find more information about getting view-only access to your files in DropBox in this link https://www.dropbox.com/help/167.

To validate your URL you can use your browser. It is a valid URL when you copy it in the address bar of your browser and it directly starts downloading the file with a prompt to save it locally, not when you see the file with the DropBox viewer. In the case of DropBox this can be achieved by changing the final “?dl=0” in the URL by “?dl=1”.

Once you have your Excel file (.xlsx) URL follow these steps to use it:

• Drag & drop a KPI, graph or table widget from the palette into the report canvas.
• Select “Microsoft Excel (.xlsx)” as data source.
• You can either pick any of the predefined widgets using remote data or you can click the button “Run wizard” to create the widget from the scratch.
• If you run the wizard, in the third step you need to select the option “Remote spreadsheet (.xlsx)”.
• Finally you see the widget settings window where you can enter you file URL and the sheet name.

Every time you modify the URL or the sheet to be used, you need to click the button “Pull data” before you can see the Excel file content in the “Sheet” tab in the widget settings window.

The data you will see in the “Sheet” tab in the widget settings window is read-only.

Please bear in mind, that for instance in the case of DropBox, it takes some time between files are changed in local and the remote replicas are synchronized. So if after you click the button “Pull data” you can’t see the latest content, you need to wait some time.

How to use Google Sheets

In order to use a Google Sheets spreadsheet you must first publish the entire spreadsheet as a Microsoft Excel file (.xlsx).

Follow these steps to get your spreadsheet URL:

• Open your spreadsheet in Google Sheets.
• Click the File
• Select Publish to the Web....
• Choose to publish the entire spreadsheet as Microsoft Excel (.xlsx)
• Click Publish.
• Copy the URL and paste it paste it in your widget settings.

You can find more information about publishing Google Docs in this link https://support.google.com/docs/answer/37579?hl=en.

To validate your URL you can use your browser using an incognito session. It is a valid URL when you copy it in the address bar of your browser and it directly starts downloading the file with a prompt to save it locally.

Once you have your spreadsheet URL follow these steps to use it:

• Drag & drop a KPI, graph or table widget from the palette into the report canvas.
• Select “Google Sheets” as data source.
• You can either pick any of the predefined widgets or you can click the button “Run wizard” to create the widget from the scratch.
• Finally you see the widget settings window where you can enter you file URL and the sheet name.

Every time you modify the URL or the sheet to be used, you need to click the button “Pull data” before you can see the Excel file content in the “Sheet” tab in the widget settings window.

The data you will see in the “Sheet” tab in the widget settings window is read-only.

Please bear in mind, that it can take some time between spreadsheets are changed and those changes are published. So if after you click the button “Pull data” you can’t see the latest content, you need to wait some time.

How to use an embedded spreadsheet

Besides Excel files or Google Sheets, you can also copy or enter your own data in embedded spreadsheets.

In order to use an embedded spreadsheet, follow these steps:

• Drag & drop a KPI, graph or table widget from the palette into the report canvas.
• Select “Microsoft Excel (.xlsx)” as data source.
• You can either pick any of the predefined widgets using embedded data or you can click the button “Run wizard” to create the widget from the scratch.
• If you run the wizard, in the third step you need to select the option “Embedded spreadsheet”.
• Finally you can copy or enter your data in the “Sheet” tab in the widget settings window.