Populating Visible FoxPro Reviews with facts on the Fly

Summary:

Visible FoxPro’s report writer is a highly effective total-showcased report writer that supplies all the tools you need to build and ship persuasive reviews with your application. On Most occasions, you can build Visible FoxPro reviews primarily based only on facts that now exists (e.g. an current desk, query or perspective). On the other hand, picture that the facts you need to populate your report with does not exist as a desk that can be readily added to your report’s facts surroundings nor does it exist as a set of tables on which you can execute a straight-ahead SQL Question or perspective! This post describes how you can populate a Visible FoxPro report at operate time by gathering report requirements from buyers, deriving the facts from your tables primarily based on that requirements, generating a cursor and then populating the cursor with the derived facts all at operate time.

Who need to browse this post?

This post assumes some familiarity with developing and acquiring programs with Microsoft Visible FoxPro 9

Introduction:

1 of the crucial gains of making use of Visible FoxPro as your principal progress resource is that it supplies all of the tools you need to construct highly effective, persuasive total-showcased programs that are speedy and feature rich!

1 of the characteristics of Visible FoxPro that makes this doable is the newly revamped report writer that ships with Visible FoxPro 9! Since of the need to offer or make facts accessible for your report or label in advance of you print it, the Report Writer supplies a wide variety of strategies to make facts quickly accessible for your report.

1 approach of building facts accessible for your report and incidentally the most generally employed is to produce a report that is often primarily based on the exact same facts sources. This approach would essentially require you to insert tables or sights to the facts surroundings of a report or to use a DO command in the Init celebration of the report’s facts surroundings. Alternatively, you could execute a query by generating an executing an SQL Statement in the Init celebration of the report’s facts surroundings.

A second approach is when you need to build a report that utilizes separate sets of facts sources for the report. In this circumstance, you would dynamically open those people facts sources at operate time by making use of a USE

, USE , DO or SQL Select assertion to the simply click celebration of a button or other code that will operate in advance of you challenge a REPORT or LABEL command.

On the other hand, what occurs when the facts for your report is not in a desk that can be dynamically queried with a Select SQL assertion nor is it in a sort that you can simply just open with a USE command! The Richness of the Visible FoxPro programming language will allow you to quickly clear up this problem as this post will display. To display the thought remaining talked over, picture that you need to print or generate the Earnings and Cost Report for your enterprise as at a offered day! This would suggest that you need to preserve a everyday operating equilibrium of each account in your Standard Ledger chart of accounts in a desk that we shall perhaps get in touch with ActGLDayTot. The sections that adhere to will explain how to build these tables and their framework as nicely as the code that generates the report.

Printing Reviews in Visible FoxPro

As now observed, you need to make facts accessible for a report in advance of you move forward to print the report. To print a report in Visible FoxPro, you will need to challenge the REPORT Type command. For the total syntax of the REPORT Type command, see your Visible FoxPro 9 on-line assist documentation.

If you challenge the REPORT Type command with no tables in the facts surroundings and no facts sources open in the facts surroundings of the report, the report simply just seems to flash and then shut yet again! To prevent this situation, you will both need to insert facts to the facts surroundings or use the Init celebration of the facts surroundings to make facts accessible within just the facts surroundings prior to printing. When the facts does not now exist in a query or in a sort that can be straight queried, what will you do?

Making Facts Available on the Fly

Since facts have to be very first produced accessible prior to operating a report, you will need to challenge an SQL Select assertion or open a desk at minimum in the Init celebration of the Facts Surroundings of the report. On the other hand, if your facts does not exist in a sort in which you can quickly construct a perspective 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 added as a desk then neither of the procedures talked over so far will generate the ideal success!

This indicates that you have to discover a new way of building the facts accessible to your method. A closer assessment of how you would use the SQL Select assertion supplies an insight into how you could do this. The standard sort of the syntax of the SQL Select Statement we want to look at is as follows:

 

Select [ALL | Distinctive] [Top nExpr [P.c]] Select_Record_Product [, …]

   FROM [Force] Desk_Record_Product [, …]

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

   [INTO StorageDestination | TO DisplayDestination]

This sort of the syntax implies that the SQL Select assertion creates a query and then fetches the needed data from the desk into a storage spot most normally, a cursor (momentary desk), desk or array. The cursor symbolizing storage spot is then employed as the supply of facts for the report. Based mostly on this actuality, we can deduce that our report would operate if we established a cursor object making use of the Develop CURSOR or a momentary desk making use of Develop Desk in the Init celebration of the facts surroundings. This would make facts accessible to the report and would thus prevent the report from yet again closing instantly on remaining opened.

