There are 4 options available to customers for data reporting/exporting in X Dispatch. Options will vary depending upon installation type, Cloud or On-Premise.
Basic / Advanced Find query and export
The first option to access the SQL database from X Dispatch for reporting purposes will be to use the Basic find. This option is best suited for times when a basic returned dataset is required. In the image below, you can see an example of the Basic find and the export. This option is best suited for times when a basic returned dataset is required. In the image below, you can see an example of the Basic find and the export.
A Basic find would be used most often when you need a small data sample to verify information. An example is when a customer contacts you asking for a day's worth of data for them.
(For this example we will find orders, where the customer id = 9999 and date ordered, is in a date range)
Run your query
Click Export
Save the file (Cloud Users please see How To Save Files To Your Computer From Cloud for instructions on saving files locally)
The second part of this option is to use an Advanced find. The Advanced find will allow you to write your own query to return the data you need. You export the same way you do a Basic Find. The advanced find query editor lists each table and the columns for each table in the boxes at the bottom. This will assist when writing queries for the advanced find.
The Advanced find query editor lists each table and the columns for each table in the windows at the bottom. This will assist when writing queries for the advanced find.
An Advanced find is often used when you need to retrieve specific information or have advanced functions performed on the data. The most common examples are for reports you run once a week or specific time intervals to pull larger datasets to provide customers on-going information.
Here is an example of an Advanced find to retrieve the last 10 orders placed on your system using the following SQL syntax:
You can also use the Subscription service in the Find that will generate an email, on a schedule, with the results. (Please see Find > Query Definition Subscriptions for setup instructions.)
The Subscribe method is used to automatically email data to your customers or yourself, as a form of automated reporting.
X Stream definition Export to FTP
By deploying an X Stream definition, you have the ability to automatically run an Advanced find on a schedule and use FTP to transfer the data to yourself or to a selected endpoint.
The export can be configured to run on a schedule and allows you the ability to write a query to return data in the format you require. You can then run live tests in the query editor under the Advanced query builder (As shown above).
Once you have validated that the data is returned in the expected format, you then save the query and update the X Stream definition with the name of the query you want it to export the results for.
Deployment of this method can be implemented by support quickly, full instructions here to set up additional definitions for multiple exports. Please open a support ticket to inquire about this method.
Below is an example of this X Stream Definition
An additional option that can be used with X Stream is to have Technical Support or Professional services create a new stored procedure and deploy it for use. This is custom work and subject to billable charges.
Web Service
We offer Web services that can be used to push and ingest data. Web services require developer skills. CXT Software offers developer consultation for assistance understanding and consuming our public API's.
We offer our standard Web API: XMLListener. This option works well for on-demand and some routed work. This is available to all customers running X Internet. More information can be found here and here.
We also offer a Web Service called the Ingest Service. This service requires a recurring monthly cost, adds a larger list of methods that better handle routed work. (Please open a ticket with Support for more information)
Finally with the release of X Dispatch 17.1 a new web service has been added that provides replacement functionality for both the prior web services previously mentioned. For documentation please see Web Services/API Information (v2). The new web services power Nextstop 2, and Rapidship, these services are continually being developed and expanding over time.
Remote SQL access
This option is only for Cloud customers who are on the dedicated edition. Dedicated is intended for large companies with thousands of drivers, workstations, integrations and massive data sets. Dedicated is an upgraded edition in Cloud, and can be purchased by contacting your sales representative for details and pricing. Once a client has upgraded to dedicated, Remote SQL Access can be purchased for an additional monthly fee.
Please open a support ticket for more information.