Learn about the latest improvements to the Help Guide in our Release Notes.
Last Updated: September 30 2020
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.
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: source data, filters, and answer output. The entire set of data in databases is saved 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 therefore involves deciding what your question is based on and which table contains the required information in it.
Consider the example to create a report for the busiest time of your business year based on the scheduled time calculations. Following instructions detail the steps to ask a new question for the example:
- Go to BI Reporting > Ask a question.
- On the New question page, Select a table from the simPRO build that you are currently using.
- For example, select Schedules from a list of DATA tables in the build.
- For example, select Schedules from a list of DATA tables in the build.
- From the FILTERED BY drop-down list, select a category and a relevant filter to further narrow down your results.
- For example, in the FILTERED BY drop-down, select Contractor Name, which is a text data type. Learn more about data types and filter categories in Filters, operators, and outputs.
- Different fields have different filter options based on their individual data. Under Contractor Name, select the contractors you need to exclude.
- Next, select an operator to determine how the selected filter should apply on the data in the table.
- 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.
- In the example, select metric option as Sum of > Hours to ensure that the report displays the total number of hours for each record.
- For example, in the GROUPED BY field, select Date > by week. This displays each record on the report as a week's view with the set number of hours.
- 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’s value.
- Click to apply more dimensions and add columns to the left of the dimension.
- SORT: Click the column header to sort your answer by one of the columns.
- CUSTOM FIELDS: Create a new field based on the calculations drawn from existing columns. You can only use standard maths operators in your formulas and add parentheses to clarify the order of operations. Although this new field is not permanently added to this table, it is only kept if you save a question that uses it.
- ROW LIMIT: Limit the number of rows displayed in your answer.
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 will include 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.
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, and outputs.
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 determine the visualisation options available.
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||Hide and rearrange fields in the table to match your requirements.|
|Line, Bar, and Area charts||
|Pie||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.|
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 further enhance the applied VISUALIZATION.
- Click DISPLAY and update the Line style, Show dots on lines, Show goal, add a Goal label, and Show trend line.
- Click LABELS and rename the Y-axis Label as Sum of hours each week.
- Click Done to save your visualisation settings.
- Click to download your answer as a CSV, XLSX or JSON file.
- 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 answer.
- 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 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 and use the icon to add this question to the dashboard at a later stage.
Refer the tables below to understand the different filter categories, filter options or operators, and basic metrics 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. 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.|
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. 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.
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.
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.|