Populating Visual FoxPro Studies with details on the Fly


Visual FoxPro’s report author is a impressive comprehensive-featured report author that gives all the applications you have to have to build and ship powerful studies with your software. On Most events, you can build Visual FoxPro studies based mostly only on details that already exists (e.g. an present table, question or watch). Even so, picture that the details you have to have to populate your report with does not exist as a table that can be commonly extra to your report’s details atmosphere nor does it exist as a set of tables on which you can execute a straight-ahead SQL Question or watch! This short article describes how you can populate a Visual FoxPro report at run time by collecting report requirements from people, deriving the details from your tables based mostly on that requirements, generating a cursor and then populating the cursor with the derived details all at run time.

Who should read through this short article?

This short article assumes some familiarity with creating and acquiring applications with Microsoft Visual FoxPro nine


Just one of the key added benefits of utilizing Visual FoxPro as your primary progress resource is that it gives all of the applications you have to have to create impressive, powerful comprehensive-featured applications that are quickly and attribute wealthy!

Just one of the functions of Visual FoxPro that helps make this feasible is the recently revamped report author that ships with Visual FoxPro nine! Due to the fact of the have to have to supply or make details obtainable for your report or label prior to you print it, the Report Writer gives a variety of methods to make details easily obtainable for your report.

Just one approach of producing details obtainable for your report and incidentally the most generally made use of is to generate a report that is often based mostly on the similar details sources. This approach would actually demand you to add tables or sights to the details atmosphere of a report or to use a DO command in the Init event of the report’s details atmosphere. Alternatively, you could execute a question by generating an executing an SQL Assertion in the Init event of the report’s details atmosphere.

A 2nd approach is when you have to have to build a report that makes use of independent sets of details sources for the report. In this case, you would dynamically open up these details sources at run time by utilizing a USE

, USE , DO or SQL Pick statement to the click on event of a button or other code that will run prior to you difficulty a REPORT or LABEL command.

Even so, what transpires when the details for your report is not in a table that can be dynamically queried with a Pick SQL statement nor is it in a variety that you can just open up with a USE command! The Richness of the Visual FoxPro programming language lets you to easily clear up this issue as this short article will show. To show the idea being reviewed, picture that you have to have to print or generate the Money and Expense Report for your company as at a given day! This would imply that you have to have to hold a each day running balance of each and every account in your Normal Ledger chart of accounts in a table that we shall most likely phone ActGLDayTot. The sections that abide by will explain how to build these tables and their framework as properly as the code that generates the report.

Printing Studies in Visual FoxPro

As already pointed out, you have to have to make details obtainable for a report prior to you move forward to print the report. To print a report in Visual FoxPro, you will have to have to difficulty the REPORT Kind command. For the comprehensive syntax of the REPORT Kind command, see your Visual FoxPro nine on line assistance documentation.

If you difficulty the REPORT Kind command with no tables in the details atmosphere and no details sources open up in the details atmosphere of the report, the report just appears to flash and then shut once more! To keep away from this situation, you will either have to have to add details to the details atmosphere or use the Init event of the details atmosphere to make details obtainable within just the details atmosphere prior to printing. When the details does not already exist in a question or in a variety that can be specifically queried, what will you do?

Making Details Available on the Fly

Due to the fact details must be initial designed obtainable prior to running a report, you will have to have to difficulty an SQL Pick statement or open up a table at least in the Init event of the Details Ecosystem of the report. Even so, if your details does not exist in a variety in which you can easily create a watch around it or a question around it and if it does not exist in a variety in which it can be extra as a table then neither of the approaches reviewed so considerably will produce the wished-for final results!

This means that you must come across a new way of producing the details obtainable to your software. A nearer evaluation of how you would use the SQL Pick statement gives an insight into how you could do this. The general variety of the syntax of the SQL Pick Assertion we want to take into consideration is as follows:


Pick [ALL | Distinctive] [Major nExpr [Percent]] Pick_List_Merchandise [, …]

   FROM [Pressure] Table_List_Merchandise [, …]

   [Wherever JoinCondition | FilterCondition [AND | OR JoinCondition | FilterCondition] …]

   [INTO StorageDestination | TO DisplayDestination]

