Overview

The following document outlines common use cases for Data Connect.

Who Is This Guide For?

The intended audience for this guide is data analysts and business administrators interested in Data Connect.

Onboarding Package

Contact your account manager to get started using Data Connect. They will help guide you through the steps of obtaining access credentials (onboarding package) and will provide you with a support liaison.

Authentication Options

See the table below to determine which method of authentication is best for you.

If… Then…
You want to authenticate using a web browser See Browser Authentication
You want to authenticate using only a username and password See Basic Authentication
You are using a program such as Excel that does not support headers See URL Parameter Authentication
The above options do not apply to you See Obtaining an Access Token

Authentication Priority

If multiple methods of authentication are provided, priority is resolved as follows:

  1. Bearer Token
  2. Basic Token
  3. Auth URI Parameter
Example
URI Parameters Headers Token Used
Auth=AAAA Authorization: Bearer BBBB
Authorization: Basic CCCC
BBBB
Auth=AAAA Authorization: Basic CCCC CCCC
Auth=AAAA   AAAA

Browser Authentication

Data Connect supports Basic Authentication without a token using a web browser.

To use this method,

  1. Determine the full URL of the request
  2. Enter the URL into a web browser and press Enter
  3. Enter your username and password into the prompt that appears

It is important to note that this method will only work if there is NO TOKEN (header) on the request, otherwise the provided token will be used.

Basic Authentication

Basic Authentication allows you to authenticate using a username and password to create a Basic Token which will remain valid as long as your username and password do not change.

To generate a Basic Token, you can use the request Basic Authentication with Platform Credentials.

It is important to note that value of the Authorization header in this request is a base 64 encoded version of your username and password. This value is not URL safe and should not be used as a Basic Token for authentication.

Example Request
GET https://rqdataconnectdemo.iqmetrix.net/session
Authorization: Basic vRMt17P8rIBWWbVbLzLWjTNyLfvESgA2yfXhze0WZN7HSIl4slGV866p9dSw

Example Response

HTTP 200 Content-Type: application/json
{
    "access_token": "b0k0dY70N3Vv9jR1b9oEdW9IeT5WIn85WCYFJRo6AiIKLEMBFwNbEQsfeCUeM3gdPA1gAAVxWTJacX8mJyBaGRcFVwQOEV49NgBz",
    "isValid": true,
    "ParentEntityId": 21090,
    "RQEmployeeId": 3494,
    "basic_token": "vRMt17P8rIBWWbVbLzLWjTNyLfvESgA2yfXhze0WZN7HSIl4slGV866p9dSw",
    "PreferredEndpoint": "https://rqdataconnect18.iqmetrix.net"
}

The basic_token is placed in the Authorization header of a request prefixed by the word Basic.

Example
GET https://rqdataconnectdemo.iqmetrix.net/bridge/current/schema
Authorization: Basic vRMt17P8rIBWWbVbLzLWjTNyLfvESgA2yfXhze0WZN7HSIl4slGV866p9dSw

URL Parameter Authentication

URL Parameter Authentication allows you to authenticate without headers, such as when…

To authenticate using this method, you must first generate a Basic Token. This token will remain valid as long as your username and password do not change. See Basic Authentication for examples.

Once you have a Basic Token, it can be placed as a URL parameter in a request using the key Auth.

Example
https://rqdataconnectdemo.iqmetrix.net/bridge/current/schema?Auth=vRMt17P8rIBWWbVbLzLWjTNyLfvESgA2yfXhze0WZN7HSIl4slGV866p9dSw

Compression

Compression Options

Data Connect supports the compression algorithms gzip and deflate in the Accept-Encoding header.

To learn more about Data Compression, see Data Compression 101.

The examples below demonstrate how to use gzip or deflate compression when making a request.

gzip example
GET https://rqdataconnectdemo.iqmetrix.net/session
Authorization: Basic vRMt17P8rIBWWbVbLzLWjTNyLfvESgA2yfXhze0WZN7HSIl4slGV866p9dSw
Accept-Encoding: gzip
deflate example
GET https://rqdataconnectdemo.iqmetrix.net/session
Authorization: Basic vRMt17P8rIBWWbVbLzLWjTNyLfvESgA2yfXhze0WZN7HSIl4slGV866p9dSw
Accept-Encoding: deflate

DateRange

For endpoints with URL parameters StartDate and StopDate, Data Connect provides a convenience URL parameter DateRange which can be used to alias StartDate and StopDate.

