Populating Visual FoxPro Reports with data on the Fly

Abstract:

Visual FoxPro’s report writer is a powerful full-featured report writer that provides all the tools you need to create and ship compelling reports with your application. On Most occasions, you can create Visual FoxPro reports based only on data that already exists (e.g. an existing table, query or view). However, imagine that the data you need to populate your report with does not exist as a table that can be readily added to your report’s data environment nor does it exist as a set of tables upon which you can execute a straight-forward SQL Query or view! This article describes how you can populate a Visual FoxPro report at run time by collecting report criteria from users, deriving the data from your tables based on that criteria, creating a cursor and then populating the cursor with the derived data all at run time.

Who should read this article?

This article assumes some familiarity with building and developing applications with Microsoft Visual FoxPro 9

Introduction:

One of the key benefits of using Visual FoxPro as your primary development tool is that it provides all of the tools you need to build powerful, compelling full-featured applications that are fast and feature rich!

One of the features of Visual FoxPro that makes this possible is the newly revamped report writer that ships with Visual FoxPro 9! Because of the need to provide or make data available for your report or label before you print it, the Report Writer provides a variety of ways to make data easily available for your report.

One method of making data available for your report and incidentally the most often used is to write a report that is always based on the same data sources. This method would actually require you to add tables or views to the data environment of a report or to use a DO <query> command in the Init event of the report’s data environment. Alternatively, you could execute a query by creating an executing an SQL Statement in the Init event of the report’s data environment.

A second method is when you need to create a report that utilizes separate sets of data sources for the report. In this case, you would dynamically open those data sources at run time by using a USE <table>, USE <view>, DO <query> or SQL SELECT statement to the click event of a button or other code that will run before you issue a REPORT or LABEL command.

However, what happens when the data for your report is not in a table that can be dynamically queried with a SELECT SQL statement nor is it in a form that you can simply open with a USE command! The Richness of the Visual FoxPro programming language allows you to easily solve this problem as this article will demonstrate. To demonstrate the concept being discussed, imagine that you need to print or generate the Income and Expense Report for your company as at a given day! This would mean that you need to keep a daily running balance of each account in your General Ledger chart of accounts in a table that we shall perhaps call ActGLDayTot. The sections that follow will describe how to create these tables and their structure as well as the code that generates the report.

Printing Reports in Visual FoxPro

As already noted, you need to make data available for a report before you proceed to print the report. To print a report in Visual FoxPro, you will need to issue the REPORT FORM <reportname> command. For the full syntax of the REPORT FORM command, see your Visual FoxPro 9 online help documentation.

If you issue the REPORT FORM command with no tables in the data environment and no data sources open in the data environment of the report, the report simply appears to flash and then close again! To avoid this situation, you will either need to add data to the data environment or use the Init event of the data environment to make data available within the data environment prior to printing. When the data does not already exist in a query or in a form that can be directly queried, what will you do?

Making Data Available on the Fly

Because data must be first made available prior to running a report, you will need to issue an SQL SELECT statement or open a table at least in the Init event of the Data Environment of the report. However, if your data does not exist in a form in which you can easily build a view over it or a query over it and if it does not exist in a form in which it can be added as a table then neither of the methods discussed so far will yield the desired results!

This means that you must find a new way of making the data available to your program. A closer examination of how you would use the SQL SELECT statement provides an insight into how you could do this. The general form of the syntax of the SQL SELECT Statement we want to consider is as follows:

SELECT [ALL | DISTINCT] [TOP nExpr [PERCENT]] Select_List_Item [, ...]

   FROM [FORCE] Table_List_Item [, ...]

   [WHERE JoinCondition | FilterCondition [AND | OR JoinCondition | FilterCondition] ...]

   [INTO StorageDestination | TO DisplayDestination]

This form of the syntax indicates that the SQL SELECT statement creates a query and then fetches the required information from the table into a storage destination most usually, a cursor (temporary table), table or array. The cursor representing storage destination is then used as the source of data for the report. Based on this actuality, we can deduce that our report would run if we created a cursor object using the CREATE CURSOR or a temporary table using CREATE TABLE in the Init event of the data environment. This would make data available to the report and would thus prevent the report from again closing immediately upon being opened.

How would this actually work in practice?

1.       Create your Report: First off, you would have to create a report a report in the Report Designer. You can do this by choosing the Reports Node in the Project Manager and then choosing the New button. When the New Report dialog box displays, you would then choose New Report. Visual FoxPro would then create a new blank report named Report1 and open it in the Report Designer

