Populating Visual FoxPro Stories with facts on the Fly


Visual FoxPro’s report author is a impressive complete-highlighted report author that supplies all the tools you have to have to produce and ship persuasive stories with your software. On Most instances, you can produce Visual FoxPro stories dependent only on facts that already exists (e.g. an current table, question or look at). Nevertheless, envision that the facts you have to have to populate your report with does not exist as a table that can be commonly extra to your report’s facts atmosphere nor does it exist as a set of tables on which you can execute a straight-forward SQL Query or look at! This report describes how you can populate a Visual FoxPro report at run time by amassing report standards from buyers, deriving the facts from your tables dependent on that standards, generating a cursor and then populating the cursor with the derived facts all at run time.

Who really should examine this report?

This report assumes some familiarity with constructing and producing apps with Microsoft Visual FoxPro 9


A person of the crucial positive aspects of working with Visual FoxPro as your main development software is that it supplies all of the tools you have to have to establish impressive, persuasive complete-highlighted apps that are fast and feature abundant!

A person of the functions of Visual FoxPro that will make this possible is the newly revamped report author that ships with Visual FoxPro 9! Because of the have to have to deliver or make facts readily available for your report or label in advance of you print it, the Report Author supplies a wide range of methods to make facts effortlessly readily available for your report.

A person method of generating facts readily available for your report and by the way the most often used is to publish a report that is constantly dependent on the exact facts sources. This method would essentially demand you to incorporate tables or views to the facts atmosphere of a report or to use a DO command in the Init celebration of the report’s facts atmosphere. Alternatively, you could execute a question by generating an executing an SQL Statement in the Init celebration of the report’s facts atmosphere.

A second method is when you have to have to produce a report that makes use of separate sets of facts sources for the report. In this situation, you would dynamically open up individuals facts sources at run time by working with a USE

, USE , DO or SQL Pick statement to the click celebration of a button or other code that will run in advance of you challenge a REPORT or LABEL command.

Nevertheless, what occurs when the facts for your report is not in a table that can be dynamically queried with a Pick SQL statement nor is it in a type that you can simply just open up with a USE command! The Richness of the Visual FoxPro programming language enables you to effortlessly solve this dilemma as this report will demonstrate. To demonstrate the idea currently being talked about, envision that you have to have to print or deliver the Revenue and Price Report for your organization as at a supplied day! This would mean that you have to have to keep a every day operating harmony of every single account in your Normal Ledger chart of accounts in a table that we shall probably phone ActGLDayTot. The sections that comply with will describe how to produce these tables and their framework as effectively as the code that generates the report.

Printing Stories in Visual FoxPro

As already mentioned, you have to have to make facts readily available for a report in advance of you progress to print the report. To print a report in Visual FoxPro, you will have to have to challenge the REPORT Sort command. For the complete syntax of the REPORT Sort command, see your Visual FoxPro 9 on line assistance documentation.

If you challenge the REPORT Sort command with no tables in the facts atmosphere and no facts sources open up in the facts atmosphere of the report, the report simply just appears to flash and then shut again! To keep away from this scenario, you will possibly have to have to incorporate facts to the facts atmosphere or use the Init celebration of the facts atmosphere to make facts readily available inside the facts atmosphere prior to printing. When the facts does not already exist in a question or in a type that can be instantly queried, what will you do?

Building Info Obtainable on the Fly

Because facts have to be first produced readily available prior to operating a report, you will have to have to challenge an SQL Pick statement or open up a table at minimum in the Init celebration of the Info Setting of the report. Nevertheless, if your facts does not exist in a type in which you can effortlessly establish a look at about it or a question about it and if it does not exist in a type in which it can be extra as a table then neither of the solutions talked about so considerably will produce the sought after effects!

This usually means that you have to discover a new way of generating the facts readily available to your program. A nearer assessment of how you would use the SQL Pick statement supplies an insight into how you could do this. The common type of the syntax of the SQL Pick Statement we want to consider is as follows:


Pick [ALL | Distinct] [Prime nExpr [P.c]] Pick_Checklist_Item [, …]

   FROM [Pressure] Table_Checklist_Item [, …]

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

   [INTO StorageDestination | TO DisplayDestination]

This type of the syntax suggests that the SQL Pick statement produces a question and then fetches the needed information and facts from the table into a storage spot most normally, a cursor (short term table), table or array. The cursor symbolizing storage spot is then used as the supply of facts for the report. Dependent on this actuality, we can deduce that our report would run if we created a cursor object working with the Produce CURSOR or a short term table working with Produce Table in the Init celebration of the facts atmosphere. This would make facts readily available to the report and would so stop the report from again closing promptly on currently being opened.

