Customized Reporting

Basic reports are easy to generate in ION Analytics. For simple needs, check out the "Building and Sharing Custom Dashboards" resources here.

If you're looking to gain deeper insights, writing custom SQL is a powerful tool to aggregate and transform data within your reports—enabling capabilities beyond what traditional joins and calculations offer.

Using SQL To Customize Reporting

Permissions

The permissions to edit SQL use the same analytics permissions currently required to edit dashboards in ION Analytics. You can find more details here.

Creating a SQL Element

To create a new SQL driven element you must first have a dashboard in edit mode. If you’re working with one of ION's standard dashboards, you’ll need to use the "Save As" option to create an editable copy.

1. Navigate to the "Add Element" tab in the top left of the screen.

2. Select a "Data Element" you would like to add. (Table, chart, or pivot)

3. Select SQL as your source at the bottom of the "Select Source" window.

   

Accessing the SQL Editor

Users with the appropriate ION permissions (GenerateWriteEmbeddedAnalytics) can access the SQL used in dashboards by following these steps:

1. Locate a Dashboard Element Using SQL

   • Enter editing mode to access the dashboard elements.

   • If you’re working with one of ION's standard dashboards, you’ll need to use the "Save As" option to create an editable copy.

2. Navigate to the Source Element

   • Once in editing mode (indicated at the bottom right, as shown in the image below), you can navigate to the source element of any dashboard component.

   • Select the first option "Go to source element" in the element dropdown to view that elements source.

     
3. When you navigate to an element using Custom SQL, the source for that element will appear as shown below.

   
4. From here, select the SQL toggle on the element to view the custom code.

Attribute Tables Made Easy with SQL

Custom attributes are a critical part of ION, enabling each organization to capture data tailored to their specific use case. To draw meaningful insights from these fields, it’s essential to ensure they are accessible within ION Analytics. Below, you'll learn how to quickly pivot attribute tables to join them back to their core object, giving you all the data needed for informed decision-making.

To access attributes in your environment, refer to the OBJECT_attributes tables. Running the following SQL query against these tables will output a dataset with the OBJECT_ID and its associated populated attributes.

The example below focuses on extracting PURCHASE_ORDER_LINE_ATTRIBUTES, as indicated by the table name at the end of the query template.

      {{#raw system::CurrentUserAttributeText::env}}_firstresonance.{{#raw system::CurrentUserAttributeText::schema_name}}.PURCHASE_ORDER_LINES_ATTRIBUTES_BASE

The {{#raw system::CurrentUserAttributeText::env}} and {{#raw system::CurrentUserAttributeText::schema_name}} fields formatted above ensure your organization specific data is pulled into analytics. There is no need to replace these fields, all you have to do is replace the name of the table which you want to pull data from.

You can replace PURCHASE_ORDER_LINE_ATTRIBUTES at the end with any of the following options to access the corresponding attributes.

Here is the full SQL and resulting table that is generated for PO Line Attributes:

with po_line_attributes as (
    select
      purchase_order_line_id,
      key,
      coalesce(
        _string_value,
        _number_value:: text,
        _datetime_value:: text,
        _boolean_value:: text,
        _multiselect_values,
        _select_value
      ) as value
    from
      {{#raw system::CurrentUserAttributeText::env}}_firstresonance.{{#raw system::CurrentUserAttributeText::schema_name}}.PURCHASE_ORDER_LINES_ATTRIBUTES_BASE
)
select
  *
from po_line_attributes PIVOT (
    MIN(value) for key in (ANY order by key)
  )

Once you have an attribute table pivoted, you can easily join it with other datasets in your workbook to analyze attributes related to each object ID. For example, the table above can be joined on Purchase Order Line ID with the Purchase Order Line table, providing a comprehensive view of your organization-specific attributes.

To see how this works in practice, check out the "Pivoted Attributes Tables" dashboard in your ION Analytics home folder.