How would this essentially get the job done in observe?

1.       Develop your Report: First off, you would have to build a report a report in the Report Designer. You can do this by deciding on the Reviews Node in the Job Manager and then deciding on the New button. When the New Report dialog box displays, you would then select New Report. Visible FoxPro would then build a new blank report named Report1 and open it in the Report Designer

two.       Layout your Report: Now that the report designer is open, you can begin laying out your report. Commonly, you would do this by dragging fields from the facts surroundings unto the report canvas. But in this circumstance, you have no facts in the facts surroundings so you have to set the properties of the report manually. You have to for that reason display screen the Reviews Controls Toolbar by deciding on Reviews Control Toolbar from the Perspective most important menu. At the time the toolbar displays, you can structure your report surface by performing the adhering to motion:

a.      Select a manage from the toolbox to be put on your report. If you decide on a manage these types of as a textual content box manage and then simply click on the depth band of the report, Visible FoxPro instantly opens the Industry Attributes dialog box so you can set not only the supply of facts for the discipline but also other qualities of the discipline.

b.      In the Expression box on the Standard 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 wherever Cursorname will depict the name of the cursor that you will build (of class you have not established it but in any other case you would simply just have preferred the … button to allow you decide on it from the expression builder[i]). If you simply click a manage these types of as a Label manage, area it anywhere on the report and then kind wherever the Insertion point seems to insert a descriptive label. For example, simply click a label manage on the report and then insert a descriptive label for each textual content box in the Website page header. You can insert descriptions these types of as Industry 1 and Industry Two. You can insert a report title (e.g. Check Report) by clicking on the Label manage, and then clicking at the suitable position on the Report Header. To set variations and font measurements for your captions, ideal-simply click the suitable caption, select Attributes and then select the Style tabbed website page. In the Style tabbed website page, you can make the suitable settings under the Font box or make any other settings as essential. Don’t forget to save your get the job done as you go alongside.

c.       Now that you have gotten fields on to your report, you can implement the usual formatting to the report these types of as calculated fields, strains, and so on

3.       Set Attributes for the Facts Surroundings: You will still want to prevent your report from instantly shutting down when you try out to operate it so you have to set properties for the facts surroundings as follows:

a.      Correct-simply click your report and then select the Facts Surroundings menu command from the shortcut menu that displays. The Facts Surroundings – Report Designer opens. It is vacant, displaying that no facts sources have been added to it.

b.      Correct-Simply click inside the Facts Surroundings and then select the Attributes menu command to display screen the properties sheet for the Facts Surroundings.

c.       Set the AutoOpenTables house to bogus. When you set this house to bogus, the report does not try to open the tables or sights in the facts surroundings on operating of the report. If this were to occur, the report would simply just discover that the facts surroundings did not incorporate any facts and then the report would shut abruptly. You are now all set to insert code for the report as described in the following action (action 4 – Crafting code to make facts accessible).

4.       Write Code to make Facts Available: Don’t forget that the facts is to be fetched or produced accessible only at operate time. So you have to now produce the code that will make this facts accessible for your report. You will do this in the Init celebration of the facts surroundings by writing code these types of as:

a.      Identify the Init celebration of the facts surroundings in the properties sheet and then double-simply click it to open the code window.

b.      In the code window, kind the code that creates your cursor by making use of the Develop CURSOR command and also produce code to insert numerous records to the cursor by making use of the APPEND command. This code could glimpse as follows:

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

APPEND BLANK

Change TestCursor.Field1 WITH “Check”

Change TestCursor.Field2 WITH fifty

APPEND BLANK

Change TestCursor.Field1 WITH “TEST2”

Change TestCursor.Field2 WITH 100

APPEND BLANK

Change TestCursor.Field1 WITH “TEST3”

Change TestCursor.Field2 WITH one hundred fifty

 

c.       Near the code window and shut the facts surroundings designer

d.      Swiftly exam your report by deciding on the Print Preview button on the toolbar. The report need to open in Print Preview mode.

five.       Help save and Run your Report: Now that you have established your report and created facts at operate time, you will need to save the report and then to operate it. You can exam regardless of whether your report will operate by performing the adhering to motion:

a.      Decide on the Help save button on the toolbar to assure that you have saved your report.

b.      Near the report by deciding on the Near button. The report seems under the Reviews node in the Job Manager.

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

six.       Jogging Your Report As a result of the User Interface: You would commonly make your report accessible to the buyers of your application as a result of the application consumer interface. For example, you may perhaps offer a sort as a result of which buyers can decide on your report and then press both a preview or a print button. To do this, you will have to use the REPORT Type command to operate the report programmatically. If you want your report to be accessible as a result of the application consumer interface, you will have to perform the adhering to motion:

