We all have those moments when we get requested for data, or need to know the answer to a specific question for something, but dread the task of pulling a number of different reports together into a spreadsheet and run some formulae, or maybe having to potentially use an overly complex custom query builder to build conditions across a large data set. To help solve this problem we built our SIMPLE report builder.
The platform was architected to work with large data and the Report Builder caters for the situation when the Predefined Reports do not answer the question you need to get an answer to.
To access the Report Builder, just select “Report Builder from the right hand side of the Report Dashboard. Here you will be presented with a screen that asks you to confirm the source of the data you wish to query.
You can simple define: Date Range / Currency / Fields you want to pull / and the Scope of data My Spend (Users) / Company Spend (Admin) / Organisation Spend (Admin + Company reporting)
Working with the screens
The report builder screen largely looks like the Predefined Reports, however the cube charts have been replaced with a conditional filter editor explored in more detail below. Along the top of the screen, the bar indicates the current selected data source, and the data represented in below. The data table scrolls horizontally, so you can scroll across to the right to see all of the fields chosen as well as vertically. For large data sets, the platform will render enough rows to fill the screen, and as you scroll down the page will continuously load more rows so you can see a full picture of your spend. This approach allows us to render large amounts of data quickly into your browser or application without having to wait for data to be formatted or rendered, and allows us to deliver an instant reporting experience.
Conditional Filters – Building Your Filter
The key part of the Report Builder, and the one we are most excited about, is the Conditional Filter builder. This puts you in control of the data which is displayed in the data table below, and allows you to logically build a string of conditions which trim down the results until you are left with only the results to the question you needed to ask.
To begin, click Add Condition, and choose your first field name to query. Next, select your measure (for example, equal to) and finally choose your criteria – here you are presented with the top options but you can begin to type in this box and autofill will present the available options. If no option lists here, there is no data matching this condition. To turn this query into a negative ‘is not’ query, simply click on ‘is’ to toggle this option.
Once you have built your query you are free to build another filter by selecting Add Condition again, and you can chain these filters to create complex queries quickly with immediate results detailed in the data table below.
You can clear the filters and start again, resetting the data table to default with all results, by clicking Clear Conditions.
Outputting Data from the Report Builder
The Report Builder provides the ability to output the entire data table of results into a CSV file, on demand. No scheduling or waiting is required, just hit Export to CSV and the file is downloaded in the browser. The CSV report is in a standard format which will allow you to import the data into any third party software and is formatted to import cleanly without having to manipulate the data or remove and header rows – you get a perfect full version of the data from the source you have chosen in the search.
Once you have build your report using the conditional filters you may want to save this as a template to use this again in the future. You can do this by clicking Save Template where you will be asked to give your report a name and confirm the fields you wish to choose to be part of the results. Click Load Template in the future to recall this for use.