Nested Layout Data (Subreports)

For nested data, you use subreports to create a report with multiple data groupings. You can use JSON, data objects, SQL reports and events to apply nested data to a DLEX template.

The DLEX in Figure 1 illustrates the data elements used in the examples below.

JSON

The following example illustrates using JSON as the provided layout data.

• Reads JSON data from a file (subreport.json).

{
  "ProductsByCategory": [
    {
      "Name": "Beverages",
      "Products": [
        {
          "ProductID": 1,
          "ProductName": "Chai",
          "QuantityPerUnit": "10 boxes x 20 bags",
          "Discontinued": false,
          "UnitPrice": 18
        },
        {
          "ProductID": 2,
          "ProductName": "Chang",
          "QuantityPerUnit": "24 - 12 oz bottles",
          "Discontinued": false,
          "UnitPrice": 19
        }
      ]
    }
}

• Deserializes the JSON into a .NET object.
• Uses a DLEX template to generate a PDF report.

string data = File.ReadAllText(Util.GetPath("subreport.json");
var jsonData = JsonConvert.DeserializeObject(data);
DocumentLayout layoutReport = new DocumentLayout(Util.GetPath("subreport.dlex");
LayoutData layoutData = new LayoutData(jsonData);
Document document = layoutReport.Layout(layoutData);
document.Draw(outputPath);

Dim data As String = File.ReadAllText("subreport.json")
Dim jsonData As Object = JsonConvert.DeserializeObject(data)
Dim layoutReport As New DocumentLayout("subreport.dlex")
Dim layoutData As New LayoutData(jsonData)
Dim document As Document = layoutReport.Layout(layoutData)
document.Draw(outputPath)

Data Objects

The following example generates a document using a predefined layout template (subreport.dlex). It retrieves product category data, associates it with the layout, and creates a document.

• NameValueLayoutData is used to store data that will be injected into the document.
• A list of ProductCategory objects is retrieved from ProductCategoryData.GetProductCategoryObjects().
• This data is then added to layoutData under the key "ProductsByCategory".

DocumentLayout layoutReport = new DocumentLayout("subreport.dlex");
NameValueLayoutData layoutData = new NameValueLayoutData();
List<ProductCategory> productCategoryData = ProductCategoryData.GetProductCategoryObjects();
layoutData.Add("ProductsByCategory", productCategoryData);
Document document = layoutReport.Layout(layoutData);
document.Draw(outputPath);

Dim layoutReport As New DocumentLayout("subreport.dlex")
Dim layoutData As New NameValueLayoutData()
Dim productCategoryData As List(Of ProductCategory) = GetProductCategoryObjects()
layoutData.Add("ProductsByCategory", productCategoryData)
Dim document As Document = layoutReport.Layout(layoutData)
document.Draw(outputPath)

Database Using JSON

This example retrieves data from a SQL database in JSON format, processes it, and generates a document using a predefined layout template (subreport.dlex).

• This query fetches product and category data from the Categories and Products tables.
• The FOR JSON AUTO clause converts the result into JSON format.
• The root('ProductsByCategory') option wraps the output in a root object named "ProductsByCategory".
• Executes the query with SqlCommand and reads the database output.
• Create JSON from database results.
• The JSON string is then deserialized into an object using JsonConvert.DeserializeObject().
• Loads the document layout template (subreport.dlex).
• Uses the deserialized JSON data as LayoutData.
• Generates a document using the layout and saves it to outputPath.

string queryWithForJson = "SELECT CategoryName as Name, ProductID,  ProductName, " + 
    "QuantityPerUnit, Discontinued, UnitPrice " +
    "FROM Categories, Products FOR JSON auto," +
    "root('ProductsByCategory')";
using (var conn = new SqlConnection(CONNECTION_STRING))
{
    using (var cmd = new SqlCommand(queryWithForJson, conn)
    {
        conn.Open();
        var jsonResult = new StringBuilder();
        var reader = cmd.ExecuteReader();
        if (!reader.HasRows) {
            jsonResult.Append("[]");
        } else {
            while (reader.Read()) {
                jsonResult.Append(
                    reader.GetValue(0).ToString());
            }
        }
        var jsonData = JsonConvert.DeserializeObject(
            jsonResult.ToString());
		DocumentLayout layoutReport = new DocumentLayout("subreport.dlex");
        LayoutData layoutData = new LayoutData(jsonData);
        Document document = layoutReport.Layout(layoutData);
        document.Draw(outputPath);
    }
}

Dim queryWithForJson As String = "SELECT CategoryName as Name, ProductID, ProductName, " &
"QuantityPerUnit, Discontinued, UnitPrice " &
"FROM Categories, Products FOR JSON auto, root('ProductsByCategory')"
Using conn As New SqlConnection(CONNECTION_STRING)
	Using cmd As New SqlCommand(queryWithForJson, conn)
	  conn.Open()
	  Dim jsonResult As New StringBuilder()
	  Dim reader As SqlDataReader = cmd.ExecuteReader()
	  If Not reader.HasRows Then
	  	jsonResult.Append("[]")
	  Else
	  	While reader.Read()
	  		jsonResult.Append(reader.GetValue(0).ToString())
	  	End While
	  End If
	  Dim jsonData As Object = JsonConvert.DeserializeObject(jsonResult.ToString())
	  Dim layoutReport As New DocumentLayout("subreport.dlex")
	  Dim layoutData As New LayoutData(jsonData)
	  Dim document As Document = layoutReport.Layout(layoutData) document.Draw(outputPath)
	End Using
End Using

Database Using ReportDataRequired Event

The ReportDataRequired event handler triggers an event and executes a callback function every time a report or subreport element is parsed in a DLEX file.

The following example generates a subreport using SQL data retrieval and event handling. It integrates with a DynamicPDF layout (subreport.dlex), fetching data from a SQL database to populate the report before saving the final document. It attaches an event handler (DocumentLayout_ReportDataRequired) to handle data retrieval for specific report elements.

• DocumentLayout_ReportDataRequired:
  • This event handler is invoked when data for a specific report element (ProductsByCategoryReport or ProductsByCategorySubReport) is needed.
  • If the ElementId is ProductsByCategoryReport, it executes an SQL query to retrieve categories from the Categories table and passes the result to the report.
  • If the ElementId is ProductsByCategorySubReport, it dynamically generates an SQL query to retrieve products based on the CategoryID passed from the parent report. It then passes the result to the subreport.

private static void GenerateSubReportUsingSqlEvent()
{
    DocumentLayout documentLayout = new DocumentLayout("subreport.dlex");
    documentLayout.ReportDataRequired += DocumentLayout_ReportDataRequired;
    LayoutData layoutData = new LayoutData();
    Document document = documentLayout.Layout(layoutData);
    document.Draw(outputPath);
}

private static void DocumentLayout_ReportDataRequired(object sender, ReportDataRequiredEventArgs args)
{
    if (args.ElementId == "ProductsByCategoryReport") {
        string sqlString =
                    "SELECT CategoryID, CategoryName Name "
            + "FROM   Categories ";
        SqlConnection connection = new SqlConnection(CONNECTION_STRING);
        SqlCommand command = new SqlCommand(sqlString, connection);
        connection.Open();
        SqlDataReader reader = command.ExecuteReader();
        args.ReportData = new DataReaderReportData(connection, reader);
    } else if (args.ElementId == "ProductsByCategorySubReport") {
        string sqlString = "SELECT ProductID, ProductName,"
            + "QuantityPerUnit, UnitPrice, Discontinued " +
            "FROM Products WHERE CategoryID = " + 
            args.Data["CategoryID"] + 
            " ORDER BY ProductName";
        SqlConnection connection = new SqlConnection(CONNECTION_STRING);
        SqlCommand command = new SqlCommand(sqlString, connection);
        connection.Open();
        SqlDataReader reader = command.ExecuteReader();
        args.ReportData = new DataReaderReportData(connection, reader);
}

Public Shared Sub GenerateSubReportUsingSqlEvent()
            Dim documentLayout As New DocumentLayout(Util.GetPath("Resources/DLEXs/subreport.dlex"))
            AddHandler documentLayout.ReportDataRequired, AddressOf DocumentLayout_ReportDataRequired
            Dim layoutData As New LayoutData()
            Dim document As Document = documentLayout.Layout(layoutData)
            document.Draw(Util.GetPath("Output/subreport_db_sql_output.pdf"))
End Sub

Private Shared Sub DocumentLayout_ReportDataRequired(sender As Object, args As ReportDataRequiredEventArgs)
            If args.ElementId = "ProductsByCategoryReport" Then
                Dim sqlString As String =
                "SELECT CategoryID, CategoryName Name " &
                "FROM   Categories "

                Dim connection As New SqlConnection(CONNECTION_STRING)
                Dim command As New SqlCommand(sqlString, connection)
                connection.Open()
                Dim reader As SqlDataReader = command.ExecuteReader()
                args.ReportData = New DataReaderReportData(connection, reader)

            ElseIf args.ElementId = "ProductsByCategorySubReport" Then
                Dim sqlString As String =
                "SELECT ProductID, ProductName, QuantityPerUnit, UnitPrice, Discontinued " &
                "FROM   Products " &
                "WHERE  CategoryID = " & args.Data("CategoryID") & " " &
                "ORDER BY ProductName "

                Dim connection As New SqlConnection(CONNECTION_STRING)
                Dim command As New SqlCommand(sqlString, connection)
                connection.Open()
                Dim reader As SqlDataReader = command.ExecuteReader()
                args.ReportData = New DataReaderReportData(connection, reader)
            End If
End Sub