How Do I Connect to a JSON Data Source?

In this article we demonstrate how to connect a Report Template to a JSON data source. For more details about the Connection Editor, see the Connection Editor Reference.

What is Needed to Establish the Connection

  • An JSON Data Source (file or URL)
  • A good network connection to your file or URL.

Please note, if you are inside a corporate network and you cannot access our Windward cloud-hosted demo data source, your firewall may be blocking you from access. Please consult your system or network administrator for assistance.

Connect Your Report Template to JSON

From Microsoft Word, Excel, or PowerPoint, navigate to the AutoTag Manager tab. Once on the AutoTag Manager tab, click on the upper half of the Data Sources button. This will bring up the Connection Editor.

  1. In the Connection Editor window click on the New tab.
  2. Click on JSON in the Web/File section. The Details pane will change to prompt you for your data source details.
  1. Enter a Name for your data source connection.
  2. Enter the full pathname, or a URL, to your JSON file.
  3. (Optional) Choose your Encoding (UTF-8, UTF-16, or US-ASCII). Default is ASCII, and in addition to the three encoding schemes in the dropdown menu, you can type in any encoding scheme. 
  4. (Optional) Choose your Authentication Protocol. The Authentication Protocol you choose will set the contents of the contextual Properties window below. For additional information, see our article Data Access Providers and Authentication Protocols.
  5. (Optional) Enter the Authentication Protocol Properties, based on the choice of Authentication Protocol above.
  6. Click Add, and then Test your connection. If all parameters and credentials are correct and a connection is made, you will receive a successful notification. 
  7. Click on Close to save your information. 

Other (Optional) Connection Editor Fields

Root Directory: This is the "default directory" for any Import Tag requests where the requested file does not have an absolute path. If left blank, Report Designer will use the directory where the data source file is located. If the data source is not a file it will use the directory where the template is located.

Notes

Windward Sample Template Credentials

To use Windward's sample templates, enter these credentials. 

  • File/URL: http://json.windward.net/Northwind.json 
  • Credentials: ‘Use Windows Identity’ (no user credentials are needed for the Windward sample)

Connection String

When you set all the properties for JSON you will get a connection string in the form

“Url= C:\test\FileName.json/;AccessProvider=FileSystem;Encoding=utf-8;”

Use the connection string in your code as shown below.

.NET Code Java Code
new JsonDataSourceImpl (“Url= C:\test\filename.json/;AccessProvider=FileSystem;Encoding=utf-8;” );
new JsonDataSource (“Url= C:\test\filename.json/;AccessProvider=FileSystem;Encoding=utf-8;” );

orderby() Function

The orderby() function may be added to the end of any JsonPath expression. Doing so will return a sorted array.

For instance, using the Northwind database to order an array of Employees by their EmployeeID, you would use the query: 

$.Employees[*].orderby(EmployeeID, 1).

The parameter '1' indicates the Employees are ordered in ascending order. A parameter of '-1' would indicate descending order.

If an object does not have the property specified by the orderby function (as in the EmployeeID above), then the objects are placed in arbitrary order at the end of the list.

Multiple calls to orderby() in a single query are not yet supported.

limit() Function

The limit() function simply limits the number of records returned. Note that the calling order of limit() and orderby() is significant, so make sure you call the functions in the desired order.

ForEach Tags with Arrays of Literals

If your JSON file has a node of the form '{ array: ["first", "second"] }', a ForEach Tag can be used to iterate through the items of 'array'.

To do this, create a ForEach Tag with the select value of “$.array[*]”, and a variable with the name value of "varName1." Also, create an Out Tag with the select value of "${varName1}" to place between your ForEach Tag and your EndForEach Tag.

Handling Images

Images that are embedded in JSON objects (in your file) can be either integer arrays (with values less than 256-bits), or base-64 encoded strings. In either case, use the Out Tag to view an image. The Out Tag's type property must be set to BITMAP.

0 Comments

Add your comment

E-Mail me when someone replies to this comment