In this article:
Create | Settings | Columns | Link | Edit | Actions | Import | Clear | Review | API

What are data sets?

Data sets are configurable objects that store your data (think of them as Excel worksheets, or database tables, or tabular lists). You can give a data set a name, define its columns, set the user permissions, and then import data into it.

Let's look again at our diagram (which we explained in the introduction) :

diagram datasets

Data sets are usually the first thing you need to set up, because they store all your data, and they are therefore fundamental to everything else.

When you sign up, your account already contains demo data with reports, imports, and data sets for common financial reporting. So you don't have to start from scratch, you'll have working examples which you can start to use, and amend or expand over time. You can create, change, and delete data sets at any point in time.

Some common examples of data sets are:

  • Financial data
  • List of companies
  • List of customers
  • List of business units
  • Chart of Accounts

Data sets don't need to exactly mimic the structure of your source data, because you can convert your data during the import anyway. It is best to design them in such a way that is optimal for reporting.

Create a data set

You can create a data set in two ways:

  • Click on Define - New in the sidebar menu.
  • Click on the Actions - Create new button of an existing data set. This also enables you to make a copy of an existing data set.

Either way, the rest of the process is the same, and you can enter a name for your new data set, set user permissions for it, and define its columns. The columns of a data set are shown in Excel-style with a preview of their data:

screenshot define datasets

Settings

You can define the settings for the data set via these fields:

  • Name - enter a name. This must be unique within all your data sets.
  • Description - enter an (optional) description. This is helpful to document your various data sets.
  • Active/locked - activate or lock a data set. When locked, no data can be imported or edited, and no reports can be defined or run on this data set.
  • Permissions - select the permissions for access to this data set (in others words: do you allow this data to be manually edited, imported, exported, or used by reports or models?):
    • Import data - enable this data set for Define imports, to allow data to be imported into this data set. This is typically used for data sets that contain transaction data which is imported from other systems.
    • Edit data - enable this data set for Edit data, to allow users to manually edit the content of this data set. This is typically used for maintaining lookup lists and other information that is maintained in XLReporting (not imported from other systems).
    • Reports - enable this data set for Define reports, to allow reports to be created on this data set.
    • Models - enable this data set for Define models, to allow reporting and planning models to be created on this data set.
    • Models: Save - enable this data set for Save queries in Define models, to allow user data from models to be saved into this data set.
    • Clear all data - enable all data in this data set to be cleared at once, rather than through user filters.
    • Export data - enable users to export the raw data in this data set to Excel, CSV, JSON, or PDF. Disable this option if you want to avoid sensitive data being accessible other than when used in predefined reports and models.
    • Access API - enable external API calls to this data set.
  • Group - select how this data set is to be shown in the menu navigation.
  • User roles - select one or multiple user role(s) to restrict access to this data set. If you leave this blank, all users have access.
  • Script - create a script to process data before it is being stored in the data set. This option is for advanced use, and only visible if your user role has the relevant permissions. Read more about scripts.

Define columns

The columns of a data set are shown in Excel-style with a preview of their data:

screenshot define datasets columns

You can edit or delete an existing column by clicking on its Edit header, and you can add new columns by clicking on the New header in the last column. If you want to change the order of the columns, just drag them across to the left or right.

You need to give each column a unique name and select a data type for it:

screenshot define datasets columns edit

You can choose from 8 data types:

  • Text - This is the default type. You can store unlimited number of characters.
  • Number - Numbers and amounts. You can specify the number of decimals to control the precision of the stored numbers.
  • Date - Dates. You can specify the date format in your account profile.
  • Checkbox - For yes/no values.
  • List - A dropdown list. You can define the list of values, and selection is mandatory.
  • Combo - A combo list is similar to a list, but selection is optional, and users can enter any value.
  • Key value - If you want to use data sets for lookups, you need to set one column as Key value. This column needs to contain unique values. As an example: in a product list, the key value would be the product number because that uniquely identifies every product.
  • Linked value - If you want to link this data set to another data set, select that data set and column here. The dropdown shows all data sets that contain a Key value.

