Populating Visual FoxPro Reports with data on the Fly

Abstract:
Visual FoxPro’s report writer is a powerful				REPORT FORM Report4.frx NOCONSOLE
full-featured report writer that provides all the				PREVIEW
tools you need to create and ship compelling				g. Click the Save button on the button
reports with your application. On Most occasions,				bar to save the form you have created.h.
you can create Visual FoxPro reports based only				Run the form by either pressing Ctrl + E or by
on data that already exists (e.g. an existing table,				choosing the Run toolbar button on the toolbar.
query or view). However, imagine that the data				The form runs.i. Now click the
you need to populate your report with does not				command button to run the report. The report
exist as a table that can be readily added to your				appears in print preview window.
report’s data environment nor does it exist				Applying the concept to a Real Life Situation
as a set of tables upon which you can execute a				The example already illustrated has been kept
straight-forward SQL Query or view! This article				deliberately simple. Now, this must be applied in a
describes how you can populate a Visual FoxPro				real-life situation. In the example shown, the
report at run time by collecting report criteria				cursor is populated by issuing APPEND BLANK
from users, deriving the data from your tables				commands. In a real-life application however, it
based on that criteria, creating a cursor and then				may be possible that your data may already
populating the cursor with the derived data all at				exist, needing only to be read back and
run time.				re-arranged into a format that your report can
				print. Lets illustrate this by building a Trial Balance
Who should read this article?				Report as at a given day (Trial balances are
This article assumes some familiarity with building				usually printed as at a given period end date).
and developing applications with Microsoft Visual				To be able to print such a trail balance, we would
FoxPro 9				have to know the balance of an account as at a
Introduction:				given day. Lets now accept that such data is
One of the key benefits of using Visual FoxPro as				stored day-by-day as transactions are passed in a
your primary development tool is that it provides				table called ActGLDayTot and this table could be
all of the tools you need to build powerful,				created with the following SQL Statement:
compelling full-featured applications that are fast				CREATE TABLE ActGLDayTot(BatchNo c(20)
and feature rich!				PRIMARY KEY,AccountCode c(15),;
One of the features of Visual FoxPro that makes				ValueDate D,CurrYear i,NextYear i,PeriodNumb
this possible is the newly revamped report writer				i,MonthNumb i,Debitamt Y,;
that ships with Visual FoxPro 9! Because of the				CreditAmt Y,Balance Y)
need to provide or make data available for your
report or label before you print it, the Report				For each account in the General Ledger Master
Writer provides a variety of ways to make data				file, we want to obtain the account’s balances
easily available for your report.				as at the specified day so that we will be able to
One method of making data available for your				build the trial balance. Now imagine that your
report and incidentally the most often used is to				General Ledger Master table could be created
write a report that is always based on the same				with the following SQL Statement:
data sources. This method would actually require				CREATE TABLE ActGLMast(AccountCode c(15)
you to add tables or views to the data				PRIMARY KEY,AccountName c(50),;
environment of a report or to use a DO				AccountType c(40),CurrBal Y)
<query> command in the Init event of the				The following code is placed in the Init Event of
report’s data environment. Alternatively, you				the data environment of our report:
could execute a query by creating an executing				* This Code Constructs the cursor used in the
an SQL Statement in the Init event of the				Report
report’s data environment.				LOCAL dValueDate AS Date,cBranchCode aS
A second method is when you need to create a				Character,intNoOfRows as Integer,oDT as Object
report that utilizes separate sets of data sources				,lAnswer as Logical
for the report. In this case, you would dynamically				DIMENSION arrTR(1,9)
open those data sources at run time by using a				STORE "" TO cBranchCode
USE <table>, USE <view>, DO				STORE ({ / / }) TO dValueDate
<query> or SQL SELECT statement to the				STORE 0 TO intNoOfRows
click event of a button or other code that will run
before you issue a REPORT or LABEL command.				* 1) Open the form and obtain the parameters
However, what happens when the data for your				DO FORM frmMgtTBalByDate.Scx
report is not in a table that can be dynamically				LINKEDdValueDate =
queried with a SELECT SQL statement nor is it in				=
a form that you can simply open with a USE				lbyDate.RELEASE
command! The Richness of the Visual FoxPro				*OPENTABLES()
programming language allows you to easily solve				oDT =
this problem as this article will demonstrate. To				CREATEOBJECT('ActGLDayTot')lAnswer =
demonstrate the concept being discussed, imagine				R,intNoOfRows,chrProgTitle)
that you need to print or generate the Income				CREATE CURSOR MgtTBalByDate (AccountCode
and Expense Report for your company as at a				c(20),AccountName c(50),AccountType
given day! This would mean that you need to				c(50),TBalDate D,MTDDebit Y,MTDCredit
keep a daily running balance of each account in				Y,YTDDebit Y,YTDCredit Y,UserName C(10))
your General Ledger chart of accounts in a table				SELECT MgtTBalByDate
that we shall perhaps call ActGLDayTot. The				APPEND FROM ARRAY arrTR
sections that follow will describe how to create				This.OpenTables()
these tables and their structure as well as the
code that generates the report.				In the code above memory variables are declared
Printing Reports in Visual FoxPro				that will be used either to hold the user’s
As already noted, you need to make data				report criteria or to store and transport data. The
available for a report before you proceed to print				DIMENSION command creates an array that will
the report. To print a report in Visual FoxPro, you				be used to return data from a Data Aware Class
will need to issue the REPORT FORM				that encapsulates the functionality of the
<reportname> command. For the full syntax				ActGLDayTot table. Another thing to note is how
of the REPORT FORM command, see your Visual				we have allowed users to specify the data to be
FoxPro 9 online help documentation.				included in the report by entering the specified
If you issue the REPORT FORM command with				date for which they want a trial balance. To
no tables in the data environment and no data				collect this information from the users, the line DO
sources open in the data environment of the				FORM frmMgtTBalByDate.Scx runs the report
report, the report simply appears to flash and				parameter form so users can enter criteria.
then close again! To avoid this situation, you will				When the user clicks the Ok button in that
either need to add data to the data environment				form, a THISFORM.Hide command temporarily
or use the Init event of the data environment to				hides the form from the user while the lines
make data available within the data environment				immediately following the DO FORM command
prior to printing. When the data does not already				collect the information on the form. The line
exist in a query or in a form that can be directly				frmMgtTBalByDate.RELEASE then removes the
queried, what will you do?				form from memory.
Making Data Available on the Fly
Because data must be first made available prior				Next, the line oDT = CREATEOBJECT
to running a report, you will need to issue an SQL				(‘ActGLDayTot’) creates an instance of
SELECT statement or open a table at least in the				the class ActGLDayTot and stores a reference to
Init event of the Data Environment of the report.				it in the object variable oDT. The line lAnswer =
However, if your data does not exist in a form in				GetTransByDate… calls the method in the class
which you can easily build a view over it or a				to return the specific data we want, passing the
query over it and if it does not exist in a form in				necessary parameters collected form the user
which it can be added as a table then neither of				along with the array that will be used to store and
the methods discussed so far will yield the desired				transport the data back to our report.
results!
This means that you must find a new way of				As we did earlier, we then use a CREATE
making the data available to your program. A				CURSOR MgtTBalByDate command to create a
closer examination of how you would use the				cursor with the necessary fields. The line APPEND
SQL SELECT statement provides an insight into				FROM ARRAY arrTR populates the table. The
how you could do this. The general form of the				report is then made to recognize this cursor in its
syntax of the SQL SELECT Statement we want				data environment with the line THIS.OpenTables().
to consider is as follows:
				By separating the code that does the collection
				and processing of information from the
