SQR Dynamic Break Logic Mark S. Johnson Ray Ontko & Co. Abstract Structured Query Reportwriter (SQR) is a powerful report writing language that adds, among other things, procedural control to the power of SQL. Using SQR, the application developer can quickly implement complex reports that could be extremely difficult in other environments. One tasks that remains difficult in SQR, is that of creating reports with dynamic breaks. Such a report would allow the user to specify at run-time the order of columns to be reported on, and enable the user to view the information in different ways. The regrettable alternative to implementing dynamic breaks is to create multiple reports with static breaks, different only in column ordering, column headings and ORDER BY clause. Introduction The ON-BREAK qualifier to the PRINT command empowers the application developer with control of when a value will be printed, and when a variety of other actions may be triggered. A change in value of a field being returned in a select statement is what is here referred to as a break on that field. For instance if transactions are being returned from tables containing expense reimbursement information, and they are ordered by Employee_name, transaction_type and transaction_date, then every time we start processing another employee's transactions, a break on employee_name has occurred. The developer may desire the value for employee to be printed only when there is a break on that field. Used in this way, breaks can greatly increase the readability of a report. On-breaks may be used for transaction_type and transaction_date as well, in which case a fine looking report could be produced. Sample Expense Reimbursement Report Emp Name Tran Type Tran Date Tran Amt --------- ------------- --------- ------------ Sam Jones Expense 01-JAN-93 $500.00 09-JAN-93 $750.00 19-JAN-93 $100.00 $175.00 Payment 09-JAN-93 $200.00 19-JAN-93 $300.00 $65.00 Reimbursement 15-JAN-93 $150.00 19-JAN-93 $600.00 Joe Smith Expense 19-JAN-93 $20.00 Payment 03-JAN-93 $65.00 09-JAN-93 $76.00 17-JAN-93 $105.00 The developer may wish for totals to be printed summarizing the transactions for each employee. SQR allows for actions such as this to be performed after a given field (or group of fields) breaks. Or, actions such as starting a new page with new column headings may be performed prior to the printing of records for another employee. Both of these could be performed by the following select paragraph which calls the before_emp and after_emp procedures to cause a new-page and to print totals respectively. !------------------------------------------------------------------------ begin-select employee_name &emp_name (+1,1) on-break print=change/top-page before=before_emp after=after_emp save=$old_emp_name level=1 transaction_type &tran_type (,12) on-break print=change level=2 transaction_date &tran_date (,30) on-break print=change level=3 from transaction order by employee_name, transaction_type, transaction_date end-select !------------------------------------------------------------------------ The levels assigned at the end of each select item causes the break to be triggered whenever a lower level break has been triggered. So, if an employee_name changes, even if the transaction_type remains the same, it will be reprinted as well. The save= assigns the old value to a variable which can be useful in the after procedure. When the after break procedure is invoked, the value of &emp_name has already changed, so if the value of the old employee_name is to be printed next to that employee's total, the $old_emp_name must be used. Dynamic Breaks The example above is a report with static breaks. That is, every time the report is executed, the columns will be ordered and breaking in the same way. The user that will use the report is interested in transactions, but may view those transactions only by employee, transaction_type, and transaction_date. If the user's needs are different, suppose it is desired to see the same information grouped by transaction_type then employee_name then transaction_date, then another report will be needed. Of course there are six permutations of this set of attributes, and as it turns out, they are all reasonable requests. This means that, for the user to be able to see the information in any of these ways, the application developer must write six reports that all perform similar tasks. The purpose of this paper is to examine this situation and determine if a better way can be found. It would be useful to construct a single report that would allow the user to specify how they want the columns ordered on the report. This involves dramatically modifying the select paragraph, and dynamically constructing the heading and order by, depending on values given at run-time. If it wasn't a request that I have come across numerous times as a consultant, I would probably advise against doing it. Although it is a difficult report, to the user and the analyst it is a straightforward request. There are a number of approaches (I will focus on four) to take in writing the report, and none are pretty. It is my intention to instruct on the process of writing the reports as well as advise as to which approach should be taken. The decision will be yours (and your company's) as to which approach is most suited to your needs, but will depend on factors such as: o How common a request is it for dynamically breaking reports? o How many permutations of the report exist? o How eager to learn are you developers? o How quick is your developer turnover rate? o Will the developer be the one to maintain the code? o Do you care to minimize lines of code? o Do you have an execute-only SQR license? The Multiple Report Approach Let us not rule out the option of writing separate reports for each variation. Although it is an avoidance of the problem more than a solution, it can be desirable if there are very few reports that need dynamic breaks, and few permutations of those reports. It is a testament to the difficulty of the problem that I recommend writing virtually identical reports, over using one of the other methods. This method is also recommended for the regrettable situation of having an uninspired development team. Far more code will be produced by this approach than by any other approach, increasing coding time, taking up disk space, requiring more overhead, and greatly increasing modification time. Some benefit may be made by using if statements to, in effect, comment out all of the sections of code that are not needed for the desired report. All of the reports will have to be written, or to be more precise using a relational analogy, the union of all of the reports will have to be written, in addition to the conditional logic to control execution. Slight gains in performance could be achieved by using the #if compiler directive instead of the if statement, but this would not work in an execute only environment. This approach is not highly recommended though because although it causes there to be only one report, and somewhat less code, it can increase the complexity of the report past that of some of the other approaches. Without Using On-Break: The Conditional Statement Approach This is a fairly intuitive way of solving the problem, and valuable for that reason. It is a somewhat awkward and cumbersome solution, but conceptually it is the simplest method. With this method the developer abandons SQR's On-Break print qualifier and hard codes the logic of the on-break into conditional statements. This approach is recommended if there are few permutations to the report. Otherwise the program length and complexity increases dramatically. With a complex report, this method will produce more lines of code than any other method. Additionally, it is easier to overlook bugs, and should be tested thoroughly. In implementing this method we will see some technology that will be used in all of the methods. On this material I will go into detail. First the input parameters are read into the program by the input statement. Regardless of what else is required, the program will need to know the order of the fields being processed. One solution would be to pass a single parameter with a value indicating which permutation is to be reported. Preferably, pass in a number of variables equal to the number of fields to be ordered by, and pass them in the desired order. In the case of our example, we need to know the order of employee, transaction type, and transaction date. If in the calling environment and in the report, 'A' stands for employee, 'B' for transaction type and 'C' for transaction date, then 'B A C' passed on the SQR command line would indicate that the order is transaction type, employee, transaction date. This method also works well when the reports are to be called from a form. The next stage of the process, preparation, is also implemented in all of the approaches. In the preparation stage, the dynamic ORDER BY variable and the column header variables are constructed. In construction of this process parameters section, examine each parameter in order. For each one compare it to the possible values (A, B, C, ...), and if equal, concatenate the corresponding text strings to the variables. This is also the place to set any necessary flags, although in the conditional approach there are none. If the three parameters have the values of 'B A C', then the variables would be populated as follows: $order_by = 'order by transaction_type, employee_name, transaction_date' $header = ' Tran Type Emp Name Tran Date Tran Amt ' $header_line = ' ------------- --------- --------- ------------' $order_by is used as a dynamic variable by putting it in square brackets and putting it after the where and group by clauses in the main select paragraph. The header variables are printed in the begin-header section, causing them to be printed every time a new page is written. After the process parameter section is concluded, the main select is executed. In the conditional approach's main select paragraph, the select list looks like it would if there were no breaks. The complexity of the conditional approach is entirely contained within procedural part of the paragraph. Here a flag is set to 0 that will be set to 1 when a break is caused by a change in value. Now we have an evaluate statement that has a case for each possible value for the first parameter. If the first parameter breaks (has a change of value) print it and set the flag to 1, otherwise print an empty sting of the same length so as to not interfere with the relative positioning of the other fields. Of course in doing this, we must be saving the previous value in order to compare to the present value to determine if there is a break. Next, for each case of the outer most evaluate, compare the next parameter to the remaining options, and print them or the strings of equal length depending on breaks. The evaluates and conditional statements in this part of the program are what causes this approach to be undesirable. With two parameters, there are two permutations (AB and BA). With three parameters there are six permutations (ABC, ACB, BAC, BCA, CAB, and CBA). The number of permutations grows factorially with the number of parameters, so with four parameters there will be twenty-four permutations. With three parameters there will be about two pages of conditional processing which is not pretty, and with four there will be about eight pages which is all but prohibitive. In other words, if you have four or more parameters and all of their permutations are allowed, then another approach is desirable. Because we are not using SQR's On-Break mechanism, in order to call before and after procedures, we must incorporate the functionality of before= and after= into our conditional logic. In our example, suppose we want to calculate subtotals for the lowest level break (the most significant in the order by). We need to put calls to a procedure (call it after_first_break) before the print statements that print the first parameter's field. Further more, we only want to print subtotals if the previously stored value is not null (it is not the first record returned). If we want to mention anything in the subtotal about what kind of value is being broken, we will have to have separate procedures for each of the possible lowest level break fields. Outside of the conditional processing we must move the break values to save variables, and add the amount field to total and subtotal variables. Without Using On-Break: Simulating On-Break As with the Conditional Approach, SQR's On-Break print qualifier is not used. Instead of accomplishing the task by building conditional logic to handle every possible permutation, the developer builds a loop that simulates the processing that the On-Break qualifier would perform. This approach can be implemented in less lines of code than any other, and is lends itself to better coding practice than the above approach. On the down side, it is more difficult to write the first time, but on the up side the developer will have a clear idea of what On-break is supposed to be doing by the time she is done. To quickly sketch what is being done here, in the preparation stage, in addition to the assignment of the string variables, a variable for each break field must be created that will store it's break level. If we are breaking first on employee then transaction_type then transaction_date, then we would have #emp_brk=1, #type_brk=2 and #date_brk=3. In the main select paragraph, after the select item list (or within the select item list if preferred) the host variables must be moved to a string variable after the string variable's value has been moved to a save variable. For each of the break fields, if the value changes (compare the string variable to the save variable) then move the break field variable's value to a break numeric variable (call this #break). Now we have the loop that will go from 1 to the maximum break level. When the loop counter equals the break field variable (it will equal one of them each iteration) then print the field's value if the #break is less than or equal to the counter. After breaks can be easily implemented by checking if #break = 1 before the loop is started. Using On-Break: Making Do with On-Break Another interesting approach that I have seen implemented with satisfaction uses SQR's On-Break print qualifier and a little ingenuity to obtain the desired results. In this approach, flags are set during the preparation stage indicating which fields to report on, in which order. For three parameters with all permutations allowed, we could have variables #a1, #b1 #c1, #a2, #b2, #c2, #a3, #b3 and #c3. After preparation, three variables will be flagged. Later in the select paragraph, we separate the select list from the on-break commands by using explicit print statements instead of using the position syntax at the end of the select list item. Then there are print statements (with on-breaks) that are only executed if the corresponding permutation variable is flagged. One thing for the developer to keep in mind when implementing this technique is that in processing the select paragraph, first the select list values are returned and put into the host variables, then the on-breaks are performed, and only then are the procedural statements executed. Hence, before= procedures are executed before the conditions that determine if they should be executed. In other words, your before procedures will be executed even if you don't want them to be. To get around this, put the flag check inside the before procedure as well as before the on-break statement. They are needed before the on-break as well to keep the levels from being prematurely broken. You may find that you need to use print=never in the on-break command and print the value within a before= procedure in order to surmount complications. This approach can be tricky, but it has its utility. Often times, many permutations for a given set of fields may be nonsensical. For instance if the fields are department, employee, and date, it does not make sense to break on employee, then department and then date if an employee can only be in one department. If there is a large number of parameters (four or more) and a small set of permutations (less than six or so) then this technique can be quite useful. Using On-Break: Code Generation In some ways, for some situations, this is the best of approaches. If the reports are complex enough, if your environment allows, and you have good developers, then this approach can be an excellent choice. The idea is to have this report generate, execute and then delete an SQR report. In the preparation stage of the generator, not only are the ORDER BY clause and the heading variables built, but the select items are constructed with on-breaks, before= and after=, levels and save variables. If we have three dynamic break fields as in our example, we will end the preparation stage with three variables, each holding print statements for each of the three select list items. The reports to be generated are largely similar. They differ only in the select list, the order by, and any before or after procedures. All of the report that does not change can be simply put in document paragraphs, using the begin-document statement. Text found in document paragraphs are written directly to the output file, except for !, &, $, and # which need to be duplicated or they will be interpreted as a comment or variable in the generator. By constructing variables to be printed and using begin-document, the code generator can produce the entire output file. In writing the generator, it is recommended to write one of the desired reports before writing the generator. In writing the generator, cutting and pasting will then do half of the work. This approach is attractive in ways. Although it may sound like more trouble than it is worth, it could prove to be less trouble than the other approaches. Although a tricky concept for one who has not done it before, it quickly becomes second nature. Code generation is an effective method of performing dynamic breaks because there are fairly few lines of code that differ from one permutation to the next. It may not start to seem so useful if there need to be many before= or after= procedures because this code can be used in only one permutation. For most purposes this technique works well, in fact, it is MITI's recommended method of dynamic SQR. Conclusion SQR performs admirably for many reporting needs, with impressive simplicity and efficiency. Unfortunately there are instances where the simplicity does not work to its benefit. Of course one of these is the task of dynamic breaks. It would be desirable for the level= to allow for variables, and the print statements with on-breaks (implicit or explicit) to be executed in the order of their levels. If these were the case then the approach discussed in the Making Do with On-Break section would be much simpler and certainly the optimal choice. As it is, it is trickier than it should be, but there are a number of alternatives to choose from in accomplishing your respective goals. About the Author Mark Johnson is a consultant with Ray Ontko & Co. His interests include CASE technologies, database design and code generation. He has extensive SQR programming experience, including design and implementation of a data repository. ---------------------------------------------------------------------- Mark Johnson markj@ontko.com Have a nice day. Ray Ontko & Co. ftp.ontko.com:/users/ontko