Sample KServer Reports

Here are a few examples from Keystone showing some KServer reports. These examples show the complete endpoint syntax. The examples range from simple to complex.

A/R Salesperson List

This example is a basic report with the following features:

• Query on a single table
• Report supports active/inactive selection
• Report selection on one field
• Report supports two sequences
• Fields and labels specified

{
  "URI": "/ARSalespersonList",
  "query": [
    {
      "name": "ARSalespersonList",
      "table": "artslspn",
      "sql": "SELECT SP.* FROM ARTSLSPN SP",
      "params": [
        { "name": "CLASS_NO", "field": "CLASS_NO", "filter": "range" },
        { "name": "ACTIVE_FLAG", "field": "ACTIVE_FLAG" }
      ],
      "record": {
        "fields": []
      }
    }
  ],
  "report": {
    "title": "A/R Salesperson List",
    "activefieldcount": 1,
    "selection": [
      { "title": "Salesperson Id", "field": "CLASS_NO", "type": "lookup", "lookup": { "type": "ARSalesperson" } }
    ],
    "sequence": [
      { "title": "Salesperson Id", "field": "CLASS_NO" },
      { "title": "Name", "field": "DESCRIPTION" }
    ],
    "fields": [
      { "field": "CLASS_NO", "label": "Sls Id" },
      { "field": "DESCRIPTION", "label": "Name" },
      { "field": "JOB_TITLE", "label": "Job Title" },
      { "field": "PHONE_NO", "label": "Phone" },
      { "field": "CELL_NO", "label": "Mobile" },
      { "field": "FAX_NO", "label": "Fax" },
      { "field": "EMAIL", "label": "Email" }
    ]
  }
}

A/R Product Class List

This example is a slightly more complex report with the following features:

• Query on a single table
• Report supports active/inactive selection
• Report selection on two fields
• Report supports two sequences
• Report has two options, one hidden (setparam) and one visible (list)
  • The setparam option is used to read a Keystone system option (in this case to determine if G/L is active)
  • The list option is used to select between Summary/Detail report types
• Two report fields have calculated values
  • The translate function converts a code value into a descriptive value
• Some fields only print when a condition is satisfied
  • Several fields print only for the Detail report type
  • The two G/L account fields print only for the Detail report type and only if G/L is active

{
  "URI": "/ARProductClassList",
  "query": [
    {
      "name": "ARProductClassList",
      "table": "artprcls",
      "sql": "SELECT PC.* FROM ARTPRCLS PC",
      "params": [
        { "name": "CLASS_NO", "field": "CLASS_NO", "filter": "list" },
        { "name": "PLANT_NO", "field": "PLANT_NO", "filter": "range" },
        { "name": "ACTIVE_FLAG", "field": "ACTIVE_FLAG" },
        { "name": "REPORT_TYPE", "proc": true },
        { "name": "OPT_GL_ACTIVE", "proc": true }
      ],
      "record": {
        "fields": []
      }
    }
  ],
  "report": {
    "title": "A/R Product Class List",
    "activefieldcount": 1,
    "selection": [
      { "title": "Product Class", "field": "CLASS_NO", "type": "lookupchecklist", "lookup": { "type": "ARProductClass" } },
      { "title": "Plant Id", "field": "PLANT_NO", "type": "lookup", "lookup": { "type": "ARPlant" } }
    ],
    "sequence": [
      { "title": "Product Class", "field": "CLASS_NO" },
      { "title": "Description", "field": "DESCRIPTION" }
    ],
    "options": [
      { "title": "Opt G/L Active", "type": "setparam", "param": "OPT_GL_ACTIVE", 
        "setparam": {
          "calc": "ne(sysopt('SYSTEM','GLActive','0'), '0')"
        }
      },
      { "title": "Report Type", "type": "list", "param": "REPORT_TYPE",
        "list": {
          "items": [
            { "title": "Summary", "value": "1" },
            { "title": "Detail", "value": "2" }
          ]
        }
      }
    ],
    "fields": [
      { "field": "CLASS_NO", "label": "Class" },
      { "field": "DESCRIPTION", "label": "Description" },
      { "field": "PLANT_NO", "label": "Plant" },
      { "field": "SUBTOTAL", "label": "Subtot?" },
      { "field": "STD_AUTO_QTY", "label": "Auto Qty", "calc": "translate([STD_AUTO_QTY], 'ARProdAutoQty')",
        "detail": {
          "condition": "in([REPORT_TYPE], '2')"
        }
      },
      { "field": "STD_DEFAULT_QTY", "label": "Def Qty",
        "detail": {
          "condition": "in([REPORT_TYPE], '2')"
        }
      },
      { "field": "STD_PRODUCT_TYPE", "label": "Prod Type", "calc": "translate([STD_PRODUCT_TYPE], 'ARProdType')",
        "detail": {
          "condition": "in([REPORT_TYPE], '2')"
        }
      },
      { "field": "STD_STOCKED", "label": "Stock?",
        "detail": {
          "condition": "in([REPORT_TYPE], '2')"
        }
      },
      { "field": "STD_UNIT_OF_MEASURE", "label": "U\/M",
        "detail": {
          "condition": "in([REPORT_TYPE], '2')"
        }
      },
      { "field": "STD_TAX_CODE", "label": "Tx",
        "detail": {
          "condition": "in([REPORT_TYPE], '2')"
        }
      },
      { "field": "WEIGHT_FLAG", "label": "Weight?",
        "detail": {
          "condition": "in([REPORT_TYPE], '2')"
        }
      },
      { "field": "STD_SALES_GL", "label": "Sales Account",
        "detail": {
          "condition": "and([OPT_GL_ACTIVE], in([REPORT_TYPE], '2'))"
        }
      },
      { "field": "STD_COSTOFSALES_GL", "label": "COGS Account",
        "detail": {
          "condition": "and([OPT_GL_ACTIVE], in([REPORT_TYPE], '2'))"
        }
      },
      { "field": "NON_TAX_AMOUNT", "label": "NonTax Amt", "format": "#.00"
      }
    ]
  }
}