How would this essentially operate in observe?

one.       Produce your Report: First off, you would have to produce a report a report in the Report Designer. You can do this by deciding on the Stories Node in the Task Supervisor and then deciding on the New button. When the New Report dialog box displays, you would then select New Report. Visual FoxPro would then produce a new blank report named Report1 and open up it in the Report Designer

two.       Structure your Report: Now that the report designer is open up, you can commence laying out your report. Generally, you would do this by dragging fields from the facts atmosphere unto the report canvas. But in this situation, you have no facts in the facts atmosphere so you have to set the properties of the report manually. You have to as a result display the Stories Controls Toolbar by deciding on Stories Control Toolbar from the Watch main menu. At the time the toolbar displays, you can format your report floor by accomplishing the following action:

a.      Pick a control from the toolbox to be positioned on your report. If you pick a control these types of as a textual content box control and then click on the element band of the report, Visual FoxPro instantly opens the Area Attributes dialog box so you can set not only the supply of facts for the field but also other features of the field.

b.      In the Expression box on the Normal tabbed website page, enter the expression that will server as a supply of facts for the report. You will enter it in the format cursorname.fieldname exactly where Cursorname will signify the name of the cursor that you will produce (of system you have not created it yet otherwise you would simply just have decided on the … button to allow for you pick it from the expression builder[i]). If you click a control these types of as a Label control, location it everywhere on the report and then variety exactly where the Insertion place appears to incorporate a descriptive label. For example, click a label control on the report and then incorporate a descriptive label for every single textual content box in the Web page header. You can incorporate descriptions these types of as Area A person and Area Two. You can incorporate a report title (e.g. Take a look at Report) by clicking on the Label control, and then clicking at the ideal posture on the Report Header. To set kinds and font dimensions for your captions, suitable-click the ideal caption, select Attributes and then select the Model tabbed website page. In the Model tabbed website page, you can make the ideal configurations less than the Font box or make any other configurations as needed. Remember to save your operate as you go alongside.

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

3.       Set Attributes for the Info Setting: You will still want to stop your report from instantly shutting down when you try to run it so you have to set properties for the facts atmosphere as follows:

a.      Right-click your report and then select the Info Setting menu command from the shortcut menu that displays. The Info Setting – Report Designer opens. It is vacant, displaying that no facts sources have been extra to it.

b.      Right-Click inside of the Info Setting and then select the Attributes menu command to display the properties sheet for the Info Setting.

c.       Set the AutoOpenTables assets to untrue. When you set this assets to untrue, the report does not attempt to open up the tables or views in the facts atmosphere on operating of the report. If this had been to occur, the report would simply just discover that the facts atmosphere did not contain any facts and then the report would shut abruptly. You are now prepared to incorporate code for the report as explained in the next phase (phase 4 – Creating code to make facts readily available).

4.       Produce Code to make Info Obtainable: Remember that the facts is to be fetched or produced readily available only at run time. So you have to now publish the code that will make this facts readily available for your report. You will do this in the Init celebration of the facts atmosphere by creating code these types of as:

a.      Track down the Init celebration of the facts atmosphere in the properties sheet and then double-click it to open up the code window.

b.      In the code window, variety the code that produces your cursor by working with the Produce CURSOR command and also publish code to incorporate various information to the cursor by working with the APPEND command. This code could seem as follows:

Produce CURSOR Testcursor (Field1 c(ten) one of a kind,Field2 i)


Exchange TestCursor.Field1 WITH “Take a look at”

Exchange TestCursor.Field2 WITH 50


Exchange TestCursor.Field1 WITH “TEST2”

Exchange TestCursor.Field2 WITH a hundred


Exchange TestCursor.Field1 WITH “TEST3”

Exchange TestCursor.Field2 WITH 150


c.       Shut the code window and shut the facts atmosphere designer

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

5.       Help you save and Operate your Report: Now that you have created your report and generated facts at run time, you will have to have to save the report and then to run it. You can examination irrespective of whether your report will run by accomplishing the following action:

a.      Pick the Help you save button on the toolbar to ensure that you have saved your report.

b.      Shut the report by deciding on the Shut button. The report appears less than the Stories node in the Task Supervisor.

