Populating Visible FoxPro Reports with data on the Fly

Populating Visible FoxPro Reports with data on the Fly


Visible FoxPro’s report writer is a powerful comprehensive-featured report writer that gives all the instruments you want to make and ship persuasive stories with your software. On Most events, you can make Visible FoxPro stories centered only on data that previously exists (e.g. an present table, question or view). On the other hand, consider that the data you want to populate your report with does not exist as a table that can be commonly additional to your report’s data setting nor does it exist as a established of tables upon which you can execute a straight-ahead SQL Query or view! This short article describes how you can populate a Visible FoxPro report at operate time by accumulating report standards from buyers, deriving the data from your tables centered on that standards, building a cursor and then populating the cursor with the derived data all at operate time.

Who need to read this short article?

This short article assumes some familiarity with making and acquiring apps with Microsoft Visible FoxPro nine


A single of the essential benefits of working with Visible FoxPro as your main enhancement software is that it gives all of the instruments you want to establish powerful, persuasive comprehensive-featured apps that are rapidly and feature prosperous!

A single of the functions of Visible FoxPro that can make this achievable is the recently revamped report writer that ships with Visible FoxPro nine! Due to the fact of the want to present or make data readily available for your report or label in advance of you print it, the Report Author gives a assortment of techniques to make data conveniently readily available for your report.

A single system of creating data readily available for your report and by the way the most typically applied is to compose a report that is always centered on the very same data resources. This system would actually call for you to insert tables or views to the data setting of a report or to use a DO command in the Init occasion of the report’s data setting. Alternatively, you could execute a question by building an executing an SQL Assertion in the Init occasion of the report’s data setting.

A second system is when you want to make a report that utilizes separate sets of data resources for the report. In this circumstance, you would dynamically open individuals data resources at operate time by working with a USE

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

On the other hand, what occurs when the data for your report is not in a table that can be dynamically queried with a Decide on SQL statement nor is it in a type that you can merely open with a USE command! The Richness of the Visible FoxPro programming language permits you to conveniently resolve this difficulty as this short article will show. To show the thought being mentioned, consider that you want to print or deliver the Revenue and Price Report for your company as at a given day! This would imply that you want to continue to keep a day-to-day functioning harmony of every account in your Common Ledger chart of accounts in a table that we shall maybe call ActGLDayTot. The sections that stick to will describe how to make these tables and their structure as very well as the code that generates the report.

Printing Reports in Visible FoxPro

As previously observed, you want to make data readily available for a report in advance of you continue to print the report. To print a report in Visible FoxPro, you will want to issue the REPORT Variety command. For the comprehensive syntax of the REPORT Variety command, see your Visible FoxPro nine on-line enable documentation.

If you issue the REPORT Variety command with no tables in the data setting and no data resources open in the data setting of the report, the report merely seems to flash and then near all over again! To stay clear of this scenario, you will either want to insert data to the data setting or use the Init occasion of the data setting to make data readily available within just the data setting prior to printing. When the data does not previously exist in a question or in a type that can be immediately queried, what will you do?

Creating Information Obtainable on the Fly

Due to the fact data should be first produced readily available prior to functioning a report, you will want to issue an SQL Decide on statement or open a table at the very least in the Init occasion of the Information Natural environment of the report. On the other hand, if your data does not exist in a type in which you can conveniently establish a view above it or a question above it and if it does not exist in a type in which it can be additional as a table then neither of the techniques mentioned so significantly will yield the wanted effects!

This implies that you should locate a new way of creating the data readily available to your application. A nearer evaluation of how you would use the SQL Decide on statement gives an perception into how you could do this. The normal type of the syntax of the SQL Decide on Assertion we want to take into account is as follows:


Decide on [ALL | Distinct] [Major nExpr [P.c]] Decide on_Listing_Item [, …]

   FROM [Pressure] Desk_Listing_Item [, …]

   [Exactly where JoinCondition | FilterCondition [AND | OR JoinCondition | FilterCondition] …]

   [INTO StorageDestination | TO DisplayDestination]