Dependent on the data type you have selected, you can choose to enable This column always requires a value to make this column mandatory in Edit data and Import data.

There are 2 ways in which you can link information between multiple data sets:

  • Dropdown list - by setting a column to data type List or Combo (see above data types), you can create a dropdown list based on the values in another data set. This enables you to restrict the content of a column to certain values. XLReporting validates the data during import and user editing. When user are editing data in Edit data, they will be presented with dropdown lists.
  • Linked value - by setting a column to data type Linked value, you create a link between 2 data sets. The values between a Linked value in a data set and the Key value in a related data set need to correspond. XLReporting validates the data during import and user editing. Linked data sets -and their combined columns- will be jointly available in reports.

The below example shows how a Dropdown list is used:

screenshot define datasets columns linked

The below example shows how Linked value is used to link a data set (that contains financial data) to 2 other data sets (one contains the list of companies, and the other contains the Chart of Accounts):

screenshot define datasets linked

Edit data

When defining a data set, you can manually add, edit, and delete its data in a spreadsheet-like manner. You can edit any column in any row. In List, Combo, and Date columns you can select from a dropdown with the list of values. To insert, delete, copy, or move rows, you can either right-click on a row, or click on the ☰ button in the last column of a row:

screenshot define datasets edit data

If you don't want to manually enter the data, you can also import data from a file via Actions - Import data.

Actions

When defining a data set, you can use the Save and Actions buttons in the right-top of the screen:

screenshot define datasets actions

These buttons enable you to do the following:

  • Save - save your changes
  • Actions - open a dropdown menu with further options:
    • Create a new data set (or copy one)
    • Delete this data set
    • Clear data rows
    • Import data
    • Export data or print
    • Clear cached data
    • View API access
    • Review this data set
    • Edit this data now

Clear data rows

Click on Actions - Clear data rows to clear either all data rows, or a selected number of rows. Using a preview, you can simply select and view the rows you want to clear:

screenshot define data sets clear data rows

Import data

Click on Actions - Import data to import data from a file or from another system. After you have selected your source, you will see a data preview:

screenshot define source import columns

If you selected an Excel workbook, you will be able to select which sheet within that workbook you want to import. For Excel workbooks or text files, you can also indicate at which row the data starts (enabling you to ignore empty or title rows in files) and whether your file contains column headers. It is preferable to use files that contain header names, because it is easier to work with column names when importing data. If your file does not have header names, the columns are referred to by letters (in Excel style: A-Z) or column numbers (1-99).

Once you have reviewed the data, click on Apply and you will be given the following choice:

  • Create columns - create new columns based on the content of the file. Existing columns are not affected, and no data will be imported.
  • Import data - import the data itself. This will overwrite the existing content of the data set. Be careful with this option!

Review this data set

Click on Actions - Review this data set to review a number of aspects of this data set:

  • Data diagram - shows a diagram with all linked data sets. This is derived from columns that are set to data type Linked values.
  • Linked data - shows the results of checks on the Key values and Linked values that are used to link data sets. Because linked values need to correspond with key values, this integrity check is important.
  • Define fields - shows a summary of all defined columns and data types.
  • Data set use - shows all linked data sets, the imports, reports, and models that are using this data set, and the total number of data rows in this data set.
  • Recent activity - show all recent user activity relating to this data set.

screenshot define datasets review

View API access

You can retrieve data from -and import data into- data sets through our API. For example, you can send data directly from another system or database into your data sets without having to go through the web pages. You can also retrieve data from your data sets to send it straight into another system or even directly into an Excel workbook.

Click on Actions - Define API integration for instructions, properties, and examples:

screenshot define datasets api

XLReporting provides the following API methods:

  • Get all data - returns the data content of the data set.
  • Get row count - returns the number of data rows in the data set.
  • Get column info - returns the defined columns, and the properties of all columns.
  • Get column values - returns the list of unique values from the data in a given column.
  • Import data - import data into the data set.
  • Clear data - clear selected data from the data set.

You need to provide a valid API token with all requests. Tokens are linked to users, and you can request a token through My account - Users.

Read more about API integration.

Recommended reading:
Back to top | Imports | Reports | Models