![]() |
Powered by QM on a Rpi server |
|
KnowledgeBase 00104: Printing the Value of a Suppressed BreakpointThis article was originally published as a Tip of the Week. The ProblemConsider 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 @BPVQM 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 @bpvIf 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.13Note the use of the CALC keyword to evaluate the SALESMAN.REGION on the subtotal and grand total lines. Related ArticlesNone. |