Populating Visible FoxPro Studies with information on the Fly

Populating Visible FoxPro Studies with information on the Fly


Visible FoxPro’s report author is a effective total-showcased report author that presents all the tools you want to produce and ship persuasive studies with your application. On Most occasions, you can produce Visible FoxPro studies based only on information that presently exists (e.g. an present table, query or see). Having said that, consider that the information you want to populate your report with does not exist as a table that can be conveniently extra to your report’s information surroundings nor does it exist as a established of tables on which you can execute a straight-forward SQL Question or see! This report describes how you can populate a Visible FoxPro report at operate time by amassing report requirements from consumers, deriving the information from your tables based on that requirements, developing a cursor and then populating the cursor with the derived information all at operate time.

Who should go through this report?

This report assumes some familiarity with setting up and building purposes with Microsoft Visible FoxPro nine


One particular of the critical advantages of utilizing Visible FoxPro as your primary development tool is that it presents all of the tools you want to construct effective, persuasive total-showcased purposes that are fast and aspect abundant!

One particular of the functions of Visible FoxPro that can make this achievable is the recently revamped report author that ships with Visible FoxPro nine! For the reason that of the want to provide or make information obtainable for your report or label prior to you print it, the Report Author presents a assortment of means to make information very easily obtainable for your report.

One particular system of making information obtainable for your report and incidentally the most typically employed is to publish a report that is always based on the exact same information resources. This system would in fact require you to increase tables or sights to the information surroundings of a report or to use a DO command in the Init function of the report’s information surroundings. Alternatively, you could execute a query by developing an executing an SQL Assertion in the Init function of the report’s information surroundings.

A second system is when you want to produce a report that utilizes different sets of information resources for the report. In this situation, you would dynamically open up these information resources at operate time by utilizing a USE

, USE , DO or SQL Select assertion to the click function of a button or other code that will operate prior to you situation a REPORT or LABEL command.

Having said that, what occurs when the information for your report is not in a table that can be dynamically queried with a Select SQL assertion nor is it in a sort that you can just open up with a USE command! The Richness of the Visible FoxPro programming language permits you to very easily resolve this problem as this report will exhibit. To exhibit the notion being talked about, consider that you want to print or generate the Profits and Expenditure Report for your company as at a presented working day! This would suggest that you want to preserve a every day managing harmony of every single account in your Typical Ledger chart of accounts in a table that we shall most likely call ActGLDayTot. The sections that stick to will explain how to produce these tables and their composition as properly as the code that generates the report.

Printing Studies in Visible FoxPro

As presently noted, you want to make information obtainable for a report prior to you move forward to print the report. To print a report in Visible FoxPro, you will want to situation the REPORT Sort command. For the total syntax of the REPORT Sort command, see your Visible FoxPro nine on line assist documentation.

If you situation the REPORT Sort command with no tables in the information surroundings and no information resources open up in the information surroundings of the report, the report just seems to flash and then close again! To avoid this problem, you will both want to increase information to the information surroundings or use the Init function of the information surroundings to make information obtainable inside of the information surroundings prior to printing. When the information does not presently exist in a query or in a sort that can be instantly queried, what will you do?

Building Facts Available on the Fly

For the reason that information have to be initially produced obtainable prior to managing a report, you will want to situation an SQL Select assertion or open up a table at the very least in the Init function of the Facts Atmosphere of the report. Having said that, if your information does not exist in a sort in which you can very easily construct a see in excess of it or a query in excess of it and if it does not exist in a sort in which it can be extra as a table then neither of the solutions talked about so considerably will generate the wanted success!

This means that you have to find a new way of making the information obtainable to your software. A nearer evaluation of how you would use the SQL Select assertion presents an perception into how you could do this. The general sort of the syntax of the SQL Select Assertion we want to think about is as follows:


Select [ALL | Distinctive] [Prime nExpr [Percent]] Select_Checklist_Item [, …]

   FROM [Power] Table_Checklist_Item [, …]

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

   [INTO StorageDestination | TO DisplayDestination]

