Last Updated: August 15 2023
The filters you apply for custom reports are referred to as Questions. BI Reporting is made up of two core concepts: questions and their corresponding answers. Learn more about how to access and navigate in How to Use BI Reporting.
BI Reporting is built using an integration with Metabase. Learn more about Metabase, including a more detailed guide on some of the features available in BI Reporting, in the Metabase documentation pages.
Please note that not all features available in Metabase are available in BI Reporting.
In order to view content or perform actions referred to in this article you need to have the appropriate permissions enabled in your security group. Go to System > Setup > Security Groups and access your security group to update your permissions. The relevant security group tab and specific permissions for this article can be found here:
Questions in BI Reporting are made up of: data, filters, metrics and are answered either in a table or visualised as a graph. The entire set of data from Simpro Premium build in databases is stored in tables and the tables are named based on specific elements available in each row. For example, in a customers table, each row in the table would represent a single customer. Forming a question involves deciding which table contains the required information you want to report on, and how you want that data filtered and grouped.
Consider the example to create a report for the busiest time of your business year based on the number of hours scheduled. Following instructions detail the steps to ask a new question for the example:
- Go to BI Reporting > + New > Question.
- Under Data click Pick your starting data and select Raw Data.
- You can also create a question based on Models and Saved Questions.
- Models are a way to create your own tables that can be used instead of the data tables provided from Simpro Premium. Models can be based on SQL query builder questions and allow you to update column descriptions and customise data to create a starting point for questions on a specific topic that get asked frequently. For example, you can create a model which details information relevant to specific types of customers which you want to ask a series of different questions regularly. Visit the Metadata’s documentation on Models for more information.
- Select the table that you want to report on.
- For example, select Schedules from a list of Data tables in the build.
- Click to Preview your question. You can preview your question at any point throughout the process of asking a question to get an idea of how it will be displayed and to come up with ideas about how you can filter or group the data shown.
- Click in the different sections to remove the corresponding sections, such as Preview, Join Data, Custom Column, Filter or data Summarize options. Click to Join Data from the selected table with another table.
- For example, you can opt to ask a question that looks at data from the Schedule table and Employee table.
- Joining data is an advanced feature. You may want to practise creating a question without joining data first, then add more tables later. Visit the Metabase’s article on Combining tables with joins for more information.
- Click to add a Custom Column to the results of the question and enter a FIELD FORMULA to produce the data that should appear in the column. Entering a formula is similar to writing a formula in a spreadsheet. You can use numbers fields and mathematical symbols like + to produce a result. GIVE IT A NAME to define what appears at the top of the column in the question table.
- For example, enter = Overhead Cost + Pay Rate Cost to produce a column named Total Cost, to display the total cost of schedule time.
- Custom columns are an advanced feature. You may want to practise creating a question without custom columns first, then add them to your question later. It may help to preview a table to view the information is displayed in a table first so you can consider what kind information you can apply a formula to in the process of creating a custom column.
- Under Filter click to Add filters to narrow your answer
- For example, in the Filter drop-down, select Employee Name, which is a text data type. Learn more about data types and filter categories in Filters, operators, metrics and grouping .
- Different fields have different filter options based on their individual data. For example, under Contractor Name, you could select the contractors you need to exclude or include.
- Next, select an operator to determine how the selected filter should apply on the data in the table.
- For example, select Is not if you want to select a list of contractors that should not appear in the results of the question.
- Click Add filter to populate the Filter field with your selection of filters.
- To apply additional filters, click .
- Additional filter in the example is Date > Previous > 3 > Years > Include this year > Add filter. This ensures only data from the past three years is displayed.
- Select the required metric option in the Summarize drop-down list to define what specific information you want the question to focus on. This is particularly useful if you are building a question that might be presented as a graph rather than a table of raw data. If you leave the metric field blank, a table is displayed that shows your question's criteria listed in rows.
- You can set a Custom Expression to create a new metric and, apply additional metrics, as required.
- In the example, select metric option as Sum of > Hours to ensure that the report displays the total number of hours for each record.
- Next, Pick a column to group by to combine the data presented into more valuable records, instead of presenting each record separately.
- For example, in the group by field, select Date > by week. This displays each record on the report as the sum of hours for each week. Another option is to view the Sum of Hours grouped by Employee Name.
- You can group your answers by any date or time in your table, as well as any category field. These groupings are called dimensions.
- When you apply a single dimension to your question, you get a table where the leftmost column is the dimension and the rightmost column is the value of the metric for that dimension.
- Click to apply more dimensions and add columns to the left of the dimension.
- Apply additional reporting options, as required.
- Sort: Click to sort your answer by one of the columns.
- Row Limit: Click to limit the number of rows displayed in your answer.
- Click Visualize to display the results table.
- Click to Show editor and make changes to the question as required.
- You can also click Filter or Summarize or quickly modify the Filters or metrics and grouping to change how the question is summarised.
After you have created a question, you can download the results in multiple formats.
Click to download the full results as a CSV, XLSX or JSON file.
If you download full results that contain timestamp fields, the timestamp column includes a date, time and timezone.
To reformat the column into a date and time format that suits your needs:
- Download the CSV or XLSX file and open it in your spreadsheet program.
- Add another column next to the column with the timestamp.
- In this new column, apply the following formula:
- Make sure that you replace 'A1' with the column number for timestamp column.
- When the formula is applied, you can then reformat this new column to display the date and time in the format you require.
- For example, in Excel, right-click the column and click Format Cells. Then, select the relevant format code and click OK. Download and follow the Reformatted Timestamp Example if required.
In BI Reporting, data is represented in tables. Select a data table that relates to the information you need to report on. Each data table provides information on the different record types in Simpro Premium.
All data table fields are unique to the type of information that is displayed. A full list of all data tables and fields can be found in Fields for Native Query. The data is then displayed in its raw form by default and all the related fields are also displayed. To display data specific to your query, apply Filters, operators, metrics and grouping .
Data represented in tables is useful for looking up information or finding specific numbers. However, using charts makes it easier to see trends and interpret overall data. The type of report you are building determines the visualisation options available.
Some questions will default to a chart visualisation automatically, instead of a table, when the question is created. This is often the case when a question contains a metric which is grouped. For example, a question that shows how many jobs were created, over the past year, grouped together by project tags might automatically display as a line chart.
You can switch between the data being represented on a table and as a visualisation using the toggle.
Each Visualization has its own list of customisation options. Only the visualisation options logical for your report are accessible.
Under Visualization, you can select from the data representation types listed below. The data representation type you select decides the customisation options available.
|VISUALIZATION Types||Customisation options|
|Number||Add prefixes or suffixes to your number, for example, a (currency symbol in front or a percent at the end,) or set the number of decimal places you want to include.|
|Table and Pivot Table||
|Line, Bar, Combo, Area, Row, Waterfall, Scatter, Funnel and Trend charts||
Data: Select which field to use as your measurement, and which one to use for the pie slices. Customise the legend for the pie chart, as applicable.
Display: Toggle on or off the legend. Show percentages, set the minimum slice percentage, and customise piece colours.
Continuing with the example explained in the previous section, (a report for the busiest time of your business year), the output in a table format is shown below.
You can customise this data representation as a line graph.
Follow the steps below to present the data for the given example as a line graph:
- In the Visualization drop-down list, select Line.
- Click to customise a metric and click Style to rename the metric as required.
- Click for Sum of hours each week, click Style > Sum of Hours and rename it to 'Sum of hours each week'.
- Click Display to update the Line style, Show dots on lines, Goal line, Show value on data points, and Trend line.
- Click Done to save your visualisation settings.
- To view more details about individual records, click the required data points on the line graph.
- Zoom in: For example, when you view records that contain data for an entire week, zoom in to view each day or each hour of that week.
- View these Schedules: View the raw data for each individual record that has contributed to make up the record.
- Category: Select a category to view data in that category that contributes to the relevant value.
- Location: Group the data based on a specific Country or State.
- To further filter the answer displayed, adjust the View by settings displayed at the bottom of the graph.
- Click Save to add the question to the collections for quick reference and use.
- Enter a relevant Name and Description for your question. And, specify the collection to which it should be added.
All questions created in BI Reporting are stored either in Our Data, a common collection accessible to all users logged in to the same Simpro Premium build, or in a personal collection, accessible only to the logged-in user.
- In this example, Name can be Busiest time of the year and Description can be Total number of hours scheduled each week to all employees for the past 3 years. Note that this report excludes contractors. And, save the question to Our Data.
- You can then select to add this newly saved question to a dashboard directly. Alternatively access the question, go to and use the icon to add this question to the dashboard at a later stage.
- Click Yes please! If you want to add the question to a dashboard or Not now to leave it as a stand alone question.
Refer the tables below to understand the different filter categories, filter options or operators, metrics and grouping for your answers.
Use filters to include or exclude specific information based on your question to structure the raw data into a more precise report. When creating a question click Add filters to narrow your answer to see the relevant filters for a data table. The available filters are determined by the content in the table. The filters typically represent a particular field in the selected table.
|Text fields, such as Names, Types, and Custom Fields.|
Numerical or unique ID entries, such as Employee IDs, Site ID, Costs, or Hours.
|Calendar entries, such as Date, Start and End Time.|
|True / False||
Filter to indicate a specific criteria. For instance, to indicate whether check box for a particular option is selected.
|Filter to indicate that these fields are linked other filter categories. For instance, this category can be any of the above mentioned types.|
|Is||Text, ID, Location||Display only the records that include the selected filter.|
|Is not||Text, ID, Location||Display only the records that exclude the selected filter.|
|Contains||Text||Display records that contains the selected filter. Select this operator instead of Is when the field used as a filter in the selected table contains multiple entries.|
|Does not contain||Text||Display records that do not contain the selected filter. Select this operator instead of Is not when the field used as a filter in the selected table contains multiple entries.|
|Is empty||Text, ID, Number, Location||Display only the records that are blank in the selected filter category.|
|Not empty||Text, ID, Location||Display only the records that are not blank in the selected filter category.|
|Starts with||Text||Display only the records that start with the selected filter text. Select this operator when the field used as a filter in the selected table contains multiple entries, such as asset custom fields.|
|Ends with||Text||Display only the records that end with the selected filter. Select this operator when the field used as a filter in the selected table contains multiple entries.|
|Equal to||Number||Display only the records that include the exact number in the selected filter category.|
|Not equal to||Number||Display only the records that exclude the exact number in the selected filter category.|
|Greater than||Number||Display only the records that include numbers greater than the specified number in the selected filter category.|
|Less than||Number||Display only the records that include numbers less than the specified number in the selected filter category.|
|Between||Number||Display only the records that include numbers between the two selected numbers in the selected filter category.|
|Greater than or equal to||Number||Display only the records that include numbers equal to or greater than the specified number in the selected filter category.|
|Less than or equal to||Number||Display only the records that include numbers equal to or less than the specified number in the selected filter category.|
A metric is a number that is derived from your source table. It includes any filters that you have applied to your question in BI Reporting. When creating a question, under Summarize, click Pick the metric you want to see to view the relevant metrics for a data table. Selecting any of the below metrics, returns an answer in the form of a number.
|Count of rows||Calculates and displays the total number of rows in the answer. Each row corresponds to a separate record. For example, to know how many orders in the Orders table were placed with a price greater than $40, first filter by Price greater than 40, and then select Count. With this, the BI Reporting then counts how many orders matched your filter.|
|Sum of||Calculates and displays the sum of all values in a number column. When you select Sum of, you are required to select the relevant column.|
|Average of||Calculates and displays the average of all values in a number column. When you select Average of, you are required to select the relevant column.|
|Number of distinct values of||Calculates and displays the number of unique values in all the cells of a single column. When you select Number of distinct values of, you are required to select the relevant column.|
|Cumulative sum of||Calculates and displays the running total of a specific column. When you select Cumulative sum of, you are required to select the relevant column. This value would be the same as Sum unless you use grouping.|
|Standard deviation||Calculates and displays how much the values of a column vary, plus or minus, from the average of that column. When you select Standard deviation, you are required to select the relevant column.|
|Minimum of||Displays the minimum value in a number column. When you select Minimum of, you are required to select the relevant column.|
|Maximum of||Displays the minimum value in a number column. When you select Maximum of, you are required to select the relevant column.|