The table below provides a list of acceptable values for DateRange and examples of how each alias is resolved into StartDate and StopDate values by Data Connect.

All examples in the table assume the date “Today” is April 5th, 2017 at 12:45:00 PM.

Value Usage Start/Stop Example
Today DateRange=Today StartDate=4/5/2017 12:00:00 AM&StopDate=4/5/2017 12:45:00 PM
Yesterday DateRange=Yesterday StartDate=4/3/2017 11:59:59 PM&StopDate=4/4/2017 11:59:59 PM
LastWeek DateRange=LastWeek StartDate=3/26/2017 12:00:00 AM&StopDate=4/1/2017 11:59:59 PM
LastMonth DateRange=LastMonth StartDate=3/1/2017 12:00:00 AM&StopDate=3/31/2017 11:59:59 PM
LastQuarter DateRange=LastQuarter StartDate=1/1/2017 12:00:00 AM&StopDate=3/31/2017 11:59:59 PM
WeekToDate DateRange=WeekToDate StartDate=4/2/2017 11:59:59 PM&StopDate=4/4/2017 11:59:59 PM
MonthToDate DateRange=MonthToDate StartDate=4/1/2017 12:00:00 AM&StopDate=4/4/2017 11:59:59 PM
QuarterToDate DateRange=QuarterToDate StartDate=4/1/2017 12:00:00 AM&StopDate=4/4/2017 11:59:59 PM
YearToDate DateRange=YearToDate StartDate=1/1/2017 12:00:00 AM&StopDate=4/4/2017 11:59:59 PM
FullYear* DateRange=FullYear&DateRangeValue=2016 StartDate=1/1/2016 12:00:00 AM&StopDate=12/31/2016 11:59:59 PM
Everything DateRange=Everything StartDate=1/1/1753 12:00:00 AM&StopDate=4/4/2017 11:59:59 PM

*FullYear requires an additional URL parameter DateRangeValue={Year}.

Response Options

Data Connect supports multiple response types using the Response URI parameter.

If no parameter is provided the default response type is JSON.

See the table below for a complete list of response types.

Format Notes Example URL
JSON Default response type  
JSON (DataTable) Contains extra data about report columns /?Response=JsonDataTable
CSV Any commas in the response are surrounded by quotes (“) /?Response=Csv
CSV (Email) Report will be sent as an email with CSV file attachment. See Email Response /?Response=CSV.Email&Email.To=test@test.com&Email.CC=test@test.com&Email.Subject=Test1&Filename=MyReport
CSV (FTP) Report will be delivered using FTP. See FTP Response /?Response=CSV.FTP&FTP.Host=ftpserver.com&FTP.Port=21&FTP.Username=default&FTP.Password=default&FTP.Path=/upload/&Filename=MyReport
CSV (FTPS) Report will be delivered using FTPS. See FTPS Response /?Response=CSV.FTPS&FTPS.Host=127.0.0.1&FTPS.Port=990&FTPS.Username=default&FTPS.Password=default&FTPS.Path=/upload/&Filename=MyReport
CSV (SFTP) Report will be delivered using SFTP. See SFTP Response /?Response=CSV.SFTP&SFTP.Host=127.0.0.1&SFTP.Port=22&SFTP.Username=default&SFTP.Password=default&SFTP.Path=/upload/&Filename=MyReport
XML   /?Response=Xml
XML (DataTable) Contains extra data about report columns /?Response=XmlDataTable
XML (Email) Report will be sent as an email with XML file attachment. See Email Response /?Response=XML.Email&Email.To=test@test.com&Email.CC=test@test.com&Email.Subject=Test1&Filename=MyReport
XML (FTP) Report will be delivered using FTP. See FTP Response /?Response=XML.FTP&FTP.Host=ftpserver.com&FTP.Port=21&FTP.Username=default&FTP.Password=default&FTP.Path=/upload/&Filename=MyReport
XML (FTPS) Report will be delivered using FTPS. See FTPS Response /?Response=XML.FTPS&FTPS.Host=127.0.0.1&FTPS.Port=990&FTPS.Username=default&FTPS.Password=default&FTPS.Path=/upload/&Filename=MyReport
XML (SFTP) Report will be delivered using SFTP. See SFTP Response /?Response=XML.SFTP&SFTP.Host=127.0.0.1&SFTP.Port=22&SFTP.Username=default&SFTP.Password=default&SFTP.Path=/upload/&Filename=MyReport
Excel File Binary .xlsx File that does not support live refresh /?Response=Excel
Excel (Email) Report will be sent as an email with Excel file attachment. See Email Response /?Response=Excel.Email&Email.To=test@test.com&Email.CC=test@test.com&Email.Subject=Test1&Filename=MyReport
Excel (FTP) Report will be delivered using FTP. See FTP Response /?Response=Excel.FTP&FTP.Host=ftpserver.com&FTP.Port=21&FTP.Username=default&FTP.Password=default&FTP.Path=/upload/&Filename=MyReport
Excel (FTPS) Report will be delivered using FTPS. See FTPS Response /?Response=Excel.FTPS&FTPS.Host=127.0.0.1&FTPS.Port=990&FTPS.Username=default&FTPS.Password=default&FTPS.Path=/upload/&Filename=MyReport
Excel (SFTP) Report will be delivered using SFTP. See SFTP Response /?Response=Excel.SFTP&SFTP.Host=127.0.0.1&SFTP.Port=22&SFTP.Username=default&SFTP.Password=default&SFTP.Path=/upload/&Filename=MyReport

