Please Note: You must download, fill out, and attach the Query Request Columns and Data Sources template spreadsheet with all of the columns and data sources for those columns that you want to see in the custom query. You must also include LIVE reproducible data that you expect to see on the query so that Orion can verify whether or not the query is pulling the expected data. Neglecting to include a detailed, filled-out template with your query request will directly impact the time it takes for your query to be completed. 

 

Article Quick Links

Why?

How?

Process Overview and What to Expect

Submitting a Query Request

Required Fields

Support Service Team Subject New or Existing Existing Query ID and Name Description of Functionality Query Name

Query App Description Text Prompts Custom Calculations Exclusions Columns

 

 

Why Would I Request a Custom Query?

• Make changes to existing queries to better fit your firm's needs.
  
  
• Add or remove columns to queries that already exist within Orion.
  
  

Common Use Cases for Custom Queries

Advisor wants a custom query to see As of Value by client by state.

Value Financial wants to see all of their clients that hold multiple accounts with them.

 

 

 

 

How Do I Request a Custom Query?

Process Overview and What to Expect

The query request process helps ensure that Orion fully understands what you need your query to do and what it should return.

Partner with us by providing the following information, and supplying clear, reproducible examples of your expected output. Investing time in this crucial first stage has a direct and positive impact on the time it takes to complete your query request.

• The first step is to fill out and submit your query request. Once you have submitted it, a Subject Matter Expert is assigned and reviews the case. If any of the fields are blank or require additional clarification, the SME contacts you via the case for further clarification and details.
   
  • Once Orion reviews and verifies accuracy, the SME creates a project for the query writing team to process.
    
    
    

• Next, the query request is worked on by a programmer on the query writing team. All query requests are worked on in the order they are received. Since every query is unique (we build to your specifications!), we are not able to offer estimated completion dates.
   
  • The SME assigned to your case may reach out to you for further information if the programmer creating your query has any questions.

Please conduct all communication regarding the query request within your case. Reaching out to us via other channels may add confusion, directly impacting the time it takes to complete your query request.

 

 

 

 

Submitting a Query Request

To request a new query or to modify an existing query, create a new case in the Orion Support app :

• Click on Contact Us > Orion Support in the upper right of your Orion screen or click on the Support app  in your app dock. 

• Click on the button from the Orion Support landing Page.
   

IMPORTANT: If requesting updates to multiple Queries, please submit a separate request for each Query. 
 
There are three tabs that represent the three types of cases you create. Select the tab named: Query Request. Then, fill out all of the fields as outlined in this article. Refer to the Required Fields section to assist you with filling out these fields. 


When submitting the case, you must provide an Excel file as a reference for the columns included in your Query. Please start by downloading this file, Query Request Columns and Data Sources, by clicking on the    button on the page that opens from that link to download it to your computer. Fill out this file as indicated in the Columns section of this article. 

Once you have filled out the template, start filling out the required fields on the case. The rest of this article walks through each field and how to fill it out. 

 

Back To Top
 

Required Fields

When submitting the custom query request, you need to provide details in the following required fields. Please fill out these fields as described below, in the format shown in the examples for each field.
 

Support Service Team

Choose the most appropriate service team from the dropdown selector to submit this query request to. For example, if the majority of the columns in your desired query are billing related, please direct it to the Billing SME team. If the query includes data from multiple teams, and you are not sure which team to direct it to, please direct it to the Reporting SME team. 

Note: The Reporting team receives a high volume of query requests. Directing it to the most applicable team whenever possible ensures that your query is completed accurately and as quickly as possible.

 

Subject

Please enter a subject for the request that clearly states your request.

Back To Top

New or Existing

Is this a New Query or are we modifying an Existing Query? A new query is one that does not currently exist as either an Orion Query or a Custom Query that was previously created for your firm.

To view existing queries:

• Launch the Query app  and select Orion Queries (1) from the left sidebar.

• In the Query Lists section of the right sidebar, select All Queries to see all Orion queries or Custom Queries to see all custom queries previously created for your firm (2).

Back To Top

         

Existing Query ID and Name

If this Query request is modifying an Existing Query or if you are using an Existing Query as a template, please provide the Title (name) and ID of the original Query. If this is an entirely new Query that does not reference an existing query, please enter: None

 