This type of the syntax indicates that the SQL Decide on statement generates a question and then fetches the essential facts from the table into a storage vacation spot most normally, a cursor (short term table), table or array. The cursor representing storage vacation spot is then applied as the resource of data for the report. Primarily based on this actuality, we can deduce that our report would operate if we established a cursor object working with the Create CURSOR or a short term table working with Create Desk in the Init occasion of the data setting. This would make data readily available to the report and would hence protect against the report from all over again closing instantly upon being opened.

How would this actually operate in apply?

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

2.       Format your Report: Now that the report designer is open, you can start out laying out your report. Commonly, you would do this by dragging fields from the data setting unto the report canvas. But in this circumstance, you have no data in the data setting so you should established the homes of the report manually. You should as a result show the Reports Controls Toolbar by choosing Reports Handle Toolbar from the Look at key menu. At the time the toolbar displays, you can structure your report surface by accomplishing the following motion:

a.      Decide on a management from the toolbox to be placed on your report. If you decide on a management such as a textual content box management and then simply click on the detail band of the report, Visible FoxPro automatically opens the Area Properties dialog box so you can established not only the resource of data for the subject but also other properties of the subject.

b.      In the Expression box on the Common tabbed page, enter the expression that will server as a resource of data for the report. You will enter it in the format cursorname.fieldname exactly where Cursorname will characterize the identify of the cursor that you will make (of class you have not established it but or else you would merely have selected the … button to enable you decide on it from the expression builder[i]). If you simply click a management such as a Label management, spot it wherever on the report and then variety exactly where the Insertion point seems to insert a descriptive label. For illustration, simply click a label management on the report and then insert a descriptive label for every textual content box in the Web page header. You can insert descriptions such as Area A single and Area Two. You can insert a report title (e.g. Check Report) by clicking on the Label management, and then clicking at the correct place on the Report Header. To established designs and font measurements for your captions, correct-simply click the correct caption, choose Properties and then choose the Model tabbed page. In the Model tabbed page, you can make the correct settings under the Font box or make any other settings as important. Try to remember to save your operate as you go together.

c.       Now that you have gotten fields on to your report, you can utilize the typical formatting to the report such as calculated fields, strains, and so forth

three.       Established Properties for the Information Natural environment: You will even now want to protect against your report from automatically shutting down when you test to operate it so you should established homes for the data setting as follows:

a.      Ideal-simply click your report and then choose the Information Natural environment menu command from the shortcut menu that displays. The Information Natural environment – Report Designer opens. It is empty, exhibiting that no data resources have been additional to it.

b.      Ideal-Click inside of the Information Natural environment and then choose the Properties menu command to show the homes sheet for the Information Natural environment.

c.       Established the AutoOpenTables residence to bogus. When you established this residence to bogus, the report does not attempt to open the tables or views in the data setting upon functioning of the report. If this were being to come about, the report would merely locate that the data setting did not incorporate any data and then the report would near abruptly. You are now ready to insert code for the report as described in the next phase (phase four – Crafting code to make data readily available).

four.       Produce Code to make Information Obtainable: Try to remember that the data is to be fetched or produced readily available only at operate time. So you should now compose the code that will make this data readily available for your report. You will do this in the Init occasion of the data setting by composing code such as:

a.      Track down the Init occasion of the data setting in the homes sheet and then double-simply click it to open the code window.

b.      In the code window, variety the code that generates your cursor by working with the Create CURSOR command and also compose code to insert many data to the cursor by working with the APPEND command. This code could look as follows:

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


Switch TestCursor.Field1 WITH “Check”

Switch TestCursor.Field2 WITH fifty


Switch TestCursor.Field1 WITH “TEST2”

Switch TestCursor.Field2 WITH 100


Switch TestCursor.Field1 WITH “TEST3”

Switch TestCursor.Field2 WITH one hundred fifty


c.       Close the code window and near the data setting designer

d.      Immediately check your report by choosing the Print Preview button on the toolbar. The report need to open in Print Preview mode.

5.       Save and Operate your Report: Now that you have established your report and created data at operate time, you will want to save the report and then to operate it. You can check regardless of whether your report will operate by accomplishing the following motion:

a.      Pick the Save button on the toolbar to make sure that you have saved your report.

b.      Close the report by choosing the Close button. The report seems under the Reports node in the Undertaking Manager.

c.       To operate this report, decide on the report (Report1) in the undertaking manager and then choose the Preview button. The report opens in print preview mode.

