Using Report Parameters To Generate Custom Reports

Report parameters can be used when generating custom reports. There are also a few unique parameters available for custom file export reports.

When creating a file export report using :DocumentKey, you can capture the relevant document details. The document key can be used for the following:

Document type

Document key

Receipting

ReceiptID

Picking/Packing

JobID

Manifests

ManifestID

Example uses for report parameters:

Report parameter

Description

FieldName

This is the field that merges into the query.

FieldTitle

This is the field name visible to the end user.

FieldType

The matches the expected data being entered.

For example, "FieldType":"Date" displays a date entry field to the end user.

Default

Default to use if nothing is entered.

For example, "Default":"TODAY".

The entire parameter is wrapped in a square bracket.

Each individual field has its own entry separated by a comma.

Use the following when a customer wishes to have the ability to generate a report for specific order number prefixes and suffixes.

The following code block allows for up to 3 order number searches on the one report. This case clause will also allow the user to enter the search criteria into any of the Order Number fields.

Order number where clause:

Copy 
AND (CASE
    WHEN LENGTH(:OrderPrefix1) > 0 AND LENGTH(:OrderPrefix2) = 0 AND LENGTH(:OrderPrefix3) = 0 THEN PackSlipNumber 
    LIKE :OrderPrefix1
    WHEN LENGTH(:OrderPrefix1) = 0 AND LENGTH(:OrderPrefix2) > 0 AND LENGTH(:OrderPrefix3) = 0 THEN PackSlipNumber 
    LIKE :OrderPrefix2
    WHEN LENGTH(:OrderPrefix1) = 0 AND LENGTH(:OrderPrefix2) = 0 AND LENGTH(:OrderPrefix3) > 0 THEN PackSlipNumber 
    LIKE :OrderPrefix3
    WHEN LENGTH(:OrderPrefix1) > 0 AND LENGTH(:OrderPrefix2) > 0 AND LENGTH(:OrderPrefix3) = 0 THEN PackSlipNumber 
    LIKE :OrderPrefix1 OR PackSlipNumber LIKE :OrderPrefix2
    WHEN LENGTH(:OrderPrefix1) = 0 AND LENGTH(:OrderPrefix2) > 0 AND LENGTH(:OrderPrefix3) > 0 THEN PackSlipNumber 
    LIKE :OrderPrefix2 OR PackSlipNumber LIKE :OrderPrefix3
    WHEN LENGTH(:OrderPrefix1) > 0 AND LENGTH(:OrderPrefix2) = 0 AND LENGTH(:OrderPrefix3) > 0 THEN PackSlipNumber 
    LIKE :OrderPrefix1 OR PackSlipNumber LIKE :OrderPrefix3
    WHEN LENGTH(:OrderPrefix1) > 0 AND LENGTH(:OrderPrefix2) > 0 AND LENGTH(:OrderPrefix3) > 0 THEN PackSlipNumber 
    LIKE :OrderPrefix1 OR PackSlipNumber LIKE :OrderPrefix2 OR PackSlipNumber LIKE :OrderPrefix3
    ELSE PackSlipNumber IS NOT NULL END)

The report parameters for the above:

Copy 
[
{"FieldName":"OrderPrefix1", "FieldTitle":"Order Prefix 1 (XXX% = Starts with, %XXX = Ends with)", "FieldType":"Text"},
{"FieldName":"OrderPrefix2", "FieldTitle":"Order Prefix 2 (XXX% = Starts with, %XXX = Ends with)", "FieldType":"Text"},
{"FieldName":"OrderPrefix3", "FieldTitle":"Order Prefix 3 (XXX% = Starts with, %XXX = Ends with)", "FieldType":"Text"}
]

When added to a report, the following fields display when the customer tries to run the report:

A customer running a custom report for order number searches.

By using the LIKE function of MySQL, the customer will be able to take advantage of the following wild cards:

LIKE operator

Description

WHERE CustomerName LIKE 'a%'

Finds any values that start with "a".

WHERE CustomerName LIKE '%a'

Finds any values that end with "a".

WHERE CustomerName LIKE '%or%'

Finds any values that have "or" in any position.

WHERE CustomerName LIKE '_r%'

Finds any values that have "r" in the second position.

WHERE CustomerName LIKE 'a_%_%'

Finds any values that start with "a" and are at least 3 characters in length.

WHERE ContactName LIKE 'a%o'

Finds any values that start with "a" and end with "o".

Due to how MySQL behaves when searching for date ranges, you will need to use the following.

By concatenating in a start and end time, you are able to search for an entire day.

Copy 
AND OrderDate BETWEEN CONCAT(:StartDate, ' 00:00:01') AND CONCAT(:EndDate, ' 23:59:59')

The following fields display for the customer:

Date range searches.

The following report parameters can be used to trigger the above data fields:

Copy 
[
{"FieldName":"StartDate", "FieldTitle":"Start Date", "FieldType":"Date", "Default":"TODAY"},
{"FieldName":"EndDate", "FieldTitle":"End Date", "FieldType":"Date"}
]

When using a warehouse or tenant filters a drop-down menu is generated for the user to select an option.

Warehouse and tenant drop-down filters.

Triggering these drop-downs is simple to do.

  1. Add :TenantID and/or :WarehouseID to the where clause of your query.

    Copy 
    AND WarehouseID = :WarehouseID
    AND TenantID = :TenantID

  2. Using the following report parameter produces a drop-down for the Warehouse and/or Tenant fields.

    Copy 
    [
    {"FieldName":"WarehouseID", "FieldTitle":"Warehouse", "FieldType":"WarehouseID"},
    {"FieldName":"TenantID", "FieldTitle":"Tenant", "FieldType":"TenantID"}
    ]

Report parameters can also be used to search for a specific item.

For example, Popink requested a custom report which allows them to report on the number of units ordered compared to the units shipped. The default report gives them ALL products between a selected date range. By adding a field for ItemCode, Popink can search for a specific product for a selected date range.

Including the Item Code field to refine searches.

Results of the item search.

To add an Item Code search, include the following to your query:

Copy 
AND (CASE
    WHEN LENGTH(:ItemCode) > 0 THEN ITM.ItemCode = :ItemCode ELSE ITM.ItemCode IS NOT NULL END)

By using a where clause you allow the end user to have the option of leaving the field empty when generating the report.

Copy 
[
{"FieldName":"ItemCode", "FieldTitle":"Item Code", "FieldType":"Text"}
]

Kitchen Warehouse requested a range of KPI reports which enable them to track staff and warehouse performance.

When generating the report they are able to select a date range and view each staff member's activity during that time.

They are also able to track a single staff member's performance.

As most tables store the UserID instead of the user's FullName, you are able to use the query below to return the desired UserID based on their full name.

Copy 
(CASE WHEN LENGTH(:User) > 0 THEN DATA.UserID = (SELECT UserID FROM USR Where Fullname = :User AND InstanceID = 1000)

The following report parameter can be used:

Copy 
[
{"FieldName":"User", "FieldTitle":"User Full Name", "FieldType":"Text"}
]

In the example, the user's full name is used, but you can easily adjust this to use PackerID.