Populating Visual FoxPro Reviews with info on the Fly


Visual FoxPro’s report author is a powerful full-showcased report author that offers all the tools you want to make and ship compelling reports with your application. On Most events, you can make Visual FoxPro reports based only on info that presently exists (e.g. an current table, query or watch). Even so, think about that the info you want to populate your report with does not exist as a table that can be quickly additional to your report’s info surroundings nor does it exist as a set of tables upon which you can execute a straight-forward SQL Query or watch! This write-up describes how you can populate a Visual FoxPro report at operate time by amassing report conditions from consumers, deriving the info from your tables based on that conditions, producing a cursor and then populating the cursor with the derived info all at operate time.

Who should really go through this write-up?

This write-up assumes some familiarity with building and building programs with Microsoft Visual FoxPro nine


One of the vital gains of making use of Visual FoxPro as your main enhancement resource is that it offers all of the tools you want to create powerful, compelling full-showcased programs that are quick and attribute prosperous!

One of the attributes of Visual FoxPro that will make this probable is the newly revamped report author that ships with Visual FoxPro nine! Simply because of the want to give or make info obtainable for your report or label in advance of you print it, the Report Writer offers a wide variety of strategies to make info simply obtainable for your report.

One strategy of producing info obtainable for your report and incidentally the most normally utilised is to produce a report that is constantly based on the identical info resources. This strategy would basically demand you to incorporate tables or views to the info surroundings of a report or to use a DO command in the Init function of the report’s info surroundings. Alternatively, you could execute a query by producing an executing an SQL Assertion in the Init function of the report’s info surroundings.

A next strategy is when you want to make a report that utilizes independent sets of info resources for the report. In this situation, you would dynamically open up individuals info resources at operate time by making use of a USE

, USE , DO or SQL Find statement to the simply click function of a button or other code that will operate in advance of you issue a REPORT or LABEL command.

Even so, what transpires when the info for your report is not in a table that can be dynamically queried with a Find SQL statement nor is it in a variety that you can simply open up with a USE command! The Richness of the Visual FoxPro programming language will allow you to simply remedy this issue as this write-up will display. To display the concept staying reviewed, think about that you want to print or generate the Profits and Expense Report for your firm as at a given day! This would indicate that you want to keep a daily operating balance of just about every account in your Normal Ledger chart of accounts in a table that we shall maybe call ActGLDayTot. The sections that abide by will describe how to make these tables and their framework as perfectly as the code that generates the report.

Printing Reviews in Visual FoxPro

As presently observed, you want to make info obtainable for a report in advance of you progress to print the report. To print a report in Visual FoxPro, you will want to issue the REPORT Sort command. For the full syntax of the REPORT Sort command, see your Visual FoxPro nine on-line aid documentation.

If you issue the REPORT Sort command with no tables in the info surroundings and no info resources open up in the info surroundings of the report, the report simply seems to flash and then shut again! To stay clear of this problem, you will both want to incorporate info to the info surroundings or use the Init function of the info surroundings to make info obtainable in just the info surroundings prior to printing. When the info does not presently exist in a query or in a variety that can be directly queried, what will you do?

Creating Details Obtainable on the Fly

Simply because info have to be first created obtainable prior to operating a report, you will want to issue an SQL Find statement or open up a table at minimum in the Init function of the Details Surroundings of the report. Even so, if your info does not exist in a variety in which you can simply create a watch about it or a query about it and if it does not exist in a variety in which it can be additional as a table then neither of the procedures reviewed so far will yield the sought after benefits!

This usually means that you have to locate a new way of producing the info obtainable to your plan. A nearer evaluation of how you would use the SQL Find statement offers an perception into how you could do this. The common variety of the syntax of the SQL Find Assertion we want to take into consideration is as follows:


Find [ALL | Unique] [Major nExpr [P.c]] Find_Record_Item [, …]

   FROM [Power] Desk_Record_Item [, …]

   [Where by JoinCondition | FilterCondition [AND | OR JoinCondition | FilterCondition] …]

   [INTO StorageDestination | TO DisplayDestination]

This variety of the syntax suggests that the SQL Find statement produces a query and then fetches the essential details from the table into a storage desired destination most ordinarily, a cursor (temporary table), table or array. The cursor symbolizing storage desired destination is then utilised as the source of info for the report. Based on this actuality, we can deduce that our report would operate if we produced a cursor item making use of the Generate CURSOR or a temporary table making use of Generate Desk in the Init function of the info surroundings. This would make info obtainable to the report and would so avert the report from again closing quickly upon staying opened.

How would this basically perform in exercise?