SELECT [ALL \| DISTINCT] [TOP nExpr				Report’s own Init event, we are able to
[PERCENT]] Select_List_Item [, ...]				make this code available always form multiple
				locations because it is contained in a data aware
FROM [FORCE] Table_List_Item [, ...]				class. The code contained within the
				GetTransByDate method of the ActGLDayTot
[WHERE JoinCondition \| FilterCondition [AND \|				class is as follows:
OR JoinCondition \| FilterCondition] ...]
				* Obtain Transaction By Date
[INTO StorageDestination \| TO				PARAMETERS
DisplayDestination]				gTitle
				LOCAL cMsg AS Character,intRows AS
				Integer,lGLMastInUse AS Logical
This form of the syntax indicates that the SQL				LOCAL lGLDayTotInUse as Logical
SELECT statement creates a query and then
fetches the required information from the table				* Initialize your variables to the correct types to
into a storage destination most usually, a cursor				avoid any errors
(temporary table), table or array. The cursor				STORE "" TO cMsg
representing storage destination is then used as				STORE 0 TO intRows
the source of data for the report. Based on this
actuality, we can deduce that our report would				IF TYPE('dTranDate') <> 'D'
run if we created a cursor object using the				cMsg = "You must enter transaction
CREATE CURSOR or a temporary table using				date!"
CREATE TABLE in the Init event of the data				MESSAGEBOX(cMsg,48,chrProgTitle)
environment. This would make data available to				RETURN .F.
the report and would thus prevent the report				ENDIF
from again closing immediately upon being opened.
How would this actually work in practice?				IF TYPE('intNoOfRows') <> "N"
1. Create your Report: First off, you				RETURN .F.
would have to create a report a report in the				ENDIF
Report Designer. You can do this by choosing the
Reports Node in the Project Manager and then				IF TYPE('arrTR',1) <> "A"
choosing the New button. When the New Report				cMsg = "Array of Transactions not
dialog box displays, you would then choose New				found!"
Report. Visual FoxPro would then create a new				MESSAGEBOX(cMsg,48,chrProgTitle)
blank report named Report1 and open it in the				RETURN .F.
Report Designer				ENDIF
2. Layout your Report: Now that the
report designer is open, you can start laying out				IF TYPE('cBranchCode') <> "C"
your report. Usually, you would do this by				cMsg = "ALL"
dragging fields from the data environment unto				ELSE
the report canvas. But in this case, you have no				cMsg = " FOR BranchCode = '" +
data in the data environment so you must set				cBranchCode + "'"
the properties of the report manually. You must				ENDIF
therefore display the Reports Controls Toolbar by
choosing Reports Control Toolbar from the View				IF USED('ActGLMast')
main menu. Once the toolbar displays, you can				lGLMastInUse = .T.
layout your report surface by performing the				ELSE
following action:a. Select a control from				USE ActGLMast IN 0
the toolbox to be placed on your report. If you				lGLMastInUse = .F.
select a control such as a text box control and				ENDIF
then click on the detail band of the report, Visual				SELECT ActGLMast
FoxPro automatically opens the Field Properties				GO TOP
dialog box so you can set not only the source of
data for the field but also other characteristics of				IF USED('ActGLDayTot')
the field.b. In the Expression box on the				lGLDayTotInUse = .T.
General tabbed page, enter the expression that				ELSE
will server as a source of data for the report.				USE ActGLDayTot IN 0
You will enter it in the format				lGLDayTotInUse = .F.
cursorname.fieldname where Cursorname will				ENDIF
represent the name of the cursor that you will				SELECT ActGLMast
create (of course you have not created it yet				GO TOP
otherwise you would simply have chosen the …				SCAN &cMsg
button to allow you select it from the expression				intRows = intRows + 1
builder[i]). If you click a control such as a Label				DIMENSION arrTR(intRows,9)
control, place it anywhere on the report and then				arrTR(intRows,1) =
type where the Insertion point appears to add a				ActGLMast.AccountCode
descriptive label. For example, click a label control				arrTR(intRows,2) =
on the report and then add a descriptive label for				ActGLMast.AccountName
each text box in the Page header. You can add				arrTR(intRows,3) =
descriptions such as Field One and Field Two. You				ActGLMast.AccountType
can add a report title (e.g. Test Report) by clicking				DO CASE
on the Label control, and then clicking at the				CASE ActGLMast.CurrBal >
appropriate position on the Report Header. To set				0
styles and font sizes for your captions, right-click				arrTR(intRows,7) =
the appropriate caption, choose Properties and				ActGLMast.CurrBal
then choose the Style tabbed page. In the Style				arrTR(intRows,8) = 0
tabbed page, you can make the appropriate				CASE ActGLMast.CurrBal < 0
settings under the Font box or make any other				arrTR(intRows,7) = 0
settings as necessary. Remember to save your				arrTR(intRows,8) =
work as you go along.c. Now that you				ActGLMast.CurrBal
have gotten fields onto your report, you can				OTHERWISE
apply the usual formatting to the report such as				arrTR(intRows,7) =
calculated fields, lines, etc				0 && YTD Debit
3. Set Properties for the Data				arrTR(intRows,8) =
Environment: You will still want to prevent your				0 && YTD Credit
report from automatically shutting down when				ENDCASE
you try to run it so you must set properties for				SELECT ActGLDayTot
the data environment as follows:a.				GO TOP
Right-click your report and then choose the Data				*LOCATE FOR
Environment menu command from the shortcut				ALLTRIM(ActGLDayTot.AccountCode) =
menu that displays. The Data Environment –				ALLTRIM(cAccountCode) AND
Report Designer opens. It is empty, showing that				ActGLDayTot.ValueDate = dTranDate
no data sources have been added to it.b.				LOCATE FOR ActGLDayTot.ValueDate =
Right-Click inside the Data Environment and then				dTranDate
choose the Properties menu command to display				IF FOUND()
the properties sheet for the Data				DO CASE
Environment.c. Set the AutoOpenTables				CASE
property to false. When you set this property to				ActGLDayTot.Balance > 0
false, the report does not attempt to open the
tables or views in the data environment upon				arrTR(intRows,5) = ActGLDayTot.Balance
running of the report. If this were to happen, the
report would simply find that the data				arrTR(intRows,6) = 0
environment did not contain any data and then				CASE
the report would close abruptly. You are now				ActGLDayTot.Balance < 0
ready to add code for the report as described in
the next step (step 4 – Writing code to make				arrTR(intRows,5) = 0
data available).
4. Write Code to make Data Available:				arrTR(intRows,6) = ActGLDayTot.Balance
Remember that the data is to be fetched or
made available only at run time. So you must now				OTHERWISE
write the code that will make this data available
for your report. You will do this in the Init event				arrTR(intRows,5) = 0
of the data environment by writing code such
as:a. Locate the Init event of the data				arrTR(intRows,6) = 0
environment in the properties sheet and then				ENDCASE
double-click it to open the code window.b.				ELSE
In the code window, type the code that creates				arrTR(intRows,5) = 0
your cursor by using the CREATE CURSOR				&& MTD Debit
command and also write code to add several				arrTR(intRows,6) = 0
records to the cursor by using the APPEND				&& MTD Credit
command. This code could look as follows:				ENDIF
				ENDSCANintNoOfRows = intRows
