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

Last reviewed: June 27, 1995
Article ID: Q128589
The information in this article applies to:
  • Microsoft FoxPro for Windows, version 2.6a
  • Microsoft FoxPro for Macintosh, version 2.6a
  • Microsoft FoxPro for MS-DOS, version 2.6a
  • Microsoft FoxPro for Unix, version 2.6

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


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: June 27, 1995
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.