This sort of the syntax indicates that the SQL Select assertion produces a query and then fetches the expected data from the table into a storage location most normally, a cursor (temporary table), table or array. The cursor representing storage location is then employed as the resource of information for the report. Based on this actuality, we can deduce that our report would operate if we established a cursor object utilizing the Build CURSOR or a temporary table utilizing Build Table in the Init function of the information surroundings. This would make information obtainable to the report and would hence prevent the report from again closing right away on being opened.

How would this in fact do the job in apply?

1.       Build your Report: To start with off, you would have to produce a report a report in the Report Designer. You can do this by choosing the Studies Node in the Project Manager and then choosing the New button. When the New Report dialog box shows, you would then pick New Report. Visible FoxPro would then produce a new blank report named Report1 and open up it in the Report Designer

two.       Layout your Report: Now that the report designer is open up, you can begin laying out your report. Commonly, you would do this by dragging fields from the information surroundings unto the report canvas. But in this situation, you have no information in the information surroundings so you have to established the attributes of the report manually. You have to as a result screen the Studies Controls Toolbar by choosing Studies Manage Toolbar from the Watch key menu. As soon as the toolbar shows, you can layout your report area by doing the pursuing motion:

a.      Select a control from the toolbox to be put on your report. If you pick a control these as a textual content box control and then click on the depth band of the report, Visible FoxPro immediately opens the Area Attributes dialog box so you can established not only the resource of information for the industry but also other qualities of the industry.

b.      In the Expression box on the Typical tabbed site, enter the expression that will server as a resource of information for the report. You will enter it in the structure cursorname.fieldname where Cursorname will characterize the title of the cursor that you will produce (of program you have not established it still or else you would just have selected the … button to let you pick it from the expression builder[i]). If you click a control these as a Label control, position it any where on the report and then style where the Insertion stage seems to increase a descriptive label. For example, click a label control on the report and then increase a descriptive label for every single textual content box in the Page header. You can increase descriptions these as Area One particular and Area Two. You can increase a report title (e.g. Check Report) by clicking on the Label control, and then clicking at the appropriate situation on the Report Header. To established variations and font dimensions for your captions, correct-click the appropriate caption, pick Attributes and then pick the Model tabbed site. In the Model tabbed site, you can make the appropriate settings underneath the Font box or make any other settings as important. Keep in mind to help save your do the job as you go alongside.

c.       Now that you have gotten fields on to your report, you can utilize the normal formatting to the report these as calculated fields, lines, etc

3.       Set Attributes for the Facts Atmosphere: You will continue to want to prevent your report from immediately shutting down when you attempt to operate it so you have to established attributes for the information surroundings as follows:

a.      Suitable-click your report and then pick the Facts Atmosphere menu command from the shortcut menu that shows. The Facts Atmosphere – Report Designer opens. It is vacant, demonstrating that no information resources have been extra to it.

b.      Suitable-Click within the Facts Atmosphere and then pick the Attributes menu command to screen the attributes sheet for the Facts Atmosphere.

c.       Set the AutoOpenTables residence to fake. When you established this residence to fake, the report does not attempt to open up the tables or sights in the information surroundings on managing of the report. If this had been to take place, the report would just find that the information surroundings did not contain any information and then the report would close abruptly. You are now prepared to increase code for the report as explained in the following phase (phase four – Producing code to make information obtainable).

four.       Produce Code to make Facts Available: Keep in mind that the information is to be fetched or produced obtainable only at operate time. So you have to now publish the code that will make this information obtainable for your report. You will do this in the Init function of the information surroundings by crafting code these as:

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

b.      In the code window, style the code that produces your cursor by utilizing the Build CURSOR command and also publish code to increase many data to the cursor by utilizing the APPEND command. This code could glance as follows:

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


Replace TestCursor.Field1 WITH “Check”

Replace TestCursor.Field2 WITH 50


Replace TestCursor.Field1 WITH “TEST2”

Replace TestCursor.Field2 WITH 100


Replace TestCursor.Field1 WITH “TEST3”

Replace TestCursor.Field2 WITH a hundred and fifty


c.       Near the code window and close the information surroundings designer