one.       Generate your Report: 1st off, you would have to make a report a report in the Report Designer. You can do this by picking out the Reviews Node in the Undertaking Supervisor and then picking out the New button. When the New Report dialog box shows, you would then decide on New Report. Visual FoxPro would then make a new blank report named Report1 and open up it in the Report Designer

2.       Layout your Report: Now that the report designer is open up, you can start laying out your report. Generally, you would do this by dragging fields from the info surroundings unto the report canvas. But in this situation, you have no info in the info surroundings so you have to set the attributes of the report manually. You have to therefore display screen the Reviews Controls Toolbar by picking out Reviews Handle Toolbar from the Watch primary menu. The moment the toolbar shows, you can layout your report area by performing the pursuing action:

a.      Find a handle from the toolbox to be positioned on your report. If you pick a handle these types of as a textual content box handle and then simply click on the detail band of the report, Visual FoxPro mechanically opens the Area Attributes dialog box so you can set not only the source of info for the industry but also other traits of the industry.

b.      In the Expression box on the Normal tabbed page, enter the expression that will server as a source of info for the report. You will enter it in the structure cursorname.fieldname where Cursorname will signify the title of the cursor that you will make (of program you have not produced it still usually you would simply have picked out the … button to allow for you pick it from the expression builder[i]). If you simply click a handle these types of as a Label handle, put it everywhere on the report and then type where the Insertion level seems to incorporate a descriptive label. For instance, simply click a label handle on the report and then incorporate a descriptive label for just about every textual content box in the Site header. You can incorporate descriptions these types of as Area One and Area Two. You can incorporate a report title (e.g. Test Report) by clicking on the Label handle, and then clicking at the correct posture on the Report Header. To set kinds and font dimensions for your captions, ideal-simply click the correct caption, decide on Attributes and then decide on the Style tabbed page. In the Style tabbed page, you can make the correct options beneath the Font box or make any other options as vital. Don’t forget to preserve your perform as you go alongside.

c.       Now that you have gotten fields on to your report, you can use the common formatting to the report these types of as calculated fields, traces, and so on

3.       Set Attributes for the Details Surroundings: You will even now want to avert your report from mechanically shutting down when you consider to operate it so you have to set attributes for the info surroundings as follows:

a.      Appropriate-simply click your report and then decide on the Details Surroundings menu command from the shortcut menu that shows. The Details Surroundings – Report Designer opens. It is vacant, showing that no info resources have been additional to it.

b.      Appropriate-Simply click within the Details Surroundings and then decide on the Attributes menu command to display screen the attributes sheet for the Details Surroundings.

c.       Set the AutoOpenTables home to phony. When you set this home to phony, the report does not attempt to open up the tables or views in the info surroundings upon operating of the report. If this were to come about, the report would simply locate that the info surroundings did not include any info and then the report would shut abruptly. You are now ready to incorporate code for the report as described in the following stage (stage 4 – Producing code to make info obtainable).

4.       Generate Code to make Details Obtainable: Don’t forget that the info is to be fetched or created obtainable only at operate time. So you have to now produce the code that will make this info obtainable for your report. You will do this in the Init function of the info surroundings by creating code these types of as:

a.      Identify the Init function of the info surroundings in the attributes sheet and then double-simply click it to open up the code window.

b.      In the code window, type the code that produces your cursor by making use of the Generate CURSOR command and also produce code to incorporate quite a few data to the cursor by making use of the APPEND command. This code could search as follows:

Generate CURSOR Testcursor (Field1 c(10) special,Field2 i)


Exchange TestCursor.Field1 WITH “Test”

Exchange TestCursor.Field2 WITH 50


Exchange TestCursor.Field1 WITH “TEST2”

Exchange TestCursor.Field2 WITH one hundred


Exchange TestCursor.Field1 WITH “TEST3”

Exchange TestCursor.Field2 WITH 150


c.       Shut the code window and shut the info surroundings designer

d.      Quickly examination your report by picking out the Print Preview button on the toolbar. The report should really open up in Print Preview manner.

five.       Conserve and Run your Report: Now that you have produced your report and created info at operate time, you will want to preserve the report and then to operate it. You can examination no matter if your report will operate by performing the pursuing action:

a.      Pick out the Conserve button on the toolbar to be certain that you have saved your report.

b.      Shut the report by picking out the Shut button. The report seems beneath the Reviews node in the Undertaking Supervisor.

c.       To operate this report, pick the report (Report1) in the challenge supervisor and then decide on the Preview button. The report opens in print preview manner.