This variety of the syntax implies that the SQL Pick statement creates a question and then fetches the demanded information from the table into a storage desired destination most typically, a cursor (temporary table), table or array. The cursor representing storage desired destination is then made use of as the source of details for the report. Primarily based on this actuality, we can deduce that our report would run if we developed a cursor object utilizing the Make CURSOR or a temporary table utilizing Make Table in the Init event of the details atmosphere. This would make details obtainable to the report and would as a result stop the report from once more closing immediately on being opened.

How would this actually get the job done in apply?

1.       Make your Report: To start with off, you would have to build a report a report in the Report Designer. You can do this by deciding on the Studies Node in the Task Supervisor and then deciding on the New button. When the New Report dialog box shows, you would then pick New Report. Visual FoxPro would then build a new blank report named Report1 and open up it in the Report Designer

2.       Structure your Report: Now that the report designer is open up, you can get started laying out your report. Commonly, you would do this by dragging fields from the details atmosphere unto the report canvas. But in this case, you have no details in the details atmosphere so you must set the properties of the report manually. You must as a result screen the Studies Controls Toolbar by deciding on Studies Control Toolbar from the See principal menu. At the time the toolbar shows, you can layout your report area by undertaking the following action:

a.      Pick a manage from the toolbox to be positioned on your report. If you find a manage these as a textual content box manage and then click on on the depth band of the report, Visual FoxPro routinely opens the Area Qualities dialog box so you can set not only the source of details for the area but also other properties of the area.

b.      In the Expression box on the Normal tabbed page, enter the expression that will server as a source of details for the report. You will enter it in the structure cursorname.fieldname the place Cursorname will depict the identify of the cursor that you will build (of class you have not developed it nonetheless otherwise you would just have preferred the … button to let you find it from the expression builder[i]). If you click on a manage these as a Label manage, location it everywhere on the report and then kind the place the Insertion issue appears to add a descriptive label. For illustration, click on a label manage on the report and then add a descriptive label for each and every textual content box in the Webpage header. You can add descriptions these as Area Just one and Area Two. You can add a report title (e.g. Test Report) by clicking on the Label manage, and then clicking at the correct posture on the Report Header. To set styles and font sizes for your captions, appropriate-click on the correct caption, pick Qualities and then pick the Type tabbed page. In the Type tabbed page, you can make the correct settings below the Font box or make any other settings as necessary. Don’t forget to conserve your get the job done as you go together.

c.       Now that you have gotten fields onto your report, you can apply the standard formatting to the report these as calculated fields, lines, etcetera

3.       Established Qualities for the Details Ecosystem: You will nevertheless want to stop your report from routinely shutting down when you attempt to run it so you must set properties for the details atmosphere as follows:

a.      Appropriate-click on your report and then pick the Details Ecosystem menu command from the shortcut menu that shows. The Details Ecosystem – Report Designer opens. It is vacant, showing that no details sources have been extra to it.

b.      Appropriate-Click within the Details Ecosystem and then pick the Qualities menu command to screen the properties sheet for the Details Ecosystem.

c.       Established the AutoOpenTables house to fake. When you set this house to fake, the report does not endeavor to open up the tables or sights in the details atmosphere on running of the report. If this have been to materialize, the report would just come across that the details atmosphere did not contain any details and then the report would shut abruptly. You are now ready to add code for the report as described in the following phase (phase four – Writing code to make details obtainable).

four.       Write Code to make Details Available: Don’t forget that the details is to be fetched or designed obtainable only at run time. So you must now generate the code that will make this details obtainable for your report. You will do this in the Init event of the details atmosphere by producing code these as:

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

b.      In the code window, kind the code that creates your cursor by utilizing the Make CURSOR command and also generate code to add numerous documents to the cursor by utilizing the APPEND command. This code could look as follows:

Make CURSOR Testcursor (Field1 c(ten) exclusive,Field2 i)


Exchange TestCursor.Field1 WITH “Test”

Exchange TestCursor.Field2 WITH 50


Exchange TestCursor.Field1 WITH “TEST2”

Exchange TestCursor.Field2 WITH 100


Exchange TestCursor.Field1 WITH “TEST3”

Exchange TestCursor.Field2 WITH a hundred and fifty


c.       Close the code window and shut the details atmosphere designer

d.      Rapidly test your report by deciding on the Print Preview button on the toolbar. The report should open up in Print Preview method.