Email Response

Using Data Connect you can sent a report to an email address with the following URL parameters:

URL Parameter Required Notes Example
Response={ResponseType} true Acceptable values include: Excel.Email, XML.Email, CSV.Email Excel.Email
Email.To={Emails} true Comma separated list of email addresses for the TO field of the email test@test.com,john@kentel.com
Email.CC={Emails} false Comma separated list of email addresses for the CC field of the email test@test.com,john@kentel.com
Email.Subject={Subject} false Subject line for email, defaults to {Endpoint}-{Date}.{FileType} Test1
Filename={FileName} false File name for attachment, defaults to {Endpoint}-{Date}.{FileType} MyReport

The email will be sent from the address iQmetrix DataConnect Delivery Service <noreply@iqmetrix.net> with the report included as an attached file with the specified name.

Example Request
GET https://rqdataconnectdemo.iqmetrix.net/lists/reasoncode?Response=Excel.Email&Email.To=test@test.com&Email.CC=test@test.com&Email.Subject=Test1&Filename=MyReport
Example Response
HTTP 200
Example Email Response

The response email has the following properties:

Data Connect Email Response

FTP Response

Using Data Connect you can sent a report through FTP with the following URL parameters:

URL Parameter Required Notes Example
Response={ResponseType} true Acceptable values include: Excel.FTP, XML.FTP, CSV.FTP Excel.FTP
FTP.Host={Host} true FTP host ftpserver.com
FTP.Username={Username} true FTP username default
FTP.Password={Password} true FTP password default
FTP.Port={Host} false FTP port 21
FTP.Path={Path} false FTP path, will default to / /upload/
Filename={Filename} false File name, defaults to {Endpoint}-{Date}.{FileType} MyReport

The file will be sent to the FTP location using the specified name or default if not provided.

Example Request
GET https://rqdataconnectdemo.iqmetrix.net/lists/reasoncode?Response=Excel.FTP&FTP.Host=ftpserver.com&FTP.Port=21&FTP.Username=default&FTP.Password=default&FTP.Path=/upload/&Filename=MyReport
Example Response (Success)
HTTP 200
{
    "Status": "Success",
    "Message": "226 Successfully transferred '/upload/stores-2017-01-31.xlsx'\r\n"
}
Example Response (Failure)
HTTP 500
{
    "Error": true,
    "Message": "The remote name could not be resolved: 'FailFTPExample.com'"
}

FTPS Response

Using Data Connect you can sent a report through FTPS with the following URL parameters:

URL Parameter Required Notes Example
Response={ResponseType} true Acceptable values include: Excel.FTPS, XML.FTPS, CSV.FTPS Excel.FTPS
FTPS.Host={Host} true FTPS host 127.0.0.1
FTPS.Username={Username} true FTPS username default
FTPS.Password={Password} true FTPS password default
FTPS.Port={Host} false FTPS port 990
FTPS.Path={Path} false FTPS path, will default to / /upload/
Filename={Filename} false File name, defaults to {Endpoint}-{Date}.{FileType} MyReport

Note that Data Connect only supports implicit FTPS and does not perform client SSL certificate validation.

The file will be sent to the FTPS location using the specified name or default if not provided.

Example Request
GET https://rqdataconnectdemo.iqmetrix.net/lists/reasoncode?Response=Excel.FTPS&FTPS.Host=127.0.0.1&FTPS.Port=990&FTPS.Username=default&FTPS.Password=default&FTPS.Path=/upload/&Filename=MyReport
Example Response (Success)
HTTP 200
{
    "Status": "Success"
}
Example Response (Failure)
HTTP 404
{
    "Error": true,
    "Message": "The requested resource does not exist"
}