d.      Promptly check your report by choosing 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 established your report and created information at operate time, you will want to help save the report and then to operate it. You can check regardless of whether your report will operate by doing the pursuing motion:

a.      Opt for the Help save button on the toolbar to make sure that you have saved your report.

b.      Near the report by choosing the Near button. The report seems underneath the Studies node in the Project Manager.

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

6.       Operating Your Report By way of the User Interface: You would ordinarily make your report obtainable to the consumers of your application via the application person interface. For example, you may well provide a sort via which consumers can pick your report and then press 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 person interface, you will have to carry out the pursuing motion:

a.      Select the Kinds node on the Files tabbed site of the Visible FoxPro undertaking supervisor and then pick the New button. The New Sort dialog box shows.

b.      Opt for the New Sort button. Visible FoxPro produces a new sort named Form1 and opens it in the Sort Designer.

c.       Exhibit the Kinds Manage Toolbox if it is not presently shown by choosing the Sort Controls Toolbar menu on the see menu.

d.      Select the Command Button control on the Kinds Manage Toolbox and then click on the sort. Visible FoxPro will produce a new command button termed Command1.

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

f.        You can now enter the pursuing code:



g.      Click the Help save button on the button bar to help save the sort you have established.

h.      Run the sort by both urgent Ctrl + E or by choosing the Run toolbar button on the toolbar. The sort runs.

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

Making use of the notion to a Serious Lifestyle Situation

The example presently illustrated has been saved deliberately uncomplicated. Now, this have to be used in a genuine-life problem. In the example shown, the cursor is populated by issuing APPEND BLANK commands. In a genuine-life application even so, it may well be achievable that your information may well presently exist, needing only to be go through back again and re-arranged into a structure that your report can print. Allows illustrate this by setting up a Demo Equilibrium Report as at a presented working day (Demo balances are normally printed as at a presented time period end day).

To be capable to print these a trail harmony, we would have to know the harmony of an account as at a presented working day. Allows now settle for that these information is saved working day-by-working day as transactions are handed in a table termed ActGLDayTot and this table could be established with the pursuing SQL Assertion:

Build Table ActGLDayTot(BatchNo c(twenty) Key Key,AccountCode c(fifteen),

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

CreditAmt Y,Equilibrium Y)

For every single account in the Typical Ledger Learn file, we want to get hold of the account’s balances as at the specified working day so that we will be capable to construct the trial harmony. Now consider that your Typical Ledger Learn table could be established with the pursuing SQL Assertion:

Build Table ActGLMast(AccountCode c(fifteen) Key Key,AccountName c(50),

AccountType c(40),CurrBal Y)

The pursuing code is put in the Init Occasion of the information surroundings of our report:

* This Code Constructs the cursor employed in the Report

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

DIMENSION arrTR(1,nine)

Retailer “” TO cBranchCode

Retailer (  /  /    ) TO dValueDate

Retailer TO intNoOfRows

* 1) Open the sort and get hold of the parameters

DO Sort frmMgtTBalByDate.Scx Connected

dValueDate = frmMgtTBalByDate.txtValueDate.Benefit

cBranchCode = frmMgtTBalByDate.txtBranchCode.Benefit




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

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

Select MgtTBalByDate



In the code over memory variables are declared that will be employed both to keep the user’s report requirements or to retailer and transport information. The DIMENSION command produces an array that will be employed to return information from a Facts Aware Class that encapsulates the performance of the ActGLDayTot table. Yet another point to notice is how we have allowed consumers to specify the information to be provided in the report by moving into the specified day for which they want a trial harmony. To obtain this data from the consumers, the line DO Sort frmMgtTBalByDate.Scx runs the report parameter sort so consumers can enter requirements. When  the person clicks the Ok button in that sort, a THISFORM.Cover command quickly hides the sort from the person whilst the lines right away pursuing the DO Sort command obtain the data on the sort. The line frmMgtTBalByDate.Launch then removes the sort from memory.