6.       Running Your Report By means of the Person Interface: You would usually make your report readily available to the buyers of your software via the software consumer interface. For illustration, you may well present a type via which buyers can decide on your report and then press either a preview or a print button. To do this, you will have to use the REPORT Variety command to operate the report programmatically. If you want your report to be readily available via the software consumer interface, you will have to accomplish the following motion:

a.      Decide on the Forms node on the Files tabbed page of the Visible FoxPro undertaking manager and then choose the New button. The New Variety dialog box displays.

b.      Pick the New Variety button. Visible FoxPro generates a new type named Form1 and opens it in the Variety Designer.

c.       Exhibit the Forms Handle Toolbox if it is not previously shown by choosing the Variety Controls Toolbar menu on the view menu.

d.      Decide on the Command Button management on the Forms Handle Toolbox and then simply click on the type. Visible FoxPro will make a new command button known as Command1.

e.       Double-Click Command1 button to show the Code window for its Click occasion.

f.        You can now enter the following code:



g.      Click the Save button on the button bar to save the type you have established.

h.      Operate the type by either urgent Ctrl + E or by choosing the Operate toolbar button on the toolbar. The type runs.

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

Implementing the thought to a Authentic Lifetime Scenario

The illustration previously illustrated has been retained intentionally very simple. Now, this should be used in a genuine-everyday living scenario. In the illustration revealed, the cursor is populated by issuing APPEND BLANK instructions. In a genuine-everyday living software on the other hand, it may well be achievable that your data may well previously exist, needing only to be read again and re-organized into a format that your report can print. Lets illustrate this by making a Demo Balance Report as at a given day (Demo balances are normally printed as at a given time period end date).

To be ready to print such a path harmony, we would have to know the harmony of an account as at a given day. Lets now acknowledge that such data is stored day-by-day as transactions are handed in a table known as ActGLDayTot and this table could be established with the following SQL Assertion:

Create Desk ActGLDayTot(BatchNo c(20) Main Essential,AccountCode c(15),

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

CreditAmt Y,Balance Y)

For every account in the Common Ledger Master file, we want to get the account’s balances as at the specified day so that we will be ready to establish the trial harmony. Now consider that your Common Ledger Master table could be established with the following SQL Assertion:

Create Desk ActGLMast(AccountCode c(15) Main Essential,AccountName c(fifty),

AccountType c(forty),CurrBal Y)

The following code is placed in the Init Celebration of the data setting of our report:

* This Code Constructs the cursor applied in the Report

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

DIMENSION arrTR(1,nine)

Retail outlet “” TO cBranchCode

Retail outlet (  /  /    ) TO dValueDate

Retail outlet TO intNoOfRows

* 1) Open the type and get the parameters

DO Variety frmMgtTBalByDate.Scx Linked

dValueDate = frmMgtTBalByDate.txtValueDate.Worth

cBranchCode = frmMgtTBalByDate.txtBranchCode.Worth




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

Create CURSOR MgtTBalByDate (AccountCode c(20),AccountName c(fifty),AccountType c(fifty),TBalDate D,MTDDebit Y,MTDCredit Y,YTDDebit Y,YTDCredit Y,UserName C(ten))

Decide on MgtTBalByDate



In the code earlier mentioned memory variables are declared that will be applied either to hold the user’s report standards or to retailer and transport data. The DIMENSION command generates an array that will be applied to return data from a Information Mindful Course that encapsulates the functionality of the ActGLDayTot table. Another detail to note is how we have permitted buyers to specify the data to be bundled in the report by entering the specified date for which they want a trial harmony. To accumulate this facts from the buyers, the line DO Variety frmMgtTBalByDate.Scx runs the report parameter type so buyers can enter standards. When  the consumer clicks the Alright button in that type, a THISFORM.Cover command briefly hides the type from the consumer while the strains instantly following the DO Variety command accumulate the facts on the type. The line frmMgtTBalByDate.Release then removes the type from memory.

Subsequent, the line oDT = CREATEOBJECT (‘ActGLDayTot’) generates an instance of the course ActGLDayTot and stores a reference to it in the object variable oDT. The line lAnswer = GetTransByDate… calls the system in the course to return the distinct data we want, passing the important parameters gathered type the consumer together with the array that will be applied to retailer and transport the data again to our report.

