Setting Up a Stored Procedure Critical Number Drilldown

You can set up a drilldown for a critical number based on a stored procedure either by creating one new or by copying an existing one and then modifying it.

To set up a drilldown for a critical number based on a stored procedure:

  1. Open the Drilldowns Setup form and execute Filter In Place.
  2. In the Drilldown field, specify an unused integer to identify the drilldown.

    If you do not specify an integer here, the system automatically assigns the next available integer.

  3. In the Description field, provide a descriptive name by which the drilldown and its use can be easily identified.
  4. On the General tab, make the basic specifications for the drilldown.

    For more information, see Making General Tab Settings for a Drilldown.

  5. Optionally, use the Output Columns tab  to rearrange the order in which the output is to be displayed and the captions to be displayed for those columns.

    The data that is displayed on this tab is derived from the stored procedure identified in Step 4.

    For more information, see Setting Up Stored Procedure-based Drilldowns - Output Columns Tab.

  6. Optionally, use the Categories tab to specify one or more categories to which the drilldown belongs.

    When you select a category, the Category Description field is populated automatically.

    For more information, see Critical Number Categories.

  7. Optionally, use the Sub Drilldowns tab to specify one or more sub-drilldowns to use in conjunction with the selected drilldown.

    For more information, see Setting up a Sub-Drilldown Based on a Stored Procedure.

  8. Optionally, use the Static Parameters tab to specify one or more name-value pairs
    Note: 

    If the data needs to be refined by a hard-coded list of values, we recommend that you assign the name and value on this tab. You can then reference the static parameters when you set up the critical number/drilldown filter using the CRPARM() syntax.

    For example, a critical number based on an Account Balance stored procedure might have a static parameter called Acct. This tells the stored procedure which account number to use. The number that ships with the product uses 10000 (cash), but users can change this value if they want to monitor a different account or if their cash account uses a different account number.

  9. Optionally, use the Input Parameters tab to provide values that can be used in processing sub-drilldowns, especially.
  10. Associate the drilldown with a critical number:
    • Launch the Critical Numbers Setup form and navigate to the critical number.
    • On the Drilldowns tab, select the new drilldown and specify a description.
    • Save the record.
  11. Create your stored procedure in your preferred code editor.  

    The parameters for a drilldown are slightly different from a critical number, as shown in this example: 

    CREATE PROCEDURE WBCanCoitemPastDueDetailsSp (
  @AsOfDate        DateType
, @DrillNum        WBDrillNumType
, @KPINum           WBKPINumType
, @Id              nvarchar(500)
, @Parm1           WBSourceNameType
, @Parm2           WBSourceNameType
, @Parm3           WBSourceNameType
, @Parm4           WBSourceNameType
, @Parm5           WBSourceNameType
, @Parm6           WBSourceNameType
, @Parm7           WBSourceNameType
, @Parm8           WBSourceNameType
, @Parm9           WBSourceNameType
, @Parm10          WBSourceNameType
, @Parm11          WBSourceNameType
, @Parm12          WBSourceNameType
, @Parm13          WBSourceNameType
, @Parm14          WBSourceNameType
, @Parm15          WBSourceNameType
, @Parm16          WBSourceNameType
, @Parm17          WBSourceNameType
, @Parm18          WBSourceNameType
, @Parm19          WBSourceNameType
, @Parm20          WBSourceNameType
, @Parm21          WBSourceNameType
, @Parm22          WBSourceNameType
, @Parm23          WBSourceNameType
, @Parm24          WBSourceNameType
, @Parm25          WBSourceNameType
, @Parm26          WBSourceNameType
, @Parm27          WBSourceNameType
, @Parm28          WBSourceNameType
, @Parm29          WBSourceNameType
, @Parm30          WBSourceNameType
, @Parm31          WBSourceNameType
, @Parm32          WBSourceNameType
, @Parm33          WBSourceNameType
, @Parm34          WBSourceNameType
, @Parm35          WBSourceNameType
, @Parm36          WBSourceNameType
, @Parm37          WBSourceNameType
, @Parm38          WBSourceNameType
, @Parm39          WBSourceNameType
, @Parm40          WBSourceNameType
, @Parm41          WBSourceNameType
, @Parm42          WBSourceNameType
, @Parm43          WBSourceNameType
, @Parm44          WBSourceNameType
, @Parm45          WBSourceNameType
, @Parm46          WBSourceNameType
, @Parm47          WBSourceNameType
, @Parm48          WBSourceNameType
, @Parm49          WBSourceNameType
, @Parm50          WBSourceNameType
) AS

    Records are returned to the user through the WBTmpDrilldowns temporary table. The columns that you set in this temporary table correspond to the columns you set up on the Output Columns tab on the Drilldowns Setup form. If you specified a Detail form on the Drilldowns Setup form and you want to see details on the specific record, set the RowPointer, as shown in this example: 

    INSERT INTO WBTmpDrilldowns(
  RefRowPointer
, DATE01
, CHAR01
, INTE01
, CHAR02
, CHAR03
, SessionID
)
SELECT
  coitem.RowPointer