Following, the line oDT = CREATEOBJECT (‘ActGLDayTot’) produces an instance of the course ActGLDayTot and retailers a reference to it in the object variable oDT. The line lAnswer = GetTransByDate… phone calls the system in the course to return the certain information we want, passing the important parameters gathered sort the person alongside with the array that will be employed to retailer and transport the information back again to our report.

As we did before, we then use a Build CURSOR MgtTBalByDate command to produce a cursor with the important fields. The line APPEND FROM ARRAY arrTR populates the table. The report is then produced to identify this cursor in its information surroundings with the line THIS.OpenTables().

By separating the code that does the assortment and processing of data from the Report’s own Init function, we are capable to make this code obtainable always sort a number of places simply because it is contained in a information knowledgeable course. The code contained inside of the GetTransByDate system of the ActGLDayTot course is as follows:

* Attain 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 suitable forms to avoid any faults

Retailer “” TO cMsg

Retailer TO intRows

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

      cMsg = “You have to enter transaction day!”

      MESSAGEBOX(cMsg,forty eight,chrProgTitle)

      RETURN .F.


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

      RETURN .F.


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

      cMsg = “Array of Transactions not observed!”

      MESSAGEBOX(cMsg,forty eight,chrProgTitle)

      RETURN .F.


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

      cMsg = “ALL”


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


IF Employed(‘ActGLMast’)

      lGLMastInUse = .T.


      USE ActGLMast IN

      lGLMastInUse = .F.


Select ActGLMast

GO Prime

IF Employed(‘ActGLDayTot’)

      lGLDayTotInUse  = .T.


      USE ActGLDayTot IN

      lGLDayTotInUse = .F.


Select ActGLMast

GO Prime

SCAN &cMsg

      intRows = intRows + 1

      DIMENSION arrTR(intRows,nine)   

      arrTR(intRows,1) = ActGLMast.AccountCode

      arrTR(intRows,two) = 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


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

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


      Select ActGLDayTot

      GO Prime

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

      Find FOR ActGLDayTot.ValueDate = dTranDate

      IF Discovered()

                  DO Scenario

                        Scenario ActGLDayTot.Equilibrium >

                              arrTR(intRows,five) = ActGLDayTot.Equilibrium

                              arrTR(intRows,6) =

                        Scenario ActGLDayTot.Equilibrium < 0

                              arrTR(intRows,five) =

                              arrTR(intRows,6) = ActGLDayTot.Equilibrium


                              arrTR(intRows,five) =

                              arrTR(intRows,6) =



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

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



intNoOfRows = intRows

* Now close all tables you dont want


      USE IN ActGLMast



      USE IN ActGLDayTot



The PARAMETERS command that commences the system identifies the parameters handed to the course. The software employs a SCAN…ENDSCAN loop to go via the ActGLMast table to isolate all accounts that satisfy the requirements and then to populate the array with matching information from the ActGlDayTot table. At the end, the software returns .T. if the system completes efficiently. You could now operate this report with a REPORT Sort command just as we did both from a sort or visual FoxPro menu.


Visible FoxPro’s information manipulation language is 1 of the factors that can make Visible FoxPro standout amongst various merchandise in its course. This report has demonstrated how the richness of the Visible FoxPro language and development surroundings permits a developer to compile the information required for a report at operate time and continue to be capable to fairly substantially control the report technology process. Even however this report has assumed that the programmer is setting up a ‘pure fox’ application (immediately after all, Visible FoxPro offers you fairly substantially all the things you want to construct full sturdy information administration purposes), with a little tuning and adjustments, you can use this system to derive information from effective SQL Servers these as ORACLE, Microsoft’s own SQL Server or Advantage Database Server for use inside of a Visible FoxPro report. If you can consider it, Visible FoxPro allows you construct it.

[i] If you want to be capable to use the expression setting up to pick from a record of fields utilizing a industry picker, then you may well have to style a command in the command window that produces your cursor in progress 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 setting up. For example, you could style the pursuing in the command window:

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

As soon as you have accomplished this, you will find the fields of your cursor outlined in the fields record on the expression builder, so you can just click the wanted industry to be extra to the report.

Comments are closed.
May 2020