Purpose: Orion provides customers with pre-built and custom SQL data queries that can output any data stored in the platform, in a variety of formats, and in custom layouts. These queries can be run ad-hoc through the Orion Connect Query App, scheduled to an SFTP target and/or called ad-hoc through the API.
Simple Use Case: Customer wants to export a list of households and custom fields into a CRM database connected to their Advisor platform. Customer may call the Orion API, providing the Query ID, receive back a response with the .xlsx file, which is then immediately imported into their platform.
Scope and Outputs: This workflow provides access only to queries which already exist (new ones can be built) within the Orion Connect platform. This does not provide the ability to create new queries. The data will be formatted in the same structure as the original query, but can be output to JSON, Slickgrid, simple table, .csv, .xls, or .xlsx.
Process Overview:
- Identify Query (Custom Report) ID and parameters
- Perform API Call with inputs for Custom Report ID and parameters
- Receive response for output
Process Steps:
- Locate Query ID from Orion Connect by launching Query App, navigating or filtering to the desired query, and look at the column called “Query ID”
Example: Query ID 23 = “As Of Value by Account – All Accounts”
- Make GET Request to obtain query parameters. This payload is required for the next POST call to get results.
GET v1/Reporting/Custom/23
{
"runTo": null,
"databaseIdList": null,
"prompts": [
{
"id": 22,
"code": "@AsOfDate",
"prompt": "Enter As of Date",
"promptDescription": "Enter As of Date",
"promptType": "Date",
"defaultValue": "7/21/2021",
"isPromptUser": true,
"sortOrder": 1
},
{
"id": 7616,
"code": "@Status",
"prompt": "Enter Account Status",
"promptDescription": "0 = All Account Types, 3 = Manually Managed Only",
"promptType": "Numeric",
"defaultValue": "0",
"isPromptUser": true,
"sortOrder": 2
},
{
"id": 10614,
"code": "@fkReportCategory",
"prompt": "Enter Reporting Category",
"promptDescription": "Enter \"0\" for all, \"1\" for Performance, \"2\" for Activity Summary, \"4\" for Allocation, \"8\" for Portfolio Detail, \"16\" for Tax Detail.",
"promptType": "Numeric",
"defaultValue": "0",
"isPromptUser": true,
"sortOrder": 3
}
],
"source": 0,
"id": 23,
"scheduleIsActive": null,
"entity": "Advisor",
"isCustom": null,
"isFavorite": null,
"scheduleId": null,
"isLandscape": false,
"reportClass": "",
"isInternal": false,
"guid": "2eca3f25-87a6-4fc2-9146-3feb4394625a",
"promptClass": "",
"reportType": "Query",
"userOwnerId": 2801,
"queryType": "Custom",
"title": "As Of Value by Account - All Accounts",
"isNoteEnabled": false,
"category": "OAS",
"name": "As Of Value by Account - All Accounts",
"description": "Returns the value for all accounts as of a specific date. Does not include zero value Accounts."
}- Make POST Request with response from previous call with required parameter values.
POST /Reporting/Custom/23/Generate
{
"runTo": null,
"databaseIdList": null,
"prompts": [
{
"id": 22,
"code": "@AsOfDate",
"prompt": "Enter As of Date",
"promptDescription": "Enter As of Date",
"promptType": "Date",
"defaultValue": "7/21/2021",
"isPromptUser": true,
"sortOrder": 1
},
{
"id": 7616,
"code": "@Status",
"prompt": "Enter Account Status",
"promptDescription": "0 = All Account Types, 3 = Manually Managed Only",
"promptType": "Numeric",
"defaultValue": "0",
"isPromptUser": true,
"sortOrder": 2
},
{
"id": 10614,
"code": "@fkReportCategory",
"prompt": "Enter Reporting Category",
"promptDescription": "Enter \"0\" for all, \"1\" for Performance, \"2\" for Activity Summary, \"4\" for Allocation, \"8\" for Portfolio Detail, \"16\" for Tax Detail.",
"promptType": "Numeric",
"defaultValue": "0",
"isPromptUser": true,
"sortOrder": 3
}
],
"source": 0,
"id": 23,
"scheduleIsActive": null,
"entity": "Advisor",
"isCustom": null,
"isFavorite": null,
"scheduleId": null,
"isLandscape": false,
"reportClass": "",
"isInternal": false,
"guid": "2eca3f25-87a6-4fc2-9146-3feb4394625a",
"promptClass": "",
"reportType": "Query",
"userOwnerId": 2801,
"queryType": "Custom",
"title": "As Of Value by Account - All Accounts",
"isNoteEnabled": false,
"category": "OAS",
"name": "As Of Value by Account - All Accounts",
"description": "Returns the value for all accounts as of a specific date. Does not include zero value Accounts."
}*You can specify with the POST request your desired format, otherwise .xlsx will be chosen by default.
Possible Formats:
/Reporting/Custom/{key:int}/Generate/xlsx
/Reporting/Custom/{key:int}/Generate/xls
/Reporting/Custom/{key:int}/Generate/csv
/Reporting/Custom/{key:int}/Generate/Table
/Reporting/Custom/{key:int}/Generate/SlickGrid
4. Collect Response as your output
Response:
[
{
“client ID”: 743,
“client Name”: “Adam Smith”,
“client Address”: “”,
“client Address 2": “”,
“client City”: “”,
“client Zip”: “”,
“client State”: ” “,
“client Email”: “”,
“reg ID”: 130,
“is Qualified”: true,
“account ID”: 307,
“client Last Name”: “Smith”,
“client First Name”: “Adam”,…}
…]Process Visualization:
Process Tips or Controls:
- Orion provides other methods to acquire data in real time or in a bulk flat file. If the customer’s goals are to output mass amounts of data, evaluate if the SSIS Portfolio Extract export provides a more efficient and comprehensive solution.
- Data Queries have a timeout threshold of 20 minutes of run time or 1,000,000 rows of data.
- As a best practice, the payload from the GET call is required to make the POST call. While some custom reports may still return without this, this is not to be an expected result and maybe be removed in a future update. Always include the payload in the POST.
- The payload in the POST should not contain quotes (“) on either end of the body. This will invalidate the payload and will cause the API call to error.
- If you’re getting a 201 with the report location, you’ll want to use the following to download the file: /Reporting/Custom/{key}/Run/{filename}