How to Add Yearly Quarters to a Query for Use in Reports

ID: Q128589

The information in this article applies to:

SUMMARY

When doing a query with more then one table being output to a report, you may need to have one of the groups in the report be Yearly Quarters. This article shows by example how to use two tables in a query being output to a Report that is grouped by Yearly Quarter, Company, and State with Subtotals for each group on the Invoices Amounts.

MORE INFORMATION

Step-by-Step Example

The CUSTOMER.DBF and INVOICES.DBF tables are in FoxPro's Tutorial Directory. They are used in this example. You may want to change some of the Invoice Dates in the INVOICES.DBF table so you'll have a broader date range.

1. Issue the following command: 

   SET DEFAULT TO SYS(2004)+'\tutorial'
2. Issue the following command: 

   MODIFY REPORT Qtr && Creates a new Report named Qtr
3. Choose Data Grouping from the Report Menu.

4. Choose the Add Button, and Add a Group named Qtr. Then Click OK.

5. Choose the Add Button again, and add a Group named State. Click OK.

6. Choose the Add Button again, and add a Group named CNO. Click OK.

7. Click OK on the Data Grouping Dialog Box.

8. Use the Field Tool to add a field to the Qtr group header band.

9. Enter "year 19"+LEFT(qtr,2)+" qtr "+RIGHT(ALLTRIM(qtr),1) in the 

   Expression Box for the Field. This will Print the Year and the Quarter.
10. Use the Field Tool to add a field to the State group header band.

11. Enter "State" without the quotation marks in the Expression Box for the 

    Field.
12. Use the Field Tool to add a field to the CNO group header band.

13. Enter "CNO" without the quotation marks in the Expression Box for the 

    Field.
14. Use the Field Tool to add a field to the Detail band.

15. Enter "INO" without the quotation marks in the Expression Box for the 

    Field.
16. Use the Field Tool to add a second field to the Detail band.

17. Enter "ITOTAL" without the quotation marks in the Expression Box for 

    the Field.
18. Use the Text Tool to add the text "Company Total" without the quotation 
    marks to the CNO group footer band.
19. Use the Field Tool to add a field to the CNO group footer band.

20. Enter "Itotal" without the quotation marks in the Expression Box for 

    the Field. Click the Calculate Box, and in the Calculate Field Window,
    choose Sum and reset on CNO.
21. Use the Text Tool to add the text "State Total" without the quotation' 
    marks to the STATE group footer band.
22. Use the Field Tool to add a field to the STATE group footer band.

23. Enter "Itotal" without the quotation marks in the Expression Box for 

    the Field. Click the Calculate Box, and in the Calculate Field Window,
    choose Sum and reset on STATE.
24. Use the Text Tool to add the text "Qtr Total" without the quotation 
    marks to the CNO group footer band.
25. Use the Field Tool to add a field to the QTR group footer band.

26. Enter "Itotal" without the quotation marks in the Expression Box for 

    the Field. Click the Calculate Box, and in the Calculate Field Window,
    choose Sum and reset on QTR.
27. Save the Report.

28. Enter MODIFY COMMAND Quarter in the Command window.

29. In the QUARTER.PRG Window, enter this code: 

    SELECT customer.cno, customer.company, customer.state, invoices.ino, ;
        invoices.itotal, ;
        RIGHT(STR(YEAR(invoices.idate)),2)+ ;
        ALLTRIM(STR(INT((MONTH(invoices.idate)-1)/3)+1)) AS qtr ;
        FROM customer, invoices ;
        WHERE invoices.cno = customer.cno ;
        ORDER BY qtr, customer.state, customer.cno, invoices.ino ;
        INTO CURSOR qtr1
        REPORT FORM qtr.frx PREVIEW
30. Save and then Run the QUARTER.PRG program.

The results will show all the quarters to be from second quarter of 1990. For a more descrptive analysis, modify some of the dates to other quarters or years in the idate field of the table INVOICES.DBF in the Tutorial subdirectory.

For more information about how to do a simple Report grouped by yearly quarters, please see the following article in the Microsoft Knowledge Base: 

   ARTICLE-ID: Q95313
   TITLE     : How to Group Data by Yearly Quarters
Additional reference words: FoxWin FoxDos FoxMac FoxUnix 2.60a fourth annual quad KBCategory: kbprg kbcode KBSubcategory: FxprgSql

Last Reviewed: June 27, 1995