CREATE CURSOR Testcursor (Field1 c(10)				* Now close all tables you dont need
unique,Field2 i)				IF NOT lGLMastInUse
APPEND BLANK				USE IN ActGLMast
REPLACE TestCursor.Field1 WITH "TEST"				ENDIF
REPLACE TestCursor.Field2 WITH 50				IF NOT lGLDayTotInUse
APPEND BLANK				USE IN ActGLDayTot
REPLACE TestCursor.Field1 WITH "TEST2"				ENDIF
REPLACE TestCursor.Field2 WITH 100				RETURN .T.
APPEND BLANK
REPLACE TestCursor.Field1 WITH "TEST3"				The PARAMETERS command that begins the
REPLACE TestCursor.Field2 WITH 150				method identifies the parameters passed to the
				class. The program uses a SCAN…ENDSCAN
c. Close the code window and close				loop to go through the ActGLMast table to isolate
the data environment designerd. Quickly				all accounts that meet the criteria and then to
test your report by choosing the Print Preview				populate the array with matching data from the
button on the toolbar. The report should open in				ActGlDayTot table. At the end, the program
Print Preview mode.				returns .T. if the method completes successfully.
5. Save and Run your Report: Now that				You could now run this report with a REPORT
you have created your report and generated				FORM command just as we did either from a
data at run time, you will need to save the report				form or visual FoxPro menu.
and then to run it. You can test whether your
report will run by performing the following				Conclusion
action:a. Choose the Save button on the
toolbar to ensure that you have saved your				Visual FoxPro’s data manipulation language is
report.b. Close the report by choosing the				one of the things that makes Visual FoxPro
Close button. The report appears under the				standout amongst numerous products in its class.
Reports node in the Project Manager.c.				This article has demonstrated how the richness of
To run this report, select the report (Report1) in				the Visual FoxPro language and development
the project manager and then choose the				environment allows a developer to compile the
Preview button. The report opens in print preview				data needed for a report at run time and still be
mode.				able to pretty much control the report generation
6. Running Your Report Through the				process. Even though this article has assumed
User Interface: You would typically make your				that the programmer is building a ‘pure
report available to the users of your application				fox’ application (after all, Visual FoxPro gives
through the application user interface. For				you pretty much everything you need to build
example, you may provide a form through which				complete robust data management applications),
users can select your report and then press				with a little tuning and changes, you can use this
either a preview or a print button. To do this, you				method to derive data from powerful SQL
will have to use the REPORT FORM command to				Servers such as ORACLE, Microsoft’s own
run the report programmatically. If you want your				SQL Server or Advantage Database Server for
report to be available through the application user				use within a Visual FoxPro report. If you can
interface, you will have to perform the following				imagine it, Visual FoxPro lets you build it.
action:a. Select the Forms node on the
Documents tabbed page of the Visual FoxPro
project manager and then choose the New				[i] If you want to be able to use the expression
button. The New Form dialog box				building to select from a list of fields using a field
displays.b. Choose the New Form button.				picker, then you may have to type a command in
Visual FoxPro creates a new form named Form1				the command window that creates your cursor in
and opens it in the Form Designer.c.				advance before proceeding to layout the report
Display the Forms Control Toolbox if it is not				designer. If you do this, the fields of the cursor
already displayed by choosing the Form Controls				will be available to you from the expression
Toolbar menu on the view menu.d. Select				building. For example, you could type the following
the Command Button control on the Forms				in the command window:
Control Toolbox and then click on the form. Visual				CREATE CURSOR Testcursor (Field1 c(10)
FoxPro will create a new command button called				unique,Field2 i)
Command1.e. Double-Click Command1				Once you have done this, you will find the fields
button to display the Code window for its Click				of your cursor listed in the fields list on the
event.f. You can now enter the				expression builder, so you can just click the
following code:				desired field to be added to the report.

TeleClean

Populating Visual FoxPro Reports with data on the Fly

Articles