c.       To run this report, pick the report (Report1) in the undertaking supervisor and then select the Preview button. The report opens in print preview manner.

6.       Jogging Your Report Through the User Interface: You would commonly make your report readily available to the buyers of your software by way of the software user interface. For example, you may deliver a type by way of which buyers can pick your report and then press possibly a preview or a print button. To do this, you will have to use the REPORT Sort command to run the report programmatically. If you want your report to be readily available by way of the software user interface, you will have to accomplish the following action:

a.      Pick the Sorts node on the Paperwork tabbed website page of the Visual FoxPro undertaking supervisor and then select the New button. The New Sort dialog box displays.

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

c.       Display screen the Sorts Control Toolbox if it is not already exhibited by deciding on the Sort Controls Toolbar menu on the look at menu.

d.      Pick the Command Button control on the Sorts Control Toolbox and then click on the type. Visual FoxPro will produce a new command button identified as Command1.

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

f.        You can now enter the following code:



g.      Click the Help you save button on the button bar to save the type you have created.

h.      Operate the type by possibly pressing Ctrl + E or by deciding on the Operate toolbar button on the toolbar. The type runs.

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

Implementing the idea to a Real Existence Condition

The example already illustrated has been saved intentionally easy. Now, this have to be utilized in a actual-daily life scenario. In the example shown, the cursor is populated by issuing APPEND BLANK instructions. In a actual-daily life software even so, it may be possible that your facts may already exist, needing only to be examine again and re-organized into a format that your report can print. Lets illustrate this by constructing a Trial Stability Report as at a supplied day (Trial balances are normally printed as at a supplied period of time stop date).

To be capable to print these types of a path harmony, we would have to know the harmony of an account as at a supplied day. Lets now acknowledge that these types of facts is stored day-by-day as transactions are handed in a table identified as ActGLDayTot and this table could be created with the following SQL Statement:

Produce Table ActGLDayTot(BatchNo c(twenty) Main Critical,AccountCode c(fifteen),

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

CreditAmt Y,Stability Y)

For every single account in the Normal Ledger Master file, we want to get hold of the account’s balances as at the specified day so that we will be capable to establish the trial harmony. Now envision that your Normal Ledger Master table could be created with the following SQL Statement:

Produce Table ActGLMast(AccountCode c(fifteen) Main Critical,AccountName c(50),

AccountType c(40),CurrBal Y)

The following code is positioned in the Init Celebration of the facts atmosphere of our report:

* This Code Constructs the cursor used in the Report

Community dValueDate AS Day,cBranchCode aS Character,intNoOfRows as Integer,oDT as Item ,lAnswer as Rational

DIMENSION arrTR(one,9)

Retail store “” TO cBranchCode

Retail store (  /  /    ) TO dValueDate

Retail store TO intNoOfRows

* one) Open up the type and get hold of the parameters

DO Sort frmMgtTBalByDate.Scx Joined

dValueDate = frmMgtTBalByDate.txtValueDate.Benefit

cBranchCode = frmMgtTBalByDate.txtBranchCode.Benefit




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

Produce CURSOR MgtTBalByDate (AccountCode c(twenty),AccountName c(50),AccountType c(50),TBalDate D,MTDDebit Y,MTDCredit Y,YTDDebit Y,YTDCredit Y,UserName C(ten))

Pick MgtTBalByDate



In the code earlier mentioned memory variables are declared that will be used possibly to maintain the user’s report standards or to shop and transportation facts. The DIMENSION command produces an array that will be used to return facts from a Info Mindful Class that encapsulates the operation of the ActGLDayTot table. Yet another point to take note is how we have allowed buyers to specify the facts to be integrated in the report by getting into the specified date for which they want a trial harmony. To obtain this information and facts from the buyers, the line DO Sort frmMgtTBalByDate.Scx runs the report parameter type so buyers can enter standards. When  the user clicks the Alright button in that type, a THISFORM.Conceal command quickly hides the type from the user whilst the traces promptly following the DO Sort command obtain the information and facts on the type. The line frmMgtTBalByDate.Release then removes the type from memory.

Upcoming, the line oDT = CREATEOBJECT (‘ActGLDayTot’) produces an occasion of the course ActGLDayTot and stores a reference to it in the object variable oDT. The line lAnswer = GetTransByDate… calls the method in the course to return the specific facts we want, passing the needed parameters collected type the user alongside with the array that will be used to shop and transportation the facts again to our report.