2.       Layout your Report: Now that the report designer is open, you can start laying out your report. Usually, you would do this by dragging fields from the data environment unto the report canvas. But in this case, you have no data in the data environment so you must set the properties of the report manually. You must therefore display the Reports Controls Toolbar by choosing Reports Control Toolbar from the View main menu. Once the toolbar displays, you can layout your report surface by performing the following action:

a.      Select a control from the toolbox to be placed on your report. If you select a control such as a text box control and then click on the detail band of the report, Visual FoxPro automatically opens the Field Properties dialog box so you can set not only the source of data for the field but also other characteristics of the field.

b.      In the Expression box on the General tabbed page, enter the expression that will server as a source of data for the report. You will enter it in the format cursorname.fieldname where Cursorname will represent the name of the cursor that you will create (of course you have not created it yet otherwise you would simply have chosen the … button to allow you select it from the expression builder[i]). If you click a control such as a Label control, place it anywhere on the report and then type where the Insertion point appears to add a descriptive label. For example, click a label control on the report and then add a descriptive label for each text box in the Page header. You can add descriptions such as Field One and Field Two. You can add a report title (e.g. Test Report) by clicking on the Label control, and then clicking at the appropriate position on the Report Header. To set styles and font sizes for your captions, right-click the appropriate caption, choose Properties and then choose the Style tabbed page. In the Style tabbed page, you can make the appropriate settings under the Font box or make any other settings as necessary. Remember to save your work as you go along.

c.       Now that you have gotten fields onto your report, you can apply the usual formatting to the report such as calculated fields, lines, etc

3.       Set Properties for the Data Environment: You will still want to prevent your report from automatically shutting down when you try to run it so you must set properties for the data environment as follows:

a.      Right-click your report and then choose the Data Environment menu command from the shortcut menu that displays. The Data Environment – Report Designer opens. It is empty, showing that no data sources have been added to it.

b.      Right-Click inside the Data Environment and then choose the Properties menu command to display the properties sheet for the Data Environment.

c.       Set the AutoOpenTables property to false. When you set this property to false, the report does not attempt to open the tables or views in the data environment upon running of the report. If this were to happen, the report would simply find that the data environment did not contain any data and then the report would close abruptly. You are now ready to add code for the report as described in the next step (step 4 – Writing code to make data available).

4.       Write Code to make Data Available: Remember that the data is to be fetched or made available only at run time. So you must now write the code that will make this data available for your report. You will do this in the Init event of the data environment by writing code such as:

a.      Locate the Init event of the data environment in the properties sheet and then double-click it to open the code window.

b.      In the code window, type the code that creates your cursor by using the CREATE CURSOR command and also write code to add several records to the cursor by using the APPEND command. This code could look as follows:

CREATE CURSOR Testcursor (Field1 c(10) unique,Field2 i)

APPEND BLANK

REPLACE TestCursor.Field1 WITH "TEST"

REPLACE TestCursor.Field2 WITH 50

APPEND BLANK

REPLACE TestCursor.Field1 WITH "TEST2"

REPLACE TestCursor.Field2 WITH 100

APPEND BLANK

REPLACE TestCursor.Field1 WITH "TEST3"

REPLACE TestCursor.Field2 WITH 150

c.       Close the code window and close the data environment designer

d.      Quickly test your report by choosing the Print Preview button on the toolbar. The report should open in Print Preview mode.

5.       Save and Run your Report: Now that you have created your report and generated data at run time, you will need to save the report and then to run it. You can test whether your report will run by performing the following action:

a.      Choose the Save button on the toolbar to ensure that you have saved your report.

b.      Close the report by choosing the Close button. The report appears under the Reports node in the Project Manager.

c.       To run this report, select the report (Report1) in the project manager and then choose the Preview button. The report opens in print preview mode.

6.       Running Your Report Through the User Interface: You would typically make your report available to the users of your application through the application user interface. For example, you may provide a form through which users can select your report and then press either a preview or a print button. To do this, you will have to use the REPORT FORM command to run the report programmatically. If you want your report to be available through the application user interface, you will have to perform the following action:

a.      Select the Forms node on the Documents tabbed page of the Visual FoxPro project manager and then choose the New button. The New Form dialog box displays.

b.      Choose the New Form button. Visual FoxPro creates a new form named Form1 and opens it in the Form Designer.

c.       Display the Forms Control Toolbox if it is not already displayed by choosing the Form Controls Toolbar menu on the view menu.

d.      Select the Command Button control on the Forms Control Toolbox and then click on the form. Visual FoxPro will create a new command button called Command1.

e.       Double-Click Command1 button to display the Code window for its Click event.

f.        You can now enter the following code:

REPORT FORM Report4.frx NOCONSOLE PREVIEW

