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.
- Issue the following command:
SET DEFAULT TO SYS(2004)+'\tutorial'
- Issue the following command:
MODIFY REPORT Qtr && Creates a new Report named Qtr
- Choose Data Grouping from the Report Menu.
- Choose the Add Button, and Add a Group named Qtr. Then Click OK.
- Choose the Add Button again, and add a Group named State. Click OK.
- Choose the Add Button again, and add a Group named CNO. Click OK.
- Click OK on the Data Grouping Dialog Box.
- Use the Field Tool to add a field to the Qtr group header band.
- 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.
- Use the Field Tool to add a field to the State group header band.
- Enter "State" without the quotation marks in the Expression Box for the
Field.
- Use the Field Tool to add a field to the CNO group header band.
- Enter "CNO" without the quotation marks in the Expression Box for the
Field.
- Use the Field Tool to add a field to the Detail band.
- Enter "INO" without the quotation marks in the Expression Box for the
Field.
- Use the Field Tool to add a second field to the Detail band.
- Enter "ITOTAL" without the quotation marks in the Expression Box for
the Field.
- Use the Text Tool to add the text "Company Total" without the quotation
marks to the CNO group footer band.
- Use the Field Tool to add a field to the CNO group footer band.
- 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.
- Use the Text Tool to add the text "State Total" without the quotation'
marks to the STATE group footer band.
- Use the Field Tool to add a field to the STATE group footer band.
- 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.
- Use the Text Tool to add the text "Qtr Total" without the quotation
marks to the CNO group footer band.
- Use the Field Tool to add a field to the QTR group footer band.
- 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.
- Save the Report.
- Enter MODIFY COMMAND Quarter in the Command window.
- 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
- 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
|