Reporting Schema

The Struktural_Sys_Report table stores user and system-generated reports. The behavior, layout, columns, and charts of the report are defined strictly within the Definition property, which holds a serialized JSON string representing the ReportDefinition object.

ReportDefinition JSON Structure

{
  "RootEntity": "Order",          
  "IsCustomQuery": false,         
  
  "Header": {
    "Title": "Monthly Sales",
    "SubTitle": "Q1 2024",
    "ShowDate": true,
    "Alignment": "left",          
    "LogoImageId": null,          
    "ImagePosition": "Behind"    
  },
  
  "Page": {
    "Size": "A4",                 
    "Orientation": "Portrait"     
  },
  
  "Filter": {                     
    "Field": "Date", 
    "Operator": "after", 
    "Value": "today - 30"
  },

  "Columns": [
    { 
      "Field": "TotalAmount",     
      "Label": "Total ($)",       
      "Width": 100,               
      "Aggregate": "Sum"          
    }
  ],
  
  "Grouping": {                   
    "Field": "Customer.Name",     
    "ShowTotalInHeader": true,
    "ShowTotalInFooter": true
  },
  
  "Sorting": {                    
    "Field": "Date",
    "Direction": "desc"           
  },
  
  "Footer": {
    "Text": "Confidential",
    "ShowPageNumbers": true
  },
  
  "ShowGrandTotal": true,         
  "ShowDataGrid": true,           
  
  "Charts": [                     
    {
      "Id": "chart_1",
      "Title": "Sales by Region",
      "Type": "Bar",              
      "Width": 6,                 
      "CategoryField": "Region",  
      "ValueField": "Total",      
      "Aggregate": "Sum"          
    }
  ],
  "Query": null
}

CustomQueryDefinition Schema

When IsCustomQuery is true, standard Entity Framework navigation properties are bypassed. The system instead compiles a flat projection based on manual SQL Joins defined in the Query property. This is vital for complex nested reports (e.g., retrieving OrderLine, joined to Product, joined to Supplier).

"Query": {
  "BaseEntity": "OrderLine",
  "Joins": [
    {
      "LeftEntity": "OrderLine", 
      "LeftField": "ProductId", 
      "RightEntity": "Product", 
      "RightField": "Id", 
      "JoinType": "Inner"
    },
    {
      "LeftEntity": "Product", 
      "LeftField": "SupplierId", 
      "RightEntity": "Supplier", 
      "RightField": "Id", 
      "JoinType": "Left"
    }
  ],
  "SelectedFields": [
    { "EntityName": "Supplier", "FieldName": "Name", "Alias": "SupplierName" },
    { "EntityName": "Product", "FieldName": "Code", "Alias": "ProductCode" },
    { "EntityName": "OrderLine", "FieldName": "Quantity", "Alias": "Qty" }
  ]
}