SFTP Response

Using Data Connect you can sent a report through SFTP with the following URL parameters:

URL Parameter Required Notes Example
Response={ResponseType} true Acceptable values include: Excel.SFTP, XML.SFTP, CSV.SFTP Excel.SFTP
SFTP.Host={Host} true SFTP host 127.0.0.1
SFTP.Username={Username} true SFTP username default
SFTP.Password={Password} true SFTP password default
SFTP.Port={Host} false SFTP port 22
SFTP.Path={Path} false SFTP path, will default to / /upload/
Filename={Filename} false File name, defaults to {Endpoint}-{Date}.{FileType} MyReport

The file will be sent to the SFTP location using the specified name or default if not provided.

Example Request
GET https://rqdataconnectdemo.iqmetrix.net/lists/reasoncode?Response=Excel.SFTP&SFTP.Host=127.0.0.1&SFTP.Port=22&SFTP.Username=default&SFTP.Password=default&SFTP.Path=/upload/&Filename=MyReport
Example Response (Success)
HTTP 200
{
    "Status": "Success"
}
Example Response (Failure)
HTTP 404
{
    "Error": true,
    "Message": "The requested resource does not exist"
}

Running a Report

This example will demonstrate how Data Connect can be used to get the data that drives a report in RQ.

Step 1 - Selecting a Report

See Request List for a list of available requests in Data Connect.

For this example we will retrieve the data for the Graphed Performance Metrix Report in RQ using the request Getting the Graphed Performance Metrix Report.

This example will use Browser Authentication for authentication, no compression and the default response type (json).

(Optional) Step 2 - Setting a Report Level

Many reports allow you to optionally specify a company tree level at which to perform the report.

To set the report to a specific level, determine the available levels using Getting Location Types.

Example Request
GET /lists/LocationType

Example Response

HTTP 200 Content-Type: application/json
[
    {
        "Type": "Company"
    },
    {
        "Type": "Region"
    },
    {
        "Type": "District"
    },
    {
        "Type": "Store"
    },
    {
        "Type": "Channel"
    }      
]

This example will run the report at the Store level.

(Optional) Step 3 - Specifying Report Level

To run the report for all nodes at a level, such as All Locations or All Channels, LocationTypeIDs is set to -1 or *.

To run the report for a subset of nodes at a level, such as one Location or one Region, LocationTypeIDs should be set to a comma separated list of identifiers.

A list of identifiers in a level can be retrieved using Getting Location Ids. The LocationType URI parameter is the value determined in Step 1.

Example Request
GET /lists/LocationIDs?LocationType=Store

Example Response

HTTP 200 Content-Type: application/json
[
    {
        "ID": 6923,
        "Description": "Tampa",
        "EntityID": 453,
    },
    {
        "ID": 6924,
        "Description": "Fairweather",
        "EntityID": 454,
    },     
    {
        "ID": 6926,
        "Description": "Cornwall",
        "EntityID": 466,
    },             
    ...
]

This example will use IDs 6923 and 6924 to specify the report will be run for two stores.

Step 4 - Determining URI Parameters

Each report has different URI parameters used to determine the contents of the report.

For this example, the following URI parameters will be used.

URI Parameter Description Example Value
PeriodId Identifier for a Period in RQ, or -1 if StartDate and StopDate are used -1
StartDate Date to start report 1970-01-01T12:00:00.000Z
StopDate Date to end report 2016-01-01T12:00:00.000Z
HideRebates A flag to indicate if rebates should be included in the report -1

Step 5 - Getting the Report

The values from Steps 1-4 can be combined to get the Performance Metrix Report using Getting the Graphed Performance Metrix Report.

Example Request
GET /reports/graphedperformancemetricsreport_performancemetrix?LocationType=Store&LocationIDs=6923,6924&PeriodID=-1&StartDate=1970-01-01T12:00:00.000Z&StopDate=2016-01-01T12:00:00.000Z&HideRebates=-1&LanguageCode=en-us

Example Response

HTTP 200 Content-Type: application/json
[
    {
        "ID": 6,
        "Name": "Accessories",
        "AttachmentRate": "1249 : 2",
        "AttachmentRateTarget": "0 : 0",
        "DisplayFormat": 2,
        "IsAttachmentRate": true,
        "Margin": null,
        "MarginTarget": null,
        "Priority": -1,
        "Profit": null,
        "ProfitTarget": null,
        "Quantity": null,
        "QuantityTarget": null,
        "Sales": null,
        "SalesTarget": null
    },
    ...
]

