Summary Reports Note: We are supplying the accompanying application note as an example for our more advanced user community. We do not provide consulting services. Should you need further assistance in adapting this application to your specific needs, we will be glad to provide a list of consultants closest to your area. Simple Summary Report If you wanted to know the total salaries for each department in the sample EMPLOYEE.DTF database supplied with your program diskettes, you could define a simple report as follows. On the Column/sort spec enter: Department: 1,AS Salary: 4,ST On the Report print options screen, you would choose "Yes" for "Print totals only" and the result would be a report that looks like this: Department Salary ---------- ------------ ACCNT ------------ Total: $35,000.00 ADMIN ------------ Total: $138,000.00 EXEC ------------ Total: $177,000.00 etc. Summary Report with Enhanced Formatting On some occasions, you may want a report with the totals and the department name on the same line, as follows: Department Salary ---------- ----------- ACCNT $35,000.00 ADMIN $138,000.00 EXEC $177,000.00 etc. The following specs will produce this report. Q & A Application Note Rev. 6/92 #2213 Page 1 of 5 Summary Reports On the Column/sort spec enter: Department: 1,AS Salary: 4,I The required derived columns are: Heading: Salary Formula: @TEXT( (#1<>#50), @STR( @TOTAL(#4,#1) ) ) Column Spec: 5,F(M) Heading: Formula: #1 Column Spec: 50,I On the Report Print Options screen, select "NO" for "Print Totals Only". Programming Explanation: The pieces used to produce column 5 of this report are the following: Formula: @TEXT(,) This is a function which produces a specified number of copies of a piece of text. The number of copies is the first element, and the text copied is the second. @TEXT(1,"STARS") will produce a single copy: STARS @TEXT(0,"STARS") will produce a blank. Formula: @TEXT( (#1<>#50),) This is a statement which can be true or false. It asserts that column 1 is not equal to column 50. As used in this statement, it will produce a number-a 1 when the statement is true and a 0 when it is false. @TEXT( (#1<>#50), "STARS") is evaluated as follows. 旼컴컴컴컴컴컴컴컫컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컫컴컴컴컴컴컴컴� � � � � � Is #1 � � When you � � different � @TEXT( (#1<>#50),"STARS") � print you � � from #50? � is the same as: � get: � 쳐컴컴컴컴컴컴컴컵컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컵컴컴컴컴컴컴컴� � yes � @TEXT(1,"STARS") � STARS � � � � � � no � @TEXT(0,"STARS") � a blank � 읕컴컴컴컴컴컴컴컨컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컨컴컴컴컴컴컴컴� Q & A Application Note Rev. 6/92 #2213 Page 2 of 5 Summary Reports Q&A calculates its derived columns in order as it goes across the page. This leads to an interesting feature when Q&A determines if two columns are the same. To explore this feature, consider a report with three columns, labeled column 1, column 5 and column 50. Column 50 is a derived column which is defined as follows: Heading: Column 50 Formula: #1 Column Spec: 50 There are three records in the database with data in the fields, such that the report will look like this: Column 1 Column 5 Column 50 -------- -------- --------- Exec Brothers Exec Sales Johnson Sales Sales Jones Sales 旼컴컴� In the following examples, a � Box � will indicate the step that Q&A is working on: 읕컴컴� Column 1 Column 5 Column 50 -------- -------- --------- 旼컴컴컴컴컴� Exec � Brothers � 읕컴컴컴컴컴� Notice that Column 50 is still empty. Column 1 Column 5 Column 50 -------- -------- --------- Exec Brothers Exec 旼컴컴컴컴컴� Sales � Johnson � 읕컴컴컴컴컴� Line 2, Column 50 is blank but Line 1, Column 50 still holds Exec. If you were to stop Q&A now and ask what was the value in Column 50, it would answer "Exec." Column 1 Column 5 Column 50 -------- -------- --------- Exec Brothers Exec Sales Johnson Sales 旼컴컴컴컴컴� Sales � Jones � 읕컴컴컴컴컴� Q & A Application Note Rev. 6/92 #2213 Page 3 of 5 Summary Reports Line 3, Column 50 is also blank, but Q&A would say that the value in Column 50 was "Sales" from line 2. The way Q&A thinks of column 50 can be very useful if you want to know whether column 1 has changed from the previous line in the report. To take advantage of it, consider a report using derived columns with the following formulas: In column 5: @TEXT((#1<>#50),"STARS") and in column 50: #1 Column 5 is evaluated as follows: 旼컴컴컴컴컴컴컫컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴쩡컴컴컴컴컴컴컴� � � The formula in col. 5: � When you � � Has col. � @TEXT((#1<>#50),"STARS") � print you � � 1 changed? � is the same as: � get: � 쳐컴컴컴컴컴컴컵컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴탠컴컴컴컴컴컴컴� � yes � @TEXT(1,"STARS") � STARS � � � � � � no � @TEXT(0,"STARS") � a blank � 읕컴컴컴컴컴컴컨컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴좔컴컴컴컴컴컴컴� What we have so far is a way of printing something in a column when a particular column to its left changes. Now we need some useful information to print. Formula: @TEXT( (#1<>#50), @STR( ) ) @STR converts a number to its character equivalent. This detail allows @TEXT to work with the kind of information it expects. Formula: @TEXT( (#1<>#50), @STR( @TOTAL(#4,#1) ) ) @TOTAL prints the subtotal accumulated in column 4 when column 1 changes. Q&A provides several other functions for summarizing data. The complete list of these functions appears in Table R-6 in your manual. Formula: @TEXT( (#1<>#50), @STR( @TOTAL(#4,#1) ) ) Column Spec: 5,F(M) Q & A Application Note Rev. 6/92 #2213 Page 4 of 5 Summary Reports F(M) in the column spec causes the printed output to be formatted as a money field. Other choices are available, see Table R-3 in your manual for a list. This formula and column spec ensure that a subtotal will be printed in column 5 only when column 1 changes. The repeating values in column 1 are suppressed as well, so the only time there is something to print in the report is when column 1 has a new value. The neatly formatted report results. Q & A Application Note Rev. 6/92 #2213 Page 5 of 5