g.      Click the Save button on the button bar to save the form you have created.

h.      Run the form by either pressing Ctrl + E or by choosing the Run toolbar button on the toolbar. The form runs.

i.         Now click the command button to run the report. The report appears in print preview window.

Applying the concept to a Real Life Situation

The example already illustrated has been kept deliberately simple. Now, this must be applied in a real-life situation. In the example shown, the cursor is populated by issuing APPEND BLANK commands. In a real-life application however, it may be possible that your data may already exist, needing only to be read back and re-arranged into a format that your report can print. Lets illustrate this by building a Trial Balance Report as at a given day (Trial balances are usually printed as at a given period end date).

To be able to print such a trail balance, we would have to know the balance of an account as at a given day. Lets now accept that such data is stored day-by-day as transactions are passed in a table called ActGLDayTot and this table could be created with the following SQL Statement:

CREATE TABLE ActGLDayTot(BatchNo c(20) PRIMARY KEY,AccountCode c(15),;

ValueDate D,CurrYear i,NextYear i,PeriodNumb i,MonthNumb i,Debitamt Y,;

CreditAmt Y,Balance Y)

For each account in the General Ledger Master file, we want to obtain the account’s balances as at the specified day so that we will be able to build the trial balance. Now imagine that your General Ledger Master table could be created with the following SQL Statement:

CREATE TABLE ActGLMast(AccountCode c(15) PRIMARY KEY,AccountName c(50),;

AccountType c(40),CurrBal Y)

The following code is placed in the Init Event of the data environment of our report:

* This Code Constructs the cursor used in the Report

LOCAL dValueDate AS Date,cBranchCode aS Character,intNoOfRows as Integer,oDT as Object ,lAnswer as Logical

DIMENSION arrTR(1,9)

STORE "" TO cBranchCode

STORE ({  /  /    }) TO dValueDate

STORE 0 TO intNoOfRows

* 1) Open the form and obtain the parameters

DO FORM frmMgtTBalByDate.Scx LINKED

dValueDate = frmMgtTBalByDate.txtValueDate.Value

cBranchCode = frmMgtTBalByDate.txtBranchCode.Value

frmMgtTBalbyDate.RELEASE

*OPENTABLES()

oDT = CREATEOBJECT('ActGLDayTot')

lAnswer  = oDT.GetTransByDate(dValueDate,cBranchCode,arrTR,intNoOfRows,chrProgTitle)

CREATE CURSOR MgtTBalByDate (AccountCode c(20),AccountName c(50),AccountType c(50),TBalDate D,MTDDebit Y,MTDCredit Y,YTDDebit Y,YTDCredit Y,UserName C(10))

SELECT MgtTBalByDate

APPEND FROM ARRAY arrTR

This.OpenTables()

In the code above memory variables are declared that will be used either to hold the user’s report criteria or to store and transport data. The DIMENSION command creates an array that will be used to return data from a Data Aware Class that encapsulates the functionality of the ActGLDayTot table. Another thing to note is how we have allowed users to specify the data to be included in the report by entering the specified date for which they want a trial balance. To collect this information from the users, the line DO FORM frmMgtTBalByDate.Scx runs the report parameter form so users can enter criteria. When  the user clicks the Ok button in that form, a THISFORM.Hide command temporarily hides the form from the user while the lines immediately following the DO FORM command collect the information on the form. The line frmMgtTBalByDate.RELEASE then removes the form from memory.

Next, the line oDT = CREATEOBJECT (‘ActGLDayTot’) creates an instance of the class ActGLDayTot and stores a reference to it in the object variable oDT. The line lAnswer = GetTransByDate… calls the method in the class to return the specific data we want, passing the necessary parameters collected form the user along with the array that will be used to store and transport the data back to our report.

As we did earlier, we then use a CREATE CURSOR MgtTBalByDate command to create a cursor with the necessary fields. The line APPEND FROM ARRAY arrTR populates the table. The report is then made to recognize this cursor in its data environment with the line THIS.OpenTables().

By separating the code that does the collection and processing of information from the Report’s own Init event, we are able to make this code available always form multiple locations because it is contained in a data aware class. The code contained within the GetTransByDate method of the ActGLDayTot class is as follows:

* Obtain Transaction By Date

PARAMETERS dTranDate,cBranchCode,arrTR,intNoOfRows,chrProgTitle

LOCAL cMsg AS Character,intRows AS Integer,lGLMastInUse AS Logical

LOCAL lGLDayTotInUse as Logical

* Initialize your variables to the correct types to avoid any errors

STORE "" TO cMsg

STORE 0 TO intRows

