# Customized Reporting

Basic reports are easy to generate in ION Analytics. For simple needs, check out the "Building and Sharing Custom Dashboards" resources [here.](https://manual.firstresonance.io/ion-analytics#build-and-share-custom-dashboards)

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.](https://manual.firstresonance.io/ion-analytics#manage-permissions-within-ion)

### 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**](https://manual.firstresonance.io/ion-analytics#build-and-share-custom-dashboards)**"** 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.<br>

   <figure><img src="/files/wJkzSbqCCcCQ0wmKR3Rd" alt=""><figcaption><p>Creating new SQL Element</p></figcaption></figure>

### 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**](https://manual.firstresonance.io/ion-analytics#build-and-share-custom-dashboards)**"** 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.<br>

     <figure><img src="/files/LI0ajf0kgT752JKUCdYp" alt=""><figcaption></figcaption></figure>
3. When you navigate to an element using Custom SQL, the source for that element will appear as shown below.<br>

   <figure><img src="/files/jGnOtnv6oMJExnR5VgCl" alt=""><figcaption></figcaption></figure>
4. From here, select the **SQL toggle** on the element to view the custom code.

<figure><img src="/files/MyJskXKSSh4gy1WzxAri" alt=""><figcaption><p>Location of SQL Editor</p></figcaption></figure>

<figure><img src="/files/oQlYoepIZP5y9DaObhhx" alt=""><figcaption><p>SQL Viewing Mode</p></figcaption></figure>

### Attribute Tables Made Easy with SQL

[Custom attributes](/features/custom-attributes.md) 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.

<table><thead><tr><th width="246">Object</th><th>Attribute Table</th></tr></thead><tbody><tr><td>Purchase Order Lines</td><td>PURCHASE_ORDER_LINES_ATTRIBUTES_BASE</td></tr><tr><td>Purchase Orders</td><td>PURCHASE_ORDERS_ATTRIBUTES_BASE</td></tr><tr><td>Issues</td><td>ISSUES_ATTRIBUTES_BASE</td></tr><tr><td>Runs</td><td>RUNS_ATTRIBUTES_BASE</td></tr><tr><td>Run Steps</td><td>RUN_STEP_ATTRIBUTES_BASE</td></tr><tr><td>Parts</td><td>PART_ATTRIBUTES_BASE</td></tr><tr><td>Parts Inventory</td><td>PARTS_INVENTORIES_ATTRIBUTES_BASE</td></tr><tr><td>Part Kit</td><td>PART_KIT_ATTRIBUTES_BASE</td></tr><tr><td>Procedures</td><td>PROCEDURES_ATTRIBUTES_BASE</td></tr><tr><td>Steps</td><td>STEPS_ATTRIBUTES_BASE</td></tr><tr><td>Receipts</td><td>RECEIPT_ATTRIBUTES_BASE</td></tr><tr><td>Further Actions</td><td>FURTHER_ACTIONS_ATTRIBUTES_BASE</td></tr><tr><td>Suppliers</td><td>SUPPLIERS_ATTRIBUTES_BASE</td></tr><tr><td>Locations</td><td>LOCATIONS_ATTRIBUTES_BASE</td></tr><tr><td>Plans</td><td>PLANS_ATTRIBUTES_BASE</td></tr></tbody></table>

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

```sql
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)
  )
```

<figure><img src="/files/qSZ1twojsqed7kIpH2xW" alt=""><figcaption><p>Purchase Order Attribute Table Pivoted For Easy Access</p></figcaption></figure>

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

{% hint style="info" %}
Please note that First Resonance does not provide support for writing or maintaining custom SQL. If you require expertise in this area, you can contact your organization's Customer Success Manager to discuss Business Solutions that we can offer at an additional cost.
{% endhint %}


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://manual.firstresonance.io/ion-analytics/customized-reporting.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.