How to Create Questions

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:

BI Reporting

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:

1. Go to BI Reporting > + New > Question.
2. 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.
3. Select the table that you want to report on.
   • For example, select Schedules from a list of Data tables in the build.
4. 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.
5. 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.
6. 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.
7. 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.
8. 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.
9. Click Add filter to populate the Filter field with your selection of filters.
10. 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.
11. 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.
12. 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.
13. 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.
14. Click Visualize to display the results table.
15. 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.

Download a question

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:

1. Download the CSV or XLSX file and open it in your spreadsheet program.
2. Add another column next to the column with the timestamp.
3. In this new column, apply the following formula:
   • =DATEVALUE(MID(A1,1,10))+TIMEVALUE(MID(A1,12,8))
   • Make sure that you replace 'A1' with the column number for timestamp column.
4. 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.

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:

1. In the Visualization drop-down list, select Line.
2. 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'.
3. Click Display to update the Line style, Show dots on lines, Goal line, Show value on data points, and Trend line.
4. Click Done to save your visualisation settings.
5. 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.
6. To further filter the answer displayed, adjust the View by settings displayed at the bottom of the graph.
   
7. Click Save to add the question to the collections for quick reference and use.
8.  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.
9. 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.
10. 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.

Filter Categories

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.

Filter types	Description
	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.

Operators

Basic Metrics

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.

For additional training, complete an interactive material in the Simpro's Learning Toolbox. Learn more in About Simpro's Learning Toolbox.