Query Builder API

Description

This is how to call the Query Builder API. An API call is not required to run reports in Query Builder, you can run and export a .csv of the report directly in the Member Portal with the user interface. The API is most often used if you want to schedule the report to run and save the output regularly. Using the API will take some technical experience (basic understanding of how to make calls to an API).

Throughout this document there will be references to your <web_api_url>, this is the Web API URL unique to your instance of Fusion. If you do now know your Web API URL, you can put in a support ticket and we will provide you with this.

More detailed API documentation can be found at: <web_api_url>/help

Making Calls to the Query Builder API

1) Create a User

You will need a user in Fusion that has the security role "View/Run Query Results". That security role item is found under the Reports tab of the specific Security Role settings.

The following is a screenshot of the needed security role within Fusion. Only the highlighted role is needed to run queries. The other marked query is only needed if the user needs to also create/edit queries.

2) Get Bearer Token

That user can get a token by sending a POST request to <web_api_url>/token. This request will need to provide the username and password of the user with the correct security roles in the Body of the request. It will also need to set the grant_type as password.

The token received will be used to authenticate the Query Builder API call. This token has an expires_in that contains the duration in seconds that the token is valid for. Using the token refreshes this expiration time.

The following is an example of that request using Postman:

And what it might look like using cURL:

curl --location '<web_api_url>/token' \--header 'Content-Type: application/x-www-form-urlencoded' \--data-urlencode 'username=<my_username>' \--data-urlencode 'password=<my_password>' \--data-urlencode 'grant_type=password'

3) Validate Token

You can verify that your token is still valid by sending a GET Request to: <web_api_url>/api/Account/ValidateToken with the token in the header. This will result in a simple True/False response.

In postman that request looks like this:

An example using cURL:

curl --location '<web_api_url>/api/Account/ValidateToken' \--header 'Authorization: Bearer <my_token>'

4) Send request to Query Builder API

That user can then send a GET request to the API endpoint for the query you want to run. You will provide the token you received as a bearer token in the header. The URL for the Query Builder API will be <web_api_url>/api/Query/Run?apiId=<api_id>. Where the <api_id> is specific for the query you want to run and can be found by viewing the query in the member portal. The <api_id> will appear both in the API text field on the right side of the page.

The following is an example screenshot of that request using Postman:

And using cURL:

curl --location '<web_api_url>/api/Query/Run?apiId=<my_api_id>' \--header 'Authorization: Bearer <my_token>'

Important Notes

1) The Query Builder API does not accept parameters, it will run the exact SQL that appears in the member portal. You will need to make sure the SQL does not require you to provide parameters (such as dates), but instead has default values set (such as "beginning and end of day yesterday", "the last week", or "right now").

2) Query Builder has a built in 30 second timeout period. This is built into the tool in order to protect your Member Portal from becoming impacted if a very large query is ran. If you run into this, you will need to modify the query to limit the data being pulled (e.g. by reducing the date range of the data being pulled).