Assetas support

Report Creation

This page will walk you through the process of creating a new report.

Create a report | Report details | Raw data | Report properties | Filters | Additional filters | Sorting | SQL Select Statement | Security | Validate and run | Report customizationRelated topics 

 
  1. Expand "Reports" on the sidebar navigation.

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

Create a 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. Options include: Calendar, Chart, Map, Selection List, and 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. If the data doesn't exist within the software, please select 'Raw Data' to enter your own data.

  • 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.

Raw data

If you select ‘Raw Data’ as your Datasource in the Report Details section, you will see a new section to enter your raw data. Please enter your data as pipe-separated text (for example: Low|Medium|High). 

Raw data can be used to create a Selection List report of items not found elsewhere in the system.

 
Report properties

Depending on the type of report selected, properties specific to that report are displayed. Please note that items with an asterisk are required.

Calendar Properties:

Populate the following fields:

  • Title*: Select the data element to use for the calendar item's title. Assetas recommends selecting a short text field.

  • Description*: Select the data element to use for the calendar item's description, which will appear when you hover over the calendar item.

  • Start*: Select the data element that sets the calendar item's start date. This must be a date data type. 

  • End*: Select the data element that sets the calendar item's end date. This must be a date data type. The Start date and End date may use the same data element if the item should only appear on a single day in the calendar.

  • All Day: Toggle to indicate if the calendar item spans the entire day.

  • Background Color*: Select the background color to use for this calendar item.

  • Text Color*: Select the text color to use for this calendar item.  

  • Icon: Select an icon to display alongside the title of the calendar item.

 
Chart Properties:

Populate the following fields:

  • Category*: Data element that appears in the x-axis of a chart. For example, a vertical bar chart of asset counts by type would have the Asset Type.Name data element selected for the Category.

  • Category Format: Used for custom formatting of dates. For example, you could define the Category Format as “MMM yyyy” to show “JAN 2019”.

  • Series: Data element if there are multiple series in a chart.

  • Series Format: Used for custom formatting of dates. For example, you could define the Category Format as “MMM yyyy” to show “JAN 2019”.

  • Value*: Data element that appears in the y-axis of a report. For example, the Value of the vertical bar chart of asset counts by type would simply be Asset.Asset ID (a count of the unique Asset IDs within each Asset Type).

  • Aggregate*: Acts on the Value field as a mathematical function. If you only have a single value that you’re capturing, selecting ‘Maximum’ as the Aggregate will still return your single value.

  • Rounding*: The number of decimal points displayed by the Value field.  

  • Chart Type*: Sets the type of chart displayed (bar, line, etc.). 

  • Legend Position*: Sets whether a legend should appear and where it should appear in relation to the chart data.

Map Properties:

Populate the following fields:

  • Latitude*: The data element used for the latitudinal coordinates. Typically, this will be Location.Latitude.

  • Longitude*: The data element used for the longitudinal coordinates. Typically, this will be Location.Longitude.

  • Label Text: The data element that will be displayed within each map data point label (once the data point is clicked). If nothing is selected, only the latitude and longitude will appear in the labels when the data points are clicked.

  • Hover Text: The data element that will be displayed when you hover over each map data point. If nothing is selected, no hover text will appear within your map.

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. Assetas supports the following variables, and please enter the external identifier in place of ‘ID’:

    • {AssetAttribute:ID}

    • {ContactAttribute:ID}

    • {DocumentAttribute:ID}

    • {TaskAttribute:ID}

    • {FormAnswer:ID}
       

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

  • Limit Length*: Sets the number of characters returned. To return the full text or numeric 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.

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

Selection List Properties:

Populate the following fields:

  • Name: Enter text for the selection list name, which is displayed in the ‘Filters’ section of a report when the selection list is used as a report argument.

  • Value*: The GUID (or Globally Unique Identifier) specific to each record. Select an ID data element, such as Asset.Asset ID or Contact.Contact ID. If you’ve entered Raw Data, this field will automatically populate.

  • Text*: The text that will appear in the selection list dropdown, such as Asset.Name or Contact.Contact Name. Ensure that your Value and your Text data elements align and are sourced from the same data table (both from the Asset table or both from the Contact table, for example). If you’ve entered Raw Data, this field will automatically populate.

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}, {Current User}, {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.
 

Further report customization is available for certain types of reports once the report renders. 

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. If you’ve chosen the {StartDate} and {EndDate} variables in your filters, you will see a dropdown list of many possible date range combinations to select from.

Map report zooming: For a Map report, you may select the level of zooming you’d prefer using the Zoom dropdown at the top of the report once the report runs.

Calendar view: Buttons in the upper right-hand corner of the calendar allow you to toggle between month, week, day, and agenda views.

 
Related topics