Skip to content

Drilldown criteria

The drilldown narrows the MRP Details view to rows that are causally connected to the shortage on the source row. The criteria are applied as filters on PrimaryLayoutInterface.Criteria so they integrate cleanly with any additional filters the user adds from within the drilldown.

The reference WHERE clause

The criteria implement the WHERE clause from the project's drilldown reference document, with one addition (the week range filter):

WHERE item_no = @@item_no
   OR EXISTS (
       SELECT 1 FROM #FlatBOM b
       WHERE b.comp_item_no = @@item_no
         AND b.fg_item_no = mrpd.item_no
   )
   OR EXISTS (
       SELECT 1 FROM #FlatBOM b
       WHERE b.comp_item_no = @@item_no
         AND b.par_item_no = mrpd.item_no
   )
   AND mrp_trx_dt BETWEEN <week_start> AND <week_end>

In the SQL above, @@item_no is the item from the clicked row.

How the WHERE clause is implemented

The dynamic-query criteria builder does not support cross-table EXISTS clauses. Instead, the related items are resolved up-front and the resulting list is passed as an IsIn criterion. This is functionally equivalent to the EXISTS form but works through AddCriteriaByFieldName.

When the drilldown opens, a single SQL query gathers every item_no that the drilled item is related to via the bill of materials:

Dim szRelatedQuery As String =
    "select distinct fg_item_no as related_item_no from #FlatBOM where comp_item_no = '" & szItemEsc & "' " &
    "union " &
    "select distinct par_item_no as related_item_no from #FlatBOM where comp_item_no = '" & szItemEsc & "'"
AppCon.FillDataTable(szRelatedQuery, dtRelated)

The drilled item itself is also added to the list. Single quotes in the item number are escaped before the query is built.

Failure mode

The #FlatBOM lookup is wrapped in a Try / Catch. If the temp table is unavailable for any reason, the criteria fall back to filtering by the drilled item alone. This keeps the drilldown usable even in environments where the BOM temp table is not present.

Step 2: apply IsIn for item_no

PrimaryLayoutInterface.Criteria.AddCriteriaByFieldName(
    "item_no",
    DynamicQuery.CCriteria.CSD_1.enumLogicalOperator.IsIn,
    relatedItems.ToArray())

`IsIn` accepts a `String()` array.

Step 3: apply EqualTo for item_loc (when applicable)

The reference document specifies that `item_loc` should only be filtered if the source report had it visible:

If m_GridView.Columns("item_loc") IsNot Nothing AndAlso m_GridView.Columns("item_loc").Visible Then
    szLoc = m_GridView.GetRowCellValue(e.RowIndex, "item_loc")
    PrimaryLayoutInterface.Criteria.AddCriteriaByFieldName(
        "item_loc",
        DynamicQuery.CCriteria.CSD_1.enumLogicalOperator.EqualTo,
        szLoc)
End If

When `item_loc` is not on the source report, the location label on the drilldown's tab title becomes `ALL`.

Step 4: apply the week range on mrp_trx_dt

The week is resolved using the main calendar configuration:

Dim weekRange As PeriodRange = Pulse.Calendar.GetWeek(dtTrx, Pulse.Calendar.ProductionWeekStart)
Dim dtWeekStart As Date = weekRange.StartDate
Dim dtWeekEnd As Date = weekRange.EndDate

`Pulse.Calendar.ProductionWeekStart` is the day-of-week the user (or admin) configured for the production week. `GetWeek` returns the seven-day range containing `dtTrx`. Weeks defined here align with weeks used everywhere else in the application.

The range is then expressed as two criteria on the same field:

PrimaryLayoutInterface.Criteria.AddCriteriaByFieldName(
    "mrp_trx_dt",
    DynamicQuery.CCriteria.CSD_1.enumLogicalOperator.GreaterOrEqualTo,
    DateToNum(dtWeekStart))

PrimaryLayoutInterface.Criteria.AddCriteriaByFieldName(
    "mrp_trx_dt",
    DynamicQuery.CCriteria.CSD_1.enumLogicalOperator.LessOrEqualTo,
    DateToNum(dtWeekEnd))

`DateToNum` is the standard date-to-SQL-string converter used throughout the codebase.

Operator naming

The enum names are GreaterOrEqualTo and LessOrEqualTo (no "Than" in the middle). These are the spellings on DynamicQuery.CCriteria.CSD_1.enumLogicalOperator.

Mandatory matrix elements

Three matrix elements are flagged as mandatory on the details query view so they are always cached and available regardless of the user's saved layout:

m_DetailsQueryView.MyData.m_MandatoryElementIDs = New List(Of String)
m_DetailsQueryView.MyData.m_MandatoryElementIDs.Add(Instance.MatrixElements.safety_stk.ID)
m_DetailsQueryView.MyData.m_MandatoryElementIDs.Add(Instance.MatrixElements.mrp_ord_type.ID)
m_DetailsQueryView.MyData.m_MandatoryElementIDs.Add(Instance.MatrixElements.mrp_new_qty_il.ID)

These three are required by the [color-coding handler](../color-coding/) — `mrp_ord_type` is read on every row, and the comparison value (`safety_stk` or `mrp_new_qty_il`) is read whenever a "demand" row is being drawn.

Drilldown tab title

The new tab is labeled with the location, the item number, and the week start date:

Dim szTitleDateRange As String = "(" & szLoc & ")  #" & szItem_no & "  Week of " & dtWeekStart.ToShortDateString()

If the user did not have `item_loc` visible on the source report, `szLoc` is the literal string `ALL`.

Validation

Before any of the criteria-building runs, the override checks that both required columns are visible on the source report:

If m_GridView.Columns("item_no") Is Nothing OrElse m_GridView.Columns("item_no").Visible = False Then
    MsgBox("Item # must be included on the report to enable drilldowns.")
    Exit Sub
End If
If m_GridView.Columns("mrp_trx_dt") Is Nothing OrElse m_GridView.Columns("mrp_trx_dt").Visible = False Then
    MsgBox("MRP Transaction Date must be included on the report to enable drilldowns.")
    Exit Sub
End If

If `mrp_trx_dt` is null on the row (an unusual but technically possible state), a different message is shown and the drilldown is aborted before any criteria are applied.