a.      Select the Varieties node on the Paperwork tabbed website page of the Visible FoxPro undertaking supervisor and then select the New button. The New Type dialog box displays.

b.      Decide on the New Type button. Visible FoxPro creates a new sort named Form1 and opens it in the Type Designer.

c.       Exhibit the Varieties Control Toolbox if it is not now exhibited by deciding on the Type Controls Toolbar menu on the perspective menu.

d.      Select the Command Button manage on the Varieties Control Toolbox and then simply click on the sort. Visible FoxPro will build a new command button known as Command1.

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

f.        You can now enter the adhering to code:

REPORT Type Report4.frx NOCONSOLE PREVIEW

 

g.      Simply click the Help save button on the button bar to save the sort you have established.

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

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

Implementing the thought to a Authentic Existence Situation

The example now illustrated has been retained deliberately very simple. Now, this have to be utilized in a genuine-lifestyle situation. In the example proven, the cursor is populated by issuing APPEND BLANK commands. In a genuine-lifestyle application on the other hand, it may perhaps be doable that your facts may perhaps now exist, needing only to be browse back and re-arranged into a format that your report can print. Allows illustrate this by developing a Demo Equilibrium Report as at a offered day (Demo balances are normally printed as at a offered period of time end date).

To be equipped to print these types of a path equilibrium, we would have to know the equilibrium of an account as at a offered day. Allows now acknowledge that these types of facts is stored day-by-day as transactions are passed in a desk known as ActGLDayTot and this desk could be established with the adhering to SQL Statement:

Develop Desk ActGLDayTot(BatchNo c(twenty) Primary Crucial,AccountCode c(15),

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

CreditAmt Y,Equilibrium Y)

For each account in the Standard Ledger Grasp file, we want to acquire the account’s balances as at the specified day so that we will be equipped to construct the trial equilibrium. Now picture that your Standard Ledger Grasp desk could be established with the adhering to SQL Statement:

Develop Desk ActGLMast(AccountCode c(15) Primary Crucial,AccountName c(fifty),

AccountType c(forty),CurrBal Y)

The adhering to code is put in the Init Event of the facts surroundings of our report:

* This Code Constructs the cursor employed in the Report

Regional dValueDate AS Date,cBranchCode aS Character,intNoOfRows as Integer,oDT as Item ,lAnswer as Logical

DIMENSION arrTR(1,9)

Retail store “” TO cBranchCode

Retail store (  /  /    ) TO dValueDate

Retail store TO intNoOfRows

* 1) Open the sort and acquire the parameters

DO Type frmMgtTBalByDate.Scx Joined

dValueDate = frmMgtTBalByDate.txtValueDate.Price

cBranchCode = frmMgtTBalByDate.txtBranchCode.Price

frmMgtTBalbyDate.Launch

*OPENTABLES()

oDT = CREATEOBJECT(‘ActGLDayTot’)

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

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

Select MgtTBalByDate

APPEND FROM ARRAY arrTR

This.OpenTables()

In the code higher than memory variables are declared that will be employed both to hold the user’s report requirements or to keep and transport facts. The DIMENSION command creates an array that will be employed to return facts from a Facts Informed Class that encapsulates the features of the ActGLDayTot desk. An additional point to take note is how we have authorized buyers to specify the facts to be bundled in the report by entering the specified date for which they want a trial equilibrium. To obtain this data from the buyers, the line DO Type frmMgtTBalByDate.Scx operates the report parameter sort so buyers can enter requirements. When  the consumer clicks the Okay button in that sort, a THISFORM.Conceal command briefly hides the sort from the consumer even though the strains instantly adhering to the DO Type command obtain the data on the sort. The line frmMgtTBalByDate.Launch then gets rid of the sort from memory.

Next, the line oDT = CREATEOBJECT (‘ActGLDayTot’) creates an occasion of the class ActGLDayTot and outlets a reference to it in the object variable oDT. The line lAnswer = GetTransByDate… calls the approach in the class to return the precise facts we want, passing the essential parameters collected sort the consumer alongside with the array that will be employed to keep and transport the facts back to our report.

As we did earlier, we then use a Develop CURSOR MgtTBalByDate command to build a cursor with the essential fields. The line APPEND FROM ARRAY arrTR populates the desk. The report is then produced to understand this cursor in its facts surroundings with the line THIS.OpenTables().