As we did before, we then use a Create CURSOR MgtTBalByDate command to make a cursor with the important fields. The line APPEND FROM ARRAY arrTR populates the table. The report is then produced to realize this cursor in its data setting with the line THIS.OpenTables().

By separating the code that does the selection and processing of facts from the Report’s very own Init occasion, we are ready to make this code readily available always type a number of locations mainly because it is contained in a data knowledgeable course. The code contained within just the GetTransByDate system of the ActGLDayTot course is as follows:

* Acquire Transaction By Day

PARAMETERS dTranDate,cBranchCode,arrTR,intNoOfRows,chrProgTitle

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

Community lGLDayTotInUse as Sensible

* Initialize your variables to the appropriate sorts to stay clear of any problems

Retail outlet “” TO cMsg

Retail outlet TO intRows

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

      cMsg = “You should enter transaction date!”

      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 Utilised(‘ActGLMast’)

      lGLMastInUse = .T.


      USE ActGLMast IN

      lGLMastInUse = .F.


Decide on ActGLMast

GO Major

IF Utilised(‘ActGLDayTot’)

      lGLDayTotInUse  = .T.


      USE ActGLDayTot IN

      lGLDayTotInUse = .F.


Decide on ActGLMast

GO Major

SCAN &cMsg

      intRows = intRows + 1

      DIMENSION arrTR(intRows,nine)   

      arrTR(intRows,1) = ActGLMast.AccountCode

      arrTR(intRows,2) = ActGLMast.AccountName

      arrTR(intRows,three) = ActGLMast.AccountType

      DO Case

            Case ActGLMast.CurrBal >

                  arrTR(intRows,seven) = ActGLMast.CurrBal

                  arrTR(intRows,eight) =

            Case ActGLMast.CurrBal < 0

                  arrTR(intRows,seven) =

                  arrTR(intRows,eight) = ActGLMast.CurrBal

            Or else

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

                  arrTR(intRows,eight) = 0  && YTD Credit rating


      Decide on ActGLDayTot

      GO Major

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

      Track down FOR ActGLDayTot.ValueDate = dTranDate

      IF Found()

                  DO Case

                        Case ActGLDayTot.Balance >

                              arrTR(intRows,5) = ActGLDayTot.Balance

                              arrTR(intRows,6) =

                        Case ActGLDayTot.Balance < 0

                              arrTR(intRows,5) =

                              arrTR(intRows,6) = ActGLDayTot.Balance

                        Or else

                              arrTR(intRows,5) =

                              arrTR(intRows,6) =



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

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



intNoOfRows = intRows

* Now near all tables you dont want


      USE IN ActGLMast



      USE IN ActGLDayTot



The PARAMETERS command that starts the system identifies the parameters handed to the course. The application makes use of a SCAN…ENDSCAN loop to go via the ActGLMast table to isolate all accounts that fulfill the standards and then to populate the array with matching data from the ActGlDayTot table. At the end, the application returns .T. if the system completes effectively. You could now operate this report with a REPORT Variety command just as we did either from a type or visual FoxPro menu.


Visible FoxPro’s data manipulation language is a person of the factors that can make Visible FoxPro standout amongst a lot of products and solutions in its course. This short article has shown how the richness of the Visible FoxPro language and enhancement setting permits a developer to compile the data desired for a report at operate time and even now be ready to really significantly management the report generation process. Even while this short article has assumed that the programmer is making a ‘pure fox’ software (after all, Visible FoxPro gives you really significantly every thing you want to establish entire strong data administration apps), with a very little tuning and adjustments, you can use this system to derive data from powerful SQL Servers such as ORACLE, Microsoft’s very own SQL Server or Gain Database Server for use within just a Visible FoxPro report. If you can consider it, Visible FoxPro lets you establish it.

[i] If you want to be ready to use the expression making to decide on from a list of fields working with a subject picker, then you may well have to variety a command in the command window that generates your cursor in progress in advance of continuing to structure the report designer. If you do this, the fields of the cursor will be readily available to you from the expression making. For illustration, you could variety the following in the command window:

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

At the time you have completed this, you will locate the fields of your cursor listed in the fields list on the expression builder, so you can just simply click the wanted subject to be additional to the report.

Comments are closed.