As we did before, we then use a Produce CURSOR MgtTBalByDate command to produce a cursor with the needed fields. The line APPEND FROM ARRAY arrTR populates the table. The report is then produced to realize this cursor in its facts atmosphere with the line THIS.OpenTables().

By separating the code that does the collection and processing of information and facts from the Report’s individual Init celebration, we are capable to make this code readily available constantly type several locations for the reason that it is contained in a facts conscious course. The code contained inside the GetTransByDate method of the ActGLDayTot course is as follows:

* Obtain Transaction By Day

PARAMETERS dTranDate,cBranchCode,arrTR,intNoOfRows,chrProgTitle

Community cMsg AS Character,intRows AS Integer,lGLMastInUse AS Rational

Community lGLDayTotInUse as Rational

* Initialize your variables to the right types to keep away from any glitches

Retail store “” TO cMsg

Retail store TO intRows

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

      cMsg = “You have to enter transaction date!”


      RETURN .F.


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

      RETURN .F.


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

      cMsg = “Array of Transactions not discovered!”


      RETURN .F.


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

      cMsg = “ALL”


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


IF Used(‘ActGLMast’)

      lGLMastInUse = .T.


      USE ActGLMast IN

      lGLMastInUse = .F.


Pick ActGLMast

GO Prime

IF Used(‘ActGLDayTot’)

      lGLDayTotInUse  = .T.


      USE ActGLDayTot IN

      lGLDayTotInUse = .F.


Pick ActGLMast

GO Prime

SCAN &cMsg

      intRows = intRows + one

      DIMENSION arrTR(intRows,9)   

      arrTR(intRows,one) = ActGLMast.AccountCode

      arrTR(intRows,two) = ActGLMast.AccountName

      arrTR(intRows,3) = ActGLMast.AccountType

      DO Circumstance

            Circumstance ActGLMast.CurrBal >

                  arrTR(intRows,7) = ActGLMast.CurrBal

                  arrTR(intRows,8) =

            Circumstance ActGLMast.CurrBal < 0

                  arrTR(intRows,7) =

                  arrTR(intRows,8) = ActGLMast.CurrBal

            If not

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

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


      Pick ActGLDayTot

      GO Prime

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

      Track down FOR ActGLDayTot.ValueDate = dTranDate

      IF Located()

                  DO Circumstance

                        Circumstance ActGLDayTot.Stability >

                              arrTR(intRows,5) = ActGLDayTot.Stability

                              arrTR(intRows,6) =

                        Circumstance ActGLDayTot.Stability < 0

                              arrTR(intRows,5) =

                              arrTR(intRows,6) = ActGLDayTot.Stability

                        If not

                              arrTR(intRows,5) =

                              arrTR(intRows,6) =



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

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



intNoOfRows = intRows

* Now shut all tables you dont have to have


      USE IN ActGLMast



      USE IN ActGLDayTot



The PARAMETERS command that commences the method identifies the parameters handed to the course. The program makes use of a SCAN…ENDSCAN loop to go by way of the ActGLMast table to isolate all accounts that meet up with the standards and then to populate the array with matching facts from the ActGlDayTot table. At the stop, the program returns .T. if the method completes properly. You could now run this report with a REPORT Sort command just as we did possibly from a type or visual FoxPro menu.


Visual FoxPro’s facts manipulation language is one of the issues that will make Visual FoxPro standout amongst several merchandise in its course. This report has shown how the richness of the Visual FoxPro language and development atmosphere enables a developer to compile the facts needed for a report at run time and still be capable to fairly considerably control the report technology procedure. Even though this report has assumed that the programmer is constructing a ‘pure fox’ software (soon after all, Visual FoxPro provides you fairly considerably every little thing you have to have to establish comprehensive sturdy facts management apps), with a very little tuning and changes, you can use this method to derive facts from impressive SQL Servers these types of as ORACLE, Microsoft’s individual SQL Server or Gain Databases Server for use inside a Visual FoxPro report. If you can envision it, Visual FoxPro lets you establish it.

[i] If you want to be capable to use the expression constructing to pick from a record of fields working with a field picker, then you may have to variety a command in the command window that produces your cursor in progress in advance of proceeding to format the report designer. If you do this, the fields of the cursor will be readily available to you from the expression constructing. For example, you could variety the following in the command window:

Produce CURSOR Testcursor (Field1 c(ten) one of a kind,Field2 i)

At the time you have done this, you will discover the fields of your cursor shown in the fields record on the expression builder, so you can just click the sought after field to be extra to the report.