five.       Help save and Run your Report: Now that you have developed your report and created details at run time, you will have to have to conserve the report and then to run it. You can test whether your report will run by undertaking the following action:

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

b.      Close the report by deciding on the Close button. The report appears below the Studies node in the Task Supervisor.

c.       To run this report, find the report (Report1) in the job manager and then pick the Preview button. The report opens in print preview method.

six.       Working Your Report By way of the Person Interface: You would ordinarily make your report obtainable to the people of your software by way of the software person interface. For illustration, you may well supply a variety by way of which people can find your report and then press either a preview or a print button. To do this, you will have to use the REPORT Kind command to run the report programmatically. If you want your report to be obtainable by way of the software person interface, you will have to execute the following action:

a.      Pick the Sorts node on the Documents tabbed page of the Visual FoxPro job manager and then pick the New button. The New Kind dialog box shows.

b.      Pick the New Kind button. Visual FoxPro creates a new variety named Form1 and opens it in the Kind Designer.

c.       Exhibit the Sorts Control Toolbox if it is not already displayed by deciding on the Kind Controls Toolbar menu on the watch menu.

d.      Pick the Command Button manage on the Sorts Control Toolbox and then click on on the variety. Visual FoxPro will build a new command button called Command1.

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

f.        You can now enter the following code:



g.      Click the Help save button on the button bar to conserve the variety you have developed.

h.      Run the variety by either pressing Ctrl + E or by deciding on the Run toolbar button on the toolbar. The variety operates.

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

Implementing the idea to a Genuine Existence Scenario

The illustration already illustrated has been kept intentionally simple. Now, this must be applied in a actual-everyday living situation. In the illustration shown, the cursor is populated by issuing APPEND BLANK instructions. In a actual-everyday living software however, it may well be feasible that your details may well already exist, needing only to be read through back and re-organized into a structure that your report can print. Lets illustrate this by creating a Demo Balance Report as at a given day (Demo balances are typically printed as at a given period finish day).

To be in a position to print these a trail balance, we would have to know the balance of an account as at a given day. Lets now settle for that these details is stored day-by-day as transactions are handed in a table called ActGLDayTot and this table could be developed with the following SQL Assertion:

Make Table ActGLDayTot(BatchNo c(twenty) Major Crucial,AccountCode c(fifteen),

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

CreditAmt Y,Balance Y)

For each and every account in the Normal Ledger Learn file, we want to receive the account’s balances as at the specified day so that we will be in a position to create the trial balance. Now picture that your Normal Ledger Learn table could be developed with the following SQL Assertion:

Make Table ActGLMast(AccountCode c(fifteen) Major Crucial,AccountName c(50),

AccountType c(forty),CurrBal Y)

The following code is positioned in the Init Function of the details atmosphere of our report:

* This Code Constructs the cursor made use of in the Report

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

DIMENSION arrTR(1,nine)

Retail store “” TO cBranchCode

Retail store (  /  /    ) TO dValueDate

Retail store TO intNoOfRows

* 1) Open up the variety and receive the parameters

DO Kind frmMgtTBalByDate.Scx Connected

dValueDate = frmMgtTBalByDate.txtValueDate.Price

cBranchCode = frmMgtTBalByDate.txtBranchCode.Price




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

Make 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 made use of either to hold the user’s report requirements or to keep and transportation details. The DIMENSION command creates an array that will be made use of to return details from a Details Mindful Class that encapsulates the functionality of the ActGLDayTot table. A further factor to observe is how we have allowed people to specify the details to be incorporated in the report by coming into the specified day for which they want a trial balance. To gather this information from the people, the line DO Kind frmMgtTBalByDate.Scx operates the report parameter variety so people can enter requirements. When  the person clicks the Okay button in that variety, a THISFORM.Disguise command temporarily hides the variety from the person although the lines immediately following the DO Kind command gather the information on the variety. The line frmMgtTBalByDate.Launch then removes the variety from memory.

Subsequent, the line oDT = CREATEOBJECT (‘ActGLDayTot’) creates an occasion of the course ActGLDayTot and merchants a reference to it in the object variable oDT. The line lAnswer = GetTransByDate… calls the approach in the course to return the certain details we want, passing the necessary parameters gathered variety the person together with the array that will be made use of to keep and transportation the details back to our report.