Importing into Excel

This section explains how you can import data from Data Connect directly into Excel (2016).

For this example, we will get the Inventory Listing Report.

Step 1 - Load From Web

First, open Excel and create a new workbook.

In the top bar, click on Data -> New Query -> From Other Sources -> From Web.

Load query

Step 2 - Enter URL

Enter the Request URL into the URL box and click OK.

Ensure the Request URL includes Response=Xml so Excel can load the data.

Response=Excel is not used as it outputs a binary file

Enter URL

Step 3 - Access Web Content

If this is the first time you are accessing the endpoint in Excel, a window will appear with authentication options.

Select Basic in the sidebar and enter your username and password, then click Connect.

To learn more about authentication methods, see Authentication Options.

Access Web Content

Step 4 - Select Record

A navigator will appear and load tables into a list.

Select a record and click the Load button.

Select Record

Step 5 - View Table

The record will be placed into a table in Excel, as shown below.

Results

Importing into PowerBI

This section explains how you can import data from Data Connect directly into PowerBI.

For this example, we will get the Cashout Summary Report.

Step 1 - Get Preferred URL

We recommend using the preferred data connect endpoint for best performance.

The preferred endpoint can be determined using the request Basic Authentication with Platform Credentials.

In the Authorization header of the request, place the base 64 encoded form of your username and password prefixed by the word Basic.

Example Request
GET /session
Authorization: Basic vRMt17P8rIBWWbVbLzLWjTNyLfvESgA2yfXhze0WZN7HSIl4slGV866p9dSw
Example Response
HTTP 200 Content-Type: application/json
{
    "access_token": "b0k0dY70N3Vv9jR1b9oEdW9IeT5WIn85WCYFJRo6AiIKLEMBFwNbEQsfeCUeM3gdPA1gAAVxWTJacX8mJyBaGRcFVwQOEV49NgBz",
    "isValid": true,
    "ParentEntityId": 21090,
    "RQEmployeeId": 3494,
    "basic_token": "vRMt17P8rIBWWbVbLzLWjTNyLfvESgA2yfXhze0WZN7HSIl4slGV866p9dSw",
    "PreferredEndpoint": "https://rqdataconnect18.iqmetrix.net"
}

From the response we can determine the endpoint to use for best performance is https://rqdataconnect18.iqmetrix.net.

Step 2 - Load From Web

In PowerBI, click on Get Data from the Home Ribbon and select Web.

PowerBI Get Data

Step 3 - Enter URL

The From Web wizard will appear, ensure Basic is selected.

PowerBI Enter URL

The URL field should be the preferred URL from Step 1 followed by the API endpoint for the request (e.g. /reports/CashOutSummaryReport) and any required or optional parameters needed for the request.

For this request we will use the following parameters:

Example
https://rqdataconnect18.iqmetrix.net/reports/CashOutSummaryReport?LocationType=Store&LocationTypeIDs=51&DateRange=LastMonth&Response=xml

Step 4 - Access Web Content

The Access Web content screen will appear, select Basic from the left and provide your Username and Password.

To learn more about authentication methods, see Authentication Options.

PowerBI Authenticate

Step 5 - Select Record

The Navigator screen will appear where you can preview/edit and load the result data set from the API.

Select a record and click the Load button.

PowerBI Select Record

Step 6 - View Table

Once loaded, you can see the dataset in your fields.

PowerBI loaded

Using Postman

This example will demonstrate how to generate a Basic Token using the request Basic Authentication with Platform Credentials and Postman.

Step 1 - Import Collection

For Chrome or Mac users, click the button below to import the collection directly into Postman. Alternatively, you can download the collection.

Step 2 - Open Collection

Click on Data Connect Guide, a collection in the sidebar.

If you do not see the collection, ensure the “Collection” tab is selected and not “History”.

Postman Collection

Step 3 - Open Request

The collection will open showing a list of requests. Click on Basic Authentication to load the request

Request in Postman

Step 4 - Change Environment Values

Click on the environment selector (drop down box in top right corner) and select “Manage Environments”

Environment Picker

Click on “postmanEnv” in the list to bring up an edit screen

Environment Editor

Modify the following values:

Click Update to update the postman environment and then click X to close the editor

Step 5 - Perform the Request

Click on the Send button to perform the request.

The Basic Token is listed in the response as basic_token

Postman Response

Was this page helpful?