KnowledgeBase 00104: Printing the Value of a Suppressed Breakpoint

Last updated: 17 Feb 2017 Applies to: All versions

Search   Top level index       Full Index Search Tips Previous article     Next article

This article was originally published as a Tip of the Week.

The Problem

Consider a report such as

   LIST SALES BY REGION BREAK.SUP REGION ORDER.NO SALESMAN TOTAL VALUE 
   Order  Salesman   Value.. 
   10268  Roberts    1033.77 
   10274  Jones       876.43 
   10275  Roberts     268.00 
                     ------- 
                     2178.20 
  
   10269  Edwards     564.98 
   10272  Edwards     768.33 
   10273  Smith       879.34 
                     ------- 
                     2212.65 
   --etc-- 

In this example, we are breaking on the sales region, showing all sales in that region together with the salesman name.

Perhaps we would like to show the sales region name. We can do this with a query such as

   LIST SALES BY REGION BREAK.ON REGION ORDER.NO SALESMAN TOTAL VALUE 
   Region   Order  Salesman   Value.. 
   North    10268  Roberts    1033.77 
   North    10274  Jones       876.43 
   North    10275  Roberts     268.00 
                              ------- 
                              2178.20 
  
   Central  10269  Edwards     564.98 
   Central  10272  Edwards     768.33 
   Central  10273  Smith       879.34 
                              ------- 
                              2212.65 
   --etc-- 

or we can tidy things up a bit with the 'O' breakpoint qualifier to show the region name only once.

   LIST SALES BY REGION BREAK.ON "'O'" REGION ORDER.NO SALESMAN TOTAL VALUE 
   Region   Order  Salesman   Value.. 
   North    10268  Roberts    1033.77 
            10274  Jones       876.43 
            10275  Roberts     268.00 
                              ------- 
                              2178.20 
  
   Central  10269  Edwards     564.98 
            10272  Edwards     768.33 
            10273  Smith       879.34 
                              ------- 
                              2212.65 
   --etc-- 

Maybe what we really want is for the region name to appear in the Salesman column of the subtotal line. How can we achieve this?

The 'X' Breakpoint Control Code and @BPV

QM includes an 'X' breakpoint conrtol code that saves the previous value of the breakpoint field in a system variable named @BPV. We can make use of this in a dictionary I-type item that evaluates to the salesman name on a detail line and the region name saved in @BPV on a subtotal line. We also need to return appropriate text for the grand total line. The three line types can be distinguished by checking the breakpoint level in @NB.

The I-type to achieve this for the sales example becomes

   if @nb = 0 then salesman else if @nb = 255 then 'Total' else @bpv 

If this is saved in the dictionary as SALESMAN.REGION, our query becomes

   LIST SALES BY REGION BREAK.SUP "'X'" REGION ORDER.NO CALC SALESMAN.REGION TOTAL VALUE ID.SUP 
   Order  Salesman   Value.. 
   10268  Roberts    1033.77 
   10274  Jones       876.43 
   10275  Roberts     268.00 
          --------   ------- 
          North      2178.20 
  
   10269  Edwards     564.98 
   10272  Edwards     768.33 
   10273  Smith       879.34 
          --------   ------- 
          Central    2212.65 
   --etc-- 
          ========   ======= 
          Total      9862.13 

Note the use of the CALC keyword to evaluate the SALESMAN.REGION on the subtotal and grand total lines.

Related Articles

None.

Please tell us if this article was helpful

Very     Slightly     Not at all

Comments

Email (optional)