Generate JSON Correctly (SQL Server Example)
When using Designer, you might have trouble getting started with producing JSON data to design and test your DLEX document. If you use SQL Server, then an easy way to produce a JSON dataset is by using the for JSON clause in your SQL statement.
In this short tip, we demonstrate using SQL Server to easily create a JSON document for testing in DynamicPDF Designer Online.
Although the example here is specific to SQL Server, most database platforms can output results as JSON.
When creating a report in Designer, be sure you carefully consider the JSON data structure before endeavoring to create your DLEX report. It will save you from wasting countless hours restructuring your report to match the needed JSON data.
Many tools are available to you depending on your database platform, your data requirements, and other tools in your organization. However, let's use SQL Server to create our JSON.
Subreport example
The subreport named subreport in the Samples folder in DynamicPDF File Manager has the following DLEX and JSON.
Let's delete the JSON file and generate a new JSON file.
SQL Query
In SQL Server Object Explorer in Visual Studio, we create a new SQL statement.
The SQL statement uses the for json auto clause. We also specify the root as ProductsByCategory.
select CategoryName Name, ProductID, ProductName, QuantityPerUnit, Discontinued, UnitPrice from Products, Categories as ProductsByCategory
where Products.CategoryID = ProductsByCategory.CategoryID
order by CategoryName
for json auto, root('ProductsByCategory')
We rename CategoryName as Name and Categories as ProductsByCategory to keep the JSON data names consistent with the pre-existing DLEX file.
After executing the query we obtain the following JSON document.
Save the JSON as subreport.json, upload the file to cloud storage using File Manager, and open the DLEX file in Designer.
Although the JSON is not formatted to indent properly, it still produces the expected PDF upon running the DLEX.
When creating a report in Designer, be sure you carefully consider the JSON data structure before endeavoring to create your DLEX report. It will save you from wasting countless hours restructuring your report to match the needed JSON data.
If you use SQL Server, you will find the tip presented here useful for generating JSON data using the for json clause in a SQL Server Select statement.
