Assetas support

Table Report

This page will walk you through the process of designing or editing a table report.

Design a report | Report details | Columns | Filters | Additional filters | Sorting | SQL Select Statement | Security | Validate and run | Report customizationRelated topics 

Icon Light 500px.png
Assetas support

A table report displays text and numeric information in a column format. Data may be linked to create a seamless navigation experience to other areas of the application.

What is a table report?
 
  1. Expand "Reports" on the sidebar navigation.

  2. Select "Design" to design a new report. This will immediately bring you to the Report Details screen.
     

Design a table report
 
Report details
 

The report details include the primary foundational details of the report. Please note that items with an asterisk are required.

Report Details:

Populate the following fields:

  • Category*: The category grouping for this report. You can create your own categories by navigating to Report Categories under the Lists & Types menu under Configuration.

  • Type*: The type of report. Select Table. 

  • Name*: Enter the name of the report.

  • Icon*: Select an icon to represent the report.

  • Data source*: Select the underlying data for the report.

  • Description*: Enter a short description of the report.

  • External Identifier: Create a unique identifier for this report.

Please note that all fields with the exception of the External Identifier are required for this type of data record.

Columns​

Properties specific to the table columns are displayed in this section. Please note that items with an asterisk are required.

Table Column Properties:

Populate the following fields:

  • Column*: The data element chosen for each column. The naming convention is: [Table.Element] (data type).

    • The last selection in the Column dropdown list is the option for Advanced Features.Calculated Field. Enter your variables and mathematical functions into the ‘Calculated Field’ question below.

  • Position*: The ordering of the columns with respect to each other, requires a whole number.

  • Calculated Field: This question will appear if Advanced Features.Calculated Field is selected as the column item. Enter your variables along with your mathematical functions here. SQL Server functions are also supported in this field. For more information on SQL Server functions, click here.
     

  • Assetas supports the following variables; please note that the variables available will depend on the Datasource selected in the Report Details section. Please refer to the Variables section in the Assetas Manual for help with proper syntax.

    • Asset Attribute: {AssetAttribute:<Attribute Type ID or External ID>}

    • Associated Contacts: {AssociatedContacts:<Contact Role ID or External ID>}

    • Contact Attribute: {ContactAttribute:<Attribute Type ID or External ID>}

    • Document Attribute: {DocumentAttribute:<Attribute Type ID or External ID>}

    • Task Attribute: {TaskAttribute:<Attribute Type ID or External ID>}

    • Form Answer: {FormAnswer:<Question ID or External ID>}
       

  • These variables may be further manipulated by applying SQL Server functions (for example, to calculate the difference between two dates, or using IF/THEN/ELSE logic). For a list of commonly used functions, please see Section 8.1 in the Appendix of the Assetas Manual (Calculated field – SQL Server functions).

  • Aggregate: The mathematical function applied to a grouping of data. Leave blank for text data types.

  • RoundingThe number of decimal places to round the result. This field is only visible for numerical fields. To show up to 5 decimal points, without manipulation, choose “No Rounding”.

  • Limit Length*: Sets the number of characters returned. To return the full text string, set to zero.

  • Alias: The column heading name. If left blank, the default will be the name of the data element set in the Column field.

  • Add Link: Adds a hyperlink within the table to link directly to the data elsewhere in the database. Best practice is to limit links only to names (not descriptions), such as asset names, form names, document names, and contact names. Assetas recommends using only linked column per table report.

  • Comments: Any comments you would like to store for this column.

 
Filters

Filters allow the report to dynamically return results based on defined logic, criteria, and order.