, coitem.due_date
, co.co_num
, coitem.co_line
, co.cust_num
, custaddr.name
, @SessionID
FROM coitem
INNER JOIN co
   ON co.co_num = coitem.co_num
LEFT OUTER JOIN custaddr
  ON custaddr.cust_num = co.cust_num
 AND custaddr.cust_seq = co.cust_seq
LEFT OUTER JOIN item itm
  ON itm.item = coitem.item
WHERE co.cust_num = ISNULL(NULLIF(@CustNum,''), co.cust_num)
  AND co.co_num = ISNULL(NULLIF(@CoNum,''), co.co_num)
  AND charindex(coitem.stat, @CoitemStatList) > 0
  AND qty_ordered > qty_shipped
  AND ISNULL(due_date, '1900-01-01') = dateadd(dd,="" @latedays,="" @asofdate)="" and="" ship_site="@ParmsSite" charindex(co.stat,="" @costatlist)=""> 0
  AND co.credit_hold = @CredHold
  AND (@LineFilter IS NULL OR coitem.co_line = @LineFilter)
  AND (@ItemFilter IS NULL OR coitem.item = @ItemFilter)
  AND (@ProdCodeFilter IS NULL OR itm.product_code = @ProdCodeFilter)
  AND (@WhseFilter IS NULL OR co.whse = @WhseFilter)
  AND (@StatFilter IS NULL OR co.stat = @StatFilter)
  12. You can set these additional values in the WBTmpDrilldowns table to affect what is displayed in the drilldown:
    • RowPointer provides a link to the specific record when launching a detail form.
    • GoalValue overrides the Goal Value from the Drilldowns Setup form.
    • AlertValue overrides the Alert Value from the Drilldowns Setup form.

This example shows Customer Order Past Due: 