six.       Jogging Your Report By way of the User Interface: You would generally make your report obtainable to the consumers of your application via the application consumer interface. For instance, you may possibly give a variety via which consumers can pick your report and then push both a preview or a print button. To do this, you will have to use the REPORT Sort command to operate the report programmatically. If you want your report to be obtainable via the application consumer interface, you will have to perform the pursuing action:

a.      Find the Kinds node on the Files tabbed page of the Visual FoxPro challenge supervisor and then decide on the New button. The New Sort dialog box shows.

b.      Pick out the New Sort button. Visual FoxPro produces a new variety named Form1 and opens it in the Sort Designer.

c.       Display the Kinds Handle Toolbox if it is not presently exhibited by picking out the Sort Controls Toolbar menu on the watch menu.

d.      Find the Command Button handle on the Kinds Handle Toolbox and then simply click on the variety. Visual FoxPro will make a new command button named Command1.

e.       Double-Simply click Command1 button to display screen the Code window for its Simply click function.

f.        You can now enter the pursuing code:



g.      Simply click the Conserve button on the button bar to preserve the variety you have produced.

h.      Run the variety by both pressing Ctrl + E or by picking out the Run toolbar button on the toolbar. The variety runs.

i.         Now simply click the command button to operate the report. The report seems in print preview window.

Making use of the concept to a True Existence Scenario

The instance presently illustrated has been stored deliberately easy. Now, this have to be applied in a actual-everyday living problem. In the instance shown, the cursor is populated by issuing APPEND BLANK instructions. In a actual-everyday living application on the other hand, it may possibly be probable that your info may possibly presently exist, needing only to be go through again and re-arranged into a structure that your report can print. Lets illustrate this by building a Trial Stability Report as at a given day (Trial balances are ordinarily printed as at a given interval finish date).

To be in a position to print these types of a path balance, we would have to know the balance of an account as at a given day. Lets now accept that these types of info is saved day-by-day as transactions are handed in a table named ActGLDayTot and this table could be produced with the pursuing SQL Assertion:

Generate Desk ActGLDayTot(BatchNo c(20) Primary Crucial,AccountCode c(fifteen),

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

CreditAmt Y,Stability Y)

For just about every account in the Normal Ledger Master file, we want to obtain the account’s balances as at the specified day so that we will be in a position to create the demo balance. Now think about that your Normal Ledger Master table could be produced with the pursuing SQL Assertion:

Generate Desk ActGLMast(AccountCode c(fifteen) Primary Crucial,AccountName c(50),

AccountType c(forty),CurrBal Y)

The pursuing code is positioned in the Init Occasion of the info surroundings of our report:

* This Code Constructs the cursor utilised in the Report

Neighborhood dValueDate AS Day,cBranchCode aS Character,intNoOfRows as Integer,oDT as Item ,lAnswer as Sensible

DIMENSION arrTR(one,nine)

Keep “” TO cBranchCode

Keep (  /  /    ) TO dValueDate

Keep TO intNoOfRows

* one) Open up the variety and obtain the parameters

DO Sort frmMgtTBalByDate.Scx Joined

dValueDate = frmMgtTBalByDate.txtValueDate.Benefit

cBranchCode = frmMgtTBalByDate.txtBranchCode.Benefit




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

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

Find MgtTBalByDate



In the code above memory variables are declared that will be utilised both to hold the user’s report conditions or to keep and transportation info. The DIMENSION command produces an array that will be utilised to return info from a Details Informed Course that encapsulates the features of the ActGLDayTot table. One more matter to observe is how we have authorized consumers to specify the info to be provided in the report by moving into the specified date for which they want a demo balance. To acquire this details from the consumers, the line DO Sort frmMgtTBalByDate.Scx runs the report parameter variety so consumers can enter conditions. When  the consumer clicks the Okay button in that variety, a THISFORM.Disguise command briefly hides the variety from the consumer whilst the traces quickly pursuing the DO Sort command acquire the details on the variety. The line frmMgtTBalByDate.Release then removes the variety from memory.

Upcoming, the line oDT = CREATEOBJECT (‘ActGLDayTot’) produces an occasion of the class ActGLDayTot and stores a reference to it in the item variable oDT. The line lAnswer = GetTransByDate… calls the strategy in the class to return the certain info we want, passing the vital parameters collected variety the consumer alongside with the array that will be utilised to keep and transportation the info again to our report.

As we did previously, we then use a Generate CURSOR MgtTBalByDate command to make a cursor with the vital fields. The line APPEND FROM ARRAY arrTR populates the table. The report is then created to acknowledge this cursor in its info surroundings with the line THIS.OpenTables().

By separating the code that does the assortment and processing of details from the Report’s very own Init function, we are in a position to make this code obtainable constantly variety many places due to the fact it is contained in a info informed class. The code contained in just the GetTransByDate strategy of the ActGLDayTot class is as follows:

* Obtain Transaction By Day

PARAMETERS dTranDate,cBranchCode,arrTR,intNoOfRows,chrProgTitle

Neighborhood cMsg AS Character,intRows AS Integer,lGLMastInUse AS Sensible

Neighborhood lGLDayTotInUse as Sensible

* Initialize your variables to the right styles to stay clear of any faults

Keep “” TO cMsg

Keep TO intRows

IF Style(‘dTranDate’) <> ‘D’

      cMsg = “You have to enter transaction date!”

      MESSAGEBOX(cMsg,forty eight,chrProgTitle)

      RETURN .F.


IF Style(‘intNoOfRows’) <> “N”

      RETURN .F.


IF Style(‘arrTR’,one) <> “A”

      cMsg = “Array of Transactions not uncovered!”

      MESSAGEBOX(cMsg,forty eight,chrProgTitle)

      RETURN .F.


IF Style(‘cBranchCode’) <> “C”

      cMsg = “ALL”


      cMsg = ” FOR BranchCode = ‘” + cBranchCode + “‘”


IF Utilised(‘ActGLMast’)

      lGLMastInUse = .T.


      USE ActGLMast IN

      lGLMastInUse = .F.


Find ActGLMast

GO Major

IF Utilised(‘ActGLDayTot’)

      lGLDayTotInUse  = .T.


      USE ActGLDayTot IN

      lGLDayTotInUse = .F.


Find ActGLMast

GO Major

SCAN &cMsg

      intRows = intRows + one

      DIMENSION arrTR(intRows,nine)   

      arrTR(intRows,one) = ActGLMast.AccountCode

      arrTR(intRows,2) = ActGLMast.AccountName

      arrTR(intRows,3) = ActGLMast.AccountType

      DO Case

            Case ActGLMast.CurrBal >

                  arrTR(intRows,7) = ActGLMast.CurrBal

                  arrTR(intRows,8) =

            Case ActGLMast.CurrBal < 0

                  arrTR(intRows,7) =

                  arrTR(intRows,8) = ActGLMast.CurrBal

            Or else

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

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


      Find ActGLDayTot

      GO Major

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

      Identify FOR ActGLDayTot.ValueDate = dTranDate

      IF Discovered()

                  DO Case

                        Case ActGLDayTot.Stability >

                              arrTR(intRows,five) = ActGLDayTot.Stability

                              arrTR(intRows,six) =

                        Case ActGLDayTot.Stability < 0

                              arrTR(intRows,five) =

                              arrTR(intRows,six) = ActGLDayTot.Stability

                        Or else

                              arrTR(intRows,five) =

                              arrTR(intRows,six) =



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

            arrTR(intRows,six) = 0    && MTD Credit score



intNoOfRows = intRows

* Now shut all tables you dont want


      USE IN ActGLMast



      USE IN ActGLDayTot



The PARAMETERS command that commences the strategy identifies the parameters handed to the class. The plan works by using a SCAN…ENDSCAN loop to go via the ActGLMast table to isolate all accounts that meet up with the conditions and then to populate the array with matching info from the ActGlDayTot table. At the finish, the plan returns .T. if the strategy completes efficiently. You could now operate this report with a REPORT Sort command just as we did both from a variety or visual FoxPro menu.


Visual FoxPro’s info manipulation language is 1 of the points that will make Visual FoxPro standout among several products in its class. This write-up has shown how the richness of the Visual FoxPro language and enhancement surroundings will allow a developer to compile the info wanted for a report at operate time and even now be in a position to fairly a lot handle the report era course of action. Even nevertheless this write-up has assumed that the programmer is building a ‘pure fox’ application (after all, Visual FoxPro provides you fairly a lot every thing you want to create comprehensive strong info administration programs), with a very little tuning and alterations, you can use this strategy to derive info from powerful SQL Servers these types of as ORACLE, Microsoft’s very own SQL Server or Benefit Databases Server for use in just a Visual FoxPro report. If you can think about it, Visual FoxPro lets you create it.

[i] If you want to be in a position to use the expression building to pick from a record of fields making use of a industry picker, then you may possibly have to type a command in the command window that produces your cursor in progress in advance of proceeding to layout the report designer. If you do this, the fields of the cursor will be obtainable to you from the expression building. For instance, you could type the pursuing in the command window:

Generate CURSOR Testcursor (Field1 c(10) special,Field2 i)

The moment you have accomplished this, you will locate the fields of your cursor shown in the fields record on the expression builder, so you can just simply click the sought after industry to be additional to the report.

April 2024