Populate the following fields:

  • Group*: Defines the order of operations when using OR logic. If only AND logic is used or you only have a single filter, you’ll only use a single grouping of filters, so select Group 1. If you have any OR logic to apply, you may need to use additional grouping for your filtering logic to work properly.

    For example, if your logic looks something like:
    (A and B and C) OR (D and E), the filter elements within the first set of parenthesis will be in Group 1, and the filter elements within the second set of parenthesis will be in Group 2.
     

  • Precedence*: Defines the order of filters within each Group in relation to the other filters defined and requires a whole number.
     

  • Column*: The data element chosen for each filter. The naming convention is: [Table.Element] (data type). For example, if you want to filter on the type asset, you would select Asset Type.Name. There is a search box within the dropdown that is helpful if you’re not certain which table your data element resides in.
     

  • Operator*: The operator used to apply the filter.

    The Operator value of “contains any of these items” will return all results that meet the entries.  For example, a filter to show only records that “contains any of these items” with “WELL,TANK” would return WELL0001, WELL0002, and TANK0001. 

    The Operator value of “is in the list” is appropriate for matching entire values.

     

  • Value*: The value(s) for which you are applying the filter. There are several options:

    • Enter a single numerical or text value (please note that text values are not case sensitive).

    • A comma-separated list of numerical or text values

    • Use a variable. The Append Variable button allows you to quickly select a variable from a re-defined list, including {Today}, {CurrentUser}, {StartDate}, or {EndDate}. Variables are denoted with curly brackets. Certain variables require you to enter a variable’s unique identifier or external identifier to evaluate properly. For example, for {ContactID}, simply enter the Contact’s unique/external identifier within the curly brackets. For Selection Lists, enter the Selection List report’s external identifier within the curly brackets, like so: {SL:Assetas_SL_Active_Assets}.
       

The two filters {StartDate} and {EndDate} must be applied together; you cannot reference only one of these two in the filters for a report. 


If the filter is referencing one date, please use {AsOfDate}, which uses the current date as the reference point.
 

  • Logical*: Contains either ‘AND’ or ‘OR’ and describes how the filter relates to the following filter (in order of precedence). If the filter directly after this filter is in a different group, then the Logical field will describe how the two groups interact.

 
Additional filters

Additional Filters define the default maximum number of rows to return and sets how to handle distinct rows.

Populate the following fields:

  • Maximum Rows*: Sets the maximum number of rows returned in a report.

  • Distinct Rows: Toggle to remove any duplicate rows.

 
Sorting

Sorting criteria allow the report to display results in the expected order.

Populate the following fields:

  • Position*: Defines the order of sorting rules applied, must be a whole number.

  • Column*: Selects the data element where the sorting is applied, must be one of the data elements returned in the report.

  • Order*: Sets whether the sort is in ascending or descending order. 

 
SQL Select Statment

This is a non-editable box that returns the SQL defined by the report parameters chosen in sections above. The SQL will dynamically adjust as data elements, filters, and sorting rules are changed. If you are comfortable with SQL, reviewing the code within this box may provide additional validation that your report is returning correctly.

No SQL skills necessary! This is simply for validation, if you'd like to use it.

 
Security

In this section you may define which security roles have access to your report.
 

By default, reports are not assigned to any security roles; only the user who created the report and anyone with the “Report Administrator” security function will be able to access the newly created report. 
 

When a report is granted to one or more security roles, users of those security roles will also be able to access it.  You can add single roles at a time, or all roles at once.

 
Validate and run

Validate:  Click the Validate button to check the report for any issues and display any errors found.

Run: The Run button will run your report and will return any data per your report requirements.

A report without validation errors does not necessarily guarantee that the logic in the report is accurate or that the report will return data. It simply affirms that the way the map properties, filters, and sorting rules have been defined allow the report to run. It’s always best practice to click on the Validate button and check there are no errors.

 
Report customization

Certain reports allow for further customization once they are run. Any customization changes made the report will be saved for the next time the report is run and will not impact the original report settings.

Variable inputs: Any variable inputs will display at the top of the report. For example, if you’ve chosen the {StartDate} and {EndDate} or {AsOfDate} variables in your filters, you will see a dropdown list of many possible date range combinations to select from.