IF TYPE('dTranDate') <> 'D'

      cMsg = "You must enter transaction date!"

      MESSAGEBOX(cMsg,48,chrProgTitle)

      RETURN .F.

ENDIF

IF TYPE('intNoOfRows') <> "N"

      RETURN .F.

ENDIF

IF TYPE('arrTR',1) <> "A"

      cMsg = "Array of Transactions not found!"

      MESSAGEBOX(cMsg,48,chrProgTitle)

      RETURN .F.

ENDIF

IF TYPE('cBranchCode') <> "C"

      cMsg = "ALL"

ELSE

      cMsg = " FOR BranchCode = '" + cBranchCode + "'"

ENDIF

IF USED('ActGLMast')

      lGLMastInUse = .T.

ELSE

      USE ActGLMast IN 0

      lGLMastInUse = .F.

ENDIF

SELECT ActGLMast

GO TOP

IF USED('ActGLDayTot')

      lGLDayTotInUse  = .T.

ELSE

      USE ActGLDayTot IN 0

      lGLDayTotInUse = .F.

ENDIF

SELECT ActGLMast

GO TOP

SCAN &cMsg

      intRows = intRows + 1

      DIMENSION arrTR(intRows,9)   

      arrTR(intRows,1) = ActGLMast.AccountCode

      arrTR(intRows,2) = ActGLMast.AccountName

      arrTR(intRows,3) = ActGLMast.AccountType

      DO CASE

            CASE ActGLMast.CurrBal > 0

                  arrTR(intRows,7) = ActGLMast.CurrBal

                  arrTR(intRows,8) = 0

            CASE ActGLMast.CurrBal < 0

                  arrTR(intRows,7) = 0

                  arrTR(intRows,8) = ActGLMast.CurrBal

            OTHERWISE

                  arrTR(intRows,7) = 0  && YTD Debit

                  arrTR(intRows,8) = 0  && YTD Credit

      ENDCASE

      SELECT ActGLDayTot

      GO TOP

      *LOCATE FOR ALLTRIM(ActGLDayTot.AccountCode) = ALLTRIM(cAccountCode) AND ActGLDayTot.ValueDate = dTranDate

      LOCATE FOR ActGLDayTot.ValueDate = dTranDate

      IF FOUND()

                  DO CASE

                        CASE ActGLDayTot.Balance > 0

                              arrTR(intRows,5) = ActGLDayTot.Balance

                              arrTR(intRows,6) = 0

                        CASE ActGLDayTot.Balance < 0

                              arrTR(intRows,5) = 0

                              arrTR(intRows,6) = ActGLDayTot.Balance

                        OTHERWISE

                              arrTR(intRows,5) = 0

                              arrTR(intRows,6) = 0

                  ENDCASE

      ELSE

            arrTR(intRows,5) = 0    && MTD Debit

            arrTR(intRows,6) = 0    && MTD Credit

      ENDIF

ENDSCAN

intNoOfRows = intRows

* Now close all tables you dont need

IF NOT lGLMastInUse

      USE IN ActGLMast

ENDIF

IF NOT lGLDayTotInUse

      USE IN ActGLDayTot

ENDIF

RETURN .T.

The PARAMETERS command that begins the method identifies the parameters passed to the class. The program uses a SCAN…ENDSCAN loop to go through the ActGLMast table to isolate all accounts that meet the criteria and then to populate the array with matching data from the ActGlDayTot table. At the end, the program returns .T. if the method completes successfully. You could now run this report with a REPORT FORM command just as we did either from a form or visual FoxPro menu.

Conclusion

Visual FoxPro’s data manipulation language is one of the things that makes Visual FoxPro standout amongst numerous products in its class. This article has demonstrated how the richness of the Visual FoxPro language and development environment allows a developer to compile the data needed for a report at run time and still be able to pretty much control the report generation process. Even though this article has assumed that the programmer is building a ‘pure fox’ application (after all, Visual FoxPro gives you pretty much everything you need to build complete robust data management applications), with a little tuning and changes, you can use this method to derive data from powerful SQL Servers such as ORACLE, Microsoft’s own SQL Server or Advantage Database Server for use within a Visual FoxPro report. If you can imagine it, Visual FoxPro lets you build it.

[i] If you want to be able to use the expression building to select from a list of fields using a field picker, then you may have to type a command in the command window that creates your cursor in advance before proceeding to layout the report designer. If you do this, the fields of the cursor will be available to you from the expression building. For example, you could type the following in the command window:

CREATE CURSOR Testcursor (Field1 c(10) unique,Field2 i)

Once you have done this, you will find the fields of your cursor listed in the fields list on the expression builder, so you can just click the desired field to be added to the report.