As we did earlier, we then use a Make CURSOR MgtTBalByDate command to build a cursor with the necessary fields. The line APPEND FROM ARRAY arrTR populates the table. The report is then designed to realize this cursor in its details atmosphere with the line THIS.OpenTables().

By separating the code that does the assortment and processing of information from the Report’s individual Init event, we are in a position to make this code obtainable often variety a number of places due to the fact it is contained in a details conscious course. The code contained within just the GetTransByDate approach of the ActGLDayTot course is as follows:

* Get Transaction By Day

PARAMETERS dTranDate,cBranchCode,arrTR,intNoOfRows,chrProgTitle

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

Nearby lGLDayTotInUse as Sensible

* Initialize your variables to the appropriate kinds to keep away from any mistakes

Retail store “” TO cMsg

Retail store TO intRows

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

      cMsg = “You must enter transaction day!”


      RETURN .F.


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

      RETURN .F.


IF Kind(‘arrTR’,1) <> “A”

      cMsg = “Array of Transactions not located!”


      RETURN .F.


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

      cMsg = “ALL”


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


IF Employed(‘ActGLMast’)

      lGLMastInUse = .T.


      USE ActGLMast IN

      lGLMastInUse = .F.


Pick ActGLMast

GO Major

IF Employed(‘ActGLDayTot’)

      lGLDayTotInUse  = .T.


      USE ActGLDayTot IN

      lGLDayTotInUse = .F.


Pick ActGLMast

GO Major

SCAN &cMsg

      intRows = intRows + 1

      DIMENSION arrTR(intRows,nine)   

      arrTR(intRows,1) = ActGLMast.AccountCode

      arrTR(intRows,2) = ActGLMast.AccountName

      arrTR(intRows,3) = ActGLMast.AccountType

      DO Scenario

            Scenario ActGLMast.CurrBal >

                  arrTR(intRows,seven) = ActGLMast.CurrBal

                  arrTR(intRows,8) =

            Scenario ActGLMast.CurrBal < 0

                  arrTR(intRows,seven) =

                  arrTR(intRows,8) = ActGLMast.CurrBal

            If not

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

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


      Pick ActGLDayTot

      GO Major

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

      Track down FOR ActGLDayTot.ValueDate = dTranDate

      IF Identified()

                  DO Scenario

                        Scenario ActGLDayTot.Balance >

                              arrTR(intRows,five) = ActGLDayTot.Balance

                              arrTR(intRows,six) =

                        Scenario ActGLDayTot.Balance < 0

                              arrTR(intRows,five) =

                              arrTR(intRows,six) = ActGLDayTot.Balance

                        If not

                              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 have to have


      USE IN ActGLMast



      USE IN ActGLDayTot



The PARAMETERS command that starts the approach identifies the parameters handed to the course. The software works by using a SCAN…ENDSCAN loop to go by way of the ActGLMast table to isolate all accounts that fulfill the requirements and then to populate the array with matching details from the ActGlDayTot table. At the finish, the software returns .T. if the approach completes successfully. You could now run this report with a REPORT Kind command just as we did either from a variety or visible FoxPro menu.


Visual FoxPro’s details manipulation language is 1 of the items that helps make Visual FoxPro standout among a lot of products and solutions in its course. This short article has shown how the richness of the Visual FoxPro language and progress atmosphere lets a developer to compile the details needed for a report at run time and nevertheless be in a position to pretty a great deal manage the report technology system. Even however this short article has assumed that the programmer is creating a ‘pure fox’ software (just after all, Visual FoxPro offers you pretty a great deal almost everything you have to have to create total strong details administration applications), with a minor tuning and alterations, you can use this approach to derive details from impressive SQL Servers these as ORACLE, Microsoft’s individual SQL Server or Advantage Database Server for use within just a Visual FoxPro report. If you can picture it, Visual FoxPro lets you create it.

[i] If you want to be in a position to use the expression creating to find from a list of fields utilizing a area picker, then you may well have to kind a command in the command window that creates your cursor in advance prior to proceeding to layout the report designer. If you do this, the fields of the cursor will be obtainable to you from the expression creating. For illustration, you could kind the following in the command window:

Make CURSOR Testcursor (Field1 c(ten) exclusive,Field2 i)

At the time you have done this, you will come across the fields of your cursor stated in the fields list on the expression builder, so you can just click on the wished-for area to be extra to the report.