CREATE PROCEDURE WBCanCoitemPastDueDetailsSp (
  @AsOfDate        DateType
, @DrillNum        WBDrillNumType
, @KPINum           WBKPINumType
, @Id              nvarchar(500)
, @Parm1           WBSourceNameType
, @Parm2           WBSourceNameType
, @Parm3           WBSourceNameType
, @Parm4           WBSourceNameType
, @Parm5           WBSourceNameType
, @Parm6           WBSourceNameType
, @Parm7           WBSourceNameType
, @Parm8           WBSourceNameType
, @Parm9           WBSourceNameType
, @Parm10          WBSourceNameType
, @Parm11          WBSourceNameType
, @Parm12          WBSourceNameType
, @Parm13          WBSourceNameType
, @Parm14          WBSourceNameType
, @Parm15          WBSourceNameType
, @Parm16          WBSourceNameType
, @Parm17          WBSourceNameType
, @Parm18          WBSourceNameType
, @Parm19          WBSourceNameType
, @Parm20          WBSourceNameType
, @Parm21          WBSourceNameType
, @Parm22          WBSourceNameType
, @Parm23          WBSourceNameType
, @Parm24          WBSourceNameType
, @Parm25          WBSourceNameType
, @Parm26          WBSourceNameType
, @Parm27          WBSourceNameType
, @Parm28          WBSourceNameType
, @Parm29          WBSourceNameType
, @Parm30          WBSourceNameType
, @Parm31          WBSourceNameType
, @Parm32          WBSourceNameType
, @Parm33          WBSourceNameType
, @Parm34          WBSourceNameType
, @Parm35          WBSourceNameType
, @Parm36          WBSourceNameType
, @Parm37          WBSourceNameType
, @Parm38          WBSourceNameType
, @Parm39          WBSourceNameType
, @Parm40          WBSourceNameType
, @Parm41          WBSourceNameType
, @Parm42          WBSourceNameType
, @Parm43          WBSourceNameType
, @Parm44          WBSourceNameType
, @Parm45          WBSourceNameType
, @Parm46          WBSourceNameType
, @Parm47          WBSourceNameType
, @Parm48          WBSourceNameType
, @Parm49          WBSourceNameType
, @Parm50          WBSourceNameType
) AS
DECLARE @Severity INT
, @CoNum    CoNumType
, @CustNum  CustNumType
, @ParmsSite SiteType
, @CoStatList  LongListType
, @CoitemStatList    LongListType
, @CredHold          ListYesNoType
, @LateDays INT
, @LineFilter        CoLineType
, @ItemFilter        ItemType
, @ProdCodeFilter    ProductCodeType
, @WhseFilter        WhseType
, @StatFilter        CoitemStatusType
, @SessionID      RowPointerType
SET @Severity = 0
SET @LateDays = 0
SET @SessionId = dbo.SessionIdSp()
SET @CustNum = dbo.ExpandKyByType('CustNumType', @Parm1) SET @CoNum = dbo.ExpandKyByType('CoNumType', @Parm2) SET @LineFilter = NULLIF(@Parm3, '') SET @ItemFilter = NULLIF(@Parm4, '') SET @ProdCodeFilter = NULLIF(@Parm5, '') SET @WhseFilter = NULLIF(@Parm6, '') SET @StatFilter = NULLIF(@Parm7, '')
IF @CoNum IS NULL AND @LineFilter IS NOT NULL
   SET @LineFilter = NULL
SELECT @ParmsSite = site
FROM parms
SET @CoStatList     = ISNULL(dbo.WBGetDrillParm(@DrillNum, @KPINum, 'COStatusList'), 'POS')
SET @CoitemStatList = ISNULL(dbo.WBGetDrillParm(@DrillNum, @KPINum, 'COITEMStatusList'), 'PO')
SET @CredHold       = ISNULL(dbo.WBGetDrillParm(@DrillNum, @KPINum, 'CredHold'), 0)
SET @LateDays       = ISNULL(dbo.WBGetDrillParm(@DrillNum, @KPINum, 'LateDaysTolerance'), 0)
INSERT INTO WBTmpDrilldowns(
  RefRowPointer
, DATE01
, CHAR01
, INTE01
, CHAR02
, CHAR03
, SessionID
)
SELECT
  coitem.RowPointer
, coitem.due_date
, co.co_num
, coitem.co_line
, co.cust_num
, custaddr.name
, @SessionID
FROM coitem
INNER JOIN co
   ON co.co_num = coitem.co_num
LEFT OUTER JOIN custaddr
  ON custaddr.cust_num = co.cust_num
 AND custaddr.cust_seq = co.cust_seq
LEFT OUTER JOIN item itm
  ON itm.item = coitem.item
WHERE co.cust_num = ISNULL(NULLIF(@CustNum,''), co.cust_num)
  AND co.co_num = ISNULL(NULLIF(@CoNum,''), co.co_num)
  AND charindex(coitem.stat, @CoitemStatList) > 0
  AND qty_ordered > qty_shipped
  AND ISNULL(due_date, '1900-01-01') = dateadd(dd,="" @latedays,="" @asofdate)="" and="" ship_site="@ParmsSite" charindex(co.stat,="" @costatlist)="" 0
  AND co.credit_hold = @CredHold
  AND (@LineFilter IS NULL OR coitem.co_line = @LineFilter)
  AND (@ItemFilter IS NULL OR coitem.item = @ItemFilter)
  AND (@ProdCodeFilter IS NULL OR itm.product_code = @ProdCodeFilter)
  AND (@WhseFilter IS NULL OR co.whse = @WhseFilter)
  AND (@StatFilter IS NULL OR co.stat = @StatFilter)
RETURN @Severity
Related topics
About Critical Number Drilldowns
Setting Up an IDO Critical Number Drilldown
Setting up a Sub-Drilldown Based on a Stored Procedure
About Critical Numbers
Defining a Critical Number