Setting up a Sub-Drilldown Based on a Stored Procedure

Second-level drilldowns can be very useful in cases where a critical number is calculated from subtotaled data, and the details of the subtotal need to be accessible. You can use Output Column property names of the parent drilldown as input parameters for the sub-drilldown.

The preconfigured Inventory Value critical number is a good example of second level drilldowns for stored procedure-based critical numbers. This critical number shows your whole inventory value, and drills down to a subtotal by inventory. It drills down one step further to item totals or location totals by warehouse.

To associate a new drilldown as a sub drilldown:

  1. Create the drilldown.

    See Setting Up a Stored Procedure-based Critical Number Drilldown.

  2. Optionally, use the @Parms parameters to accept filters into your drilldown.

    For example, the Inventory Value Detail Drilldown (SSSWBCanInvValDtlSp) accepts Whse in @Parm1, Item in @Parm2, and Location in @Parm3. It is coded to use these values as filters if provided, or ignore them if they are not provided.

  3. Specify Source Parms in sequence for how you want to accept your parameters in your stored procedure.

    When one drilldown calls another, the sub-drilldown automatically pulls these values by column header name from the calling drilldown. For example, the Item Inventory Value Detail drilldown has a Column Heading named Item. When it calls the Inventory Value Detail sub-drilldown, the Item value is passed to the Inventory Value Detail program in @Parm2.

  4. Launch the Drilldowns Setup form.
  5. Navigate to the top level drilldown.
  6. On the Sub Drilldowns tab, specify the new drilldown and a description for it.
  7. Save the record.

Example:

First Level Drilldown Program:

SSSWBCanInvValItemDtlSp 


CREATE PROCEDURE SSSWBCanInvValItemDtlSp (
  @AsOfDate        DateType
, @DrillNum        WBDrillNumType
, @CrNum           WBCrNumType
, @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 
  @RowPointer RowPointer
, @TmpAmount  AmountType
, @StartItem  ItemType
, @EndItem    ItemType
DECLARE @ttItemloc TABLE (
  RowPointer uniqueidentifier
, item       nvarchar(30)
, amount     decimal(20,8)
, processed  tinyint
)
SET @StartItem  = ISNULL(@Parm1, dbo.LowString('ItemType'))
SET @EndItem    = ISNULL(@Parm1, dbo.HighString('ItemType'))
INSERT INTO @ttItemloc
SELECT RowPointer, item, 0, 0
FROM itemloc
WHERE item BETWEEN @StartItem AND @EndItem
WHILE EXISTS (SELECT TOP 1 1 FROM @ttItemloc WHERE processed = 0)
BEGIN
   SELECT TOP 1 @RowPointer = RowPointer
   FROM @ttItemloc
   WHERE processed = 0
   SET @TmpAmount = 0
   EXEC SSSWBCanInvValSubItemlocSp @RowPointer, @TmpAmount OUTPUT
   UPDATE @ttItemloc
   SET amount = @TmpAmount
   , processed = 1
   WHERE RowPointer = @RowPointer
END
INSERT INTO #tt_drill_results(
  CHAR01, DECI01, amount
)
SELECT item, SUM(amount), SUM(amount)
FROM @ttItemloc
GROUP BY item
UPDATE #tt_drill_results
SET RowPointer = item.RowPointer
FROM #tt_drill_results tt, item
WHERE item.item = tt.CHAR01
RETURN 0

Second Level Drilldown Program:

SSSWBCanInvValItemDtlSp 


CREATE PROCEDURE SSSWBCanInvValDtlSp (
  @AsOfDate        DateType
, @DrillNum        WBDrillNumType
, @CrNum           WBCrNumType
, @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 
  @RowPointer RowPointer
, @Whse       WhseType
, @TmpAmount  AmountType
, @StartWhse  WhseType
, @EndWhse    WhseType
, @StartItem  ItemType
, @EndItem    ItemType
, @StartLoc   LocType
, @EndLoc     LocType
DECLARE @ttItemloc TABLE (
  RowPointer uniqueidentifier
, whse       nvarchar(4)
, item       nvarchar(30)
, loc        nvarchar(15)
, amount     decimal(20,8)
, processed  tinyint
)
SET @StartWhse = ISNULL(@Parm1, dbo.LowString('WhseType'))
SET @EndWhse   = ISNULL(@Parm1, dbo.HighString('WhseType'))
SET @StartItem = ISNULL(@Parm2, dbo.LowString('ItemType'))
SET @EndItem   = ISNULL(@Parm2, dbo.HighString('ItemType'))
SET @StartLoc  = ISNULL(@Parm3, dbo.LowString('LocType'))
SET @EndLoc    = ISNULL(@Parm3, dbo.HighString('LocType'))
INSERT INTO @ttItemloc
SELECT RowPointer, whse, item, loc, 0, 0
FROM itemloc
WHERE whse BETWEEN @StartWhse AND @EndWhse
  AND item BETWEEN @StartItem AND @EndItem
  AND loc  BETWEEN @StartLoc  AND @EndLoc
WHILE EXISTS (SELECT TOP 1 1 FROM @ttItemloc WHERE processed = 0)
BEGIN
   SELECT TOP 1 @RowPointer = RowPointer
   FROM @TTItemloc
   WHERE processed = 0
   SET @TmpAmount = 0
   EXEC SSSWBCanInvValSubItemlocSp @RowPointer, @TmpAmount OUTPUT
   UPDATE @ttItemloc
   SET amount = @TmpAmount
   , processed = 1
   WHERE RowPointer = @RowPointer
END
INSERT INTO #tt_drill_results(
  CHAR01, CHAR02, CHAR03, DECI01, amount, RowPointer
)
SELECT whse, item, loc, amount, amount, RowPointer
FROM @ttItemloc
RETURN 0
Related topics