Json Data Provider - majorsilence/Reporting GitHub Wiki

New Report from Database wizard, on the Connection tab select the Json data provider.

Example Connection Strings

file=TestData.json

url=TestData.json;auth=Basic: PLACEHOLDER

url=https://raw.githubusercontent.com/majorsilence/My-FyiReporting/refs/heads/master/RdlCreator.Tests/TestData.json;auth=basic: Placeholder

Example Command Text

columns=EmployeID,LastName,FirstName,Title

Example DataSource and CommandText

<?xml version="1.0" encoding="UTF-8"?>
<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
 <DataSources>
    <DataSource Name="DS1">
      <ConnectionProperties>
        <DataProvider>Json</DataProvider>
        <ConnectString>file=C:\Users\peter\source\repos\My-FyiReporting\RdlCreator.Tests\NestedJsonData.json</ConnectString>
      </ConnectionProperties>
    </DataSource>
  </DataSources>

 <DataSets>
    <DataSet Name="Data">
      <Query>
        <DataSourceName>DS1</DataSourceName>
        <CommandText>columns=EmployeeID,FirstName,LastName,ContactInfo_Phone,ContactInfo_Email</CommandText>
      </Query>
    <DataSet>
 </DataSets>
</Report>

Nested Object

When working with nested objects in your JSON data, you can access nested fields using an underscore (_) to separate each level of the hierarchy. For example, if your JSON contains a ContactInfo object with Phone and Email fields, you can reference them in the columns list as ContactInfo_Phone and ContactInfo_Email.

Example:

Given the following JSON structure:

{
  "EmployeeID": 1,
  "FirstName": "John",
  "LastName": "Doe",
  "ContactInfo": {
    "Phone": "123-456-7890",
    "Email": "[email protected]"
  }
}

You would use the following command text to select nested fields:

columns=EmployeeID,FirstName,LastName,ContactInfo_Phone,ContactInfo_Email

This approach is shown in the XML example above, where ContactInfo_Phone and ContactInfo_Email are used to access the nested properties.

Nested Object Arrays

Work in Progress

References

⚠️ **GitHub.com Fallback** ⚠️