The ID and name of a query are found in the Query app in the columns Query ID and Title
 

 

Back To Top

Description of Functionality

Please provide a description of the query's desired functionality, or detail the requested changes. Be descriptive but concise. This field provides context for the information provided in the Excel template at the end of this form. It should provide a clear understanding to the programmer working on your query request on what is being requested.

Back To Top

Query Name

What should the query be named? If this is an update to an existing Custom Query, and you would like to keep the same name, enter: SAME.
 

The Query Name must start with your Firm's name or an abbreviation of it. If it is not provided here, it will be added from the information entered in: Firm Profile > Contact Information > Abbreviation.

 

 

  

Back To Top

Query App Description Text

If you would like a short one or two-sentence description to appear next to the Query Title (name) in the Query app, enter that description here. Use this field to provide users at your firm with a concise description of what the query does.
 

Back To Top

Prompts

Prompts are fields that help filter the results and/or define parameters when running the query. Please list and describe any prompts needed when running the query, such as: Date Range (helpful for targeting transactions or other activity in a specific period) or Household ID (to run for a specific Household). 

IMPORTANT NOTE: It is very important to communicate everything you need us to understand. If you need more space to list everything, please use the second sheet in the Excel file named Additional Information and simply put "See Spreadsheet" in this field.

NOTE: Including " '0' for all *** by default " tells us that you want to be able to use that parameter as a filter occasionally AND you also want the ability to enter '0' when you do NOT want to filter on that criteria.

 Back To Top

Custom Calculations

Please provide a breakdown of any Custom Calculations needed in the Query. 

A Custom Calculation is any calculation that the query needs to run rather than simply pulling information from a field. 

Each custom calculation entered should follow the following format:

• Custom calculation name.

• A description of what the calculation should do.

• The formula for the custom calculation.

• The formula worked with the live data from the Excel template and the expected result.

 

The programmer working on your query request needs this live data in order to test your query and ensure it is returning what you expect.

IMPORTANT NOTE: It is very important to communicate everything you need us to understand. If you need more space to list everything, please use the second sheet in the Excel file named Additional Information and simply put "See Spreadsheet" in this field.

 

Even if it seems like a simple and obvious calculation, such as the average daily value of an account, it is critical to provide the exact formula for arriving at your intended result. This prevents confusion or misunderstanding your intent when building your query, which directly impacts the time it takes to complete your query request.

Please give each custom calculation the same name as its corresponding column in the Excel template. That way, the programmer creating your query can cross-reference them.

Back To Top

Exclusions

Should the results exclude any entities from the returns? Some examples would be: Exclude unmanaged Accounts, Exclude Employee Households, Exclude specific tickers. Provide the source of the data to 'exclude by' in parenthesis. 

IMPORTANT NOTE: It is very important to communicate everything you need us to understand. If you need more space to list everything, please use the second sheet in the Excel file named Additional Information and simply put "See Spreadsheet" in this field.

Back To Top

Columns

Enter the column names in the entry field, each followed by a comma.
 

Within the  Query Request Columns and Data Sources, click the    button on the page that opens from that link to download it to your computer.

In addition to entering the column names in this field, you also need to fill out the Excel file template with all columns that are included in the completed query, named, and in the order you would like. It also must contain the source of each column's data and live data that can be tested against, along with the date or period the live data is "as of" in the Live Data row. We use this information to test and verify that the query accomplishes your requested functionality.

IMPORTANT NOTE: It is very important to communicate everything you need us to understand. If you need more space to list everything, please use the second sheet in the Excel file named Additional Information and simply put "See Spreadsheet" in this field.

The template file has 4 rows that must be filled out:

• Column Name: The name of the column as it should appear in your final query.

• Data Source: The source of the data for this column. Provide either the location of the data in Orion or enter "Custom Calculation". 

  • Any Data Source with "Custom Calculation" must have a corresponding calculation entered in the Custom Calculation field above.

• Live Data: Actual data from your database that corresponds with this column. If the column is a custom calculation, enter the expected result of the custom calculation. See the Custom Calculations section of this document for more information.

• Live Data Date: The date or period that the live data is "as of". 

Example:

Once all of the required fields and the template have been filled out accordingly, please attach it to your case request by clicking on the button located at the bottom. Then, when you're ready to process the request, click the   button. 

Back To Top