By separating the code that does the selection and processing of data from the Report’s personal Init celebration, we are equipped to make this code accessible often sort many destinations mainly because it is contained in a facts mindful class. The code contained within just the GetTransByDate approach of the ActGLDayTot class is as follows:

* Receive Transaction By Date

PARAMETERS dTranDate,cBranchCode,arrTR,intNoOfRows,chrProgTitle

Regional cMsg AS Character,intRows AS Integer,lGLMastInUse AS Logical

Regional lGLDayTotInUse as Logical

* Initialize your variables to the suitable types to prevent any problems

Retail store “” TO cMsg

Retail store TO intRows

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

      cMsg = “You have to enter transaction date!”

      MESSAGEBOX(cMsg,48,chrProgTitle)

      RETURN .F.

ENDIF

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

      RETURN .F.

ENDIF

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

      cMsg = “Array of Transactions not observed!”

      MESSAGEBOX(cMsg,48,chrProgTitle)

      RETURN .F.

ENDIF

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

      cMsg = “ALL”

ELSE

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

ENDIF

IF Employed(‘ActGLMast’)

      lGLMastInUse = .T.

ELSE

      USE ActGLMast IN

      lGLMastInUse = .F.

ENDIF

Select ActGLMast

GO Top

IF Employed(‘ActGLDayTot’)

      lGLDayTotInUse  = .T.

ELSE

      USE ActGLDayTot IN

      lGLDayTotInUse = .F.

ENDIF

Select ActGLMast

GO Top

SCAN &cMsg

      intRows = intRows + 1

      DIMENSION arrTR(intRows,9)   

      arrTR(intRows,1) = ActGLMast.AccountCode

      arrTR(intRows,two) = ActGLMast.AccountName

      arrTR(intRows,3) = ActGLMast.AccountType

      DO Case

            Case ActGLMast.CurrBal >

                  arrTR(intRows,7) = ActGLMast.CurrBal

                  arrTR(intRows,8) =

            Case ActGLMast.CurrBal < 0

                  arrTR(intRows,7) =

                  arrTR(intRows,8) = ActGLMast.CurrBal

            Usually

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

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

      ENDCASE

      Select ActGLDayTot

      GO Top

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

      Identify FOR ActGLDayTot.ValueDate = dTranDate

      IF Found()

                  DO Case

                        Case ActGLDayTot.Equilibrium >

                              arrTR(intRows,five) = ActGLDayTot.Equilibrium

                              arrTR(intRows,six) =

                        Case ActGLDayTot.Equilibrium < 0

                              arrTR(intRows,five) =

                              arrTR(intRows,six) = ActGLDayTot.Equilibrium

                        Usually

                              arrTR(intRows,five) =

                              arrTR(intRows,six) =

                  ENDCASE

      ELSE

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

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

      ENDIF

ENDSCAN

intNoOfRows = intRows

* Now shut all tables you dont need

IF NOT lGLMastInUse

      USE IN ActGLMast

ENDIF

IF NOT lGLDayTotInUse

      USE IN ActGLDayTot

ENDIF

RETURN .T.

The PARAMETERS command that commences the approach identifies the parameters passed to the class. The method utilizes a SCAN…ENDSCAN loop to go as a result of the ActGLMast desk to isolate all accounts that satisfy the requirements and then to populate the array with matching facts from the ActGlDayTot desk. At the end, the method returns .T. if the approach completes effectively. You could now operate this report with a REPORT Type command just as we did both from a sort or visual FoxPro menu.

Summary

Visible FoxPro’s facts manipulation language is one of the items that makes Visible FoxPro standout among a lot of products in its class. This post has shown how the richness of the Visible FoxPro language and progress surroundings will allow a developer to compile the facts essential for a report at operate time and still be equipped to rather substantially manage the report generation process. Even while this post has assumed that the programmer is developing a ‘pure fox’ application (immediately after all, Visible FoxPro gives you rather substantially every little thing you need to construct finish robust facts administration programs), with a tiny tuning and modifications, you can use this approach to derive facts from highly effective SQL Servers these types of as ORACLE, Microsoft’s personal SQL Server or Gain Databases Server for use within just a Visible FoxPro report. If you can picture it, Visible FoxPro allows you construct it.

[i] If you want to be equipped to use the expression developing to decide on from a record of fields making use of a discipline picker, then you may perhaps have to kind a command in the command window that creates your cursor in advance in advance of proceeding to structure the report designer. If you do this, the fields of the cursor will be accessible to you from the expression developing. For example, you could kind the adhering to in the command window:

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

At the time you have performed this, you will discover the fields of your cursor shown in the fields record on the expression builder, so you can just simply click the ideal discipline to be added to the report.