or SQL Find statement to the simply click function of a button or other code that will operate in advance of you issue a REPORT or LABEL command.
Even so, what transpires when the info for your report is not in a table that can be dynamically queried with a Find SQL statement nor is it in a variety that you can simply open up with a USE command! The Richness of the Visual FoxPro programming language will allow you to simply remedy this issue as this write-up will display. To display the concept staying reviewed, think about that you want to print or generate the Profits and Expense Report for your firm as at a given day! This would indicate that you want to keep a daily operating balance of just about every account in your Normal Ledger chart of accounts in a table that we shall maybe call ActGLDayTot. The sections that abide by will describe how to make these tables and their framework as perfectly as the code that generates the report.
Printing Reviews in Visual FoxPro
As presently observed, you want to make info obtainable for a report in advance of you progress to print the report. To print a report in Visual FoxPro, you will want to issue the REPORT Sort command. For the full syntax of the REPORT Sort command, see your Visual FoxPro nine on-line aid documentation.
If you issue the REPORT Sort command with no tables in the info surroundings and no info resources open up in the info surroundings of the report, the report simply seems to flash and then shut again! To stay clear of this problem, you will both want to incorporate info to the info surroundings or use the Init function of the info surroundings to make info obtainable in just the info surroundings prior to printing. When the info does not presently exist in a query or in a variety that can be directly queried, what will you do?
Creating Details Obtainable on the Fly
Simply because info have to be first created obtainable prior to operating a report, you will want to issue an SQL Find statement or open up a table at minimum in the Init function of the Details Surroundings of the report. Even so, if your info does not exist in a variety in which you can simply create a watch about it or a query about it and if it does not exist in a variety in which it can be additional as a table then neither of the procedures reviewed so far will yield the sought after benefits!
This usually means that you have to locate a new way of producing the info obtainable to your plan. A nearer evaluation of how you would use the SQL Find statement offers an perception into how you could do this. The common variety of the syntax of the SQL Find Assertion we want to take into consideration is as follows:
Find [ALL | Unique] [Major nExpr [P.c]] Find_Record_Item [, …]
FROM [Power] Desk_Record_Item [, …]
[Where by JoinCondition | FilterCondition [AND | OR JoinCondition | FilterCondition] …]
[INTO StorageDestination | TO DisplayDestination]
This variety of the syntax suggests that the SQL Find statement produces a query and then fetches the essential details from the table into a storage desired destination most ordinarily, a cursor (temporary table), table or array. The cursor symbolizing storage desired destination is then utilised as the source of info for the report. Based on this actuality, we can deduce that our report would operate if we produced a cursor item making use of the Generate CURSOR or a temporary table making use of Generate Desk in the Init function of the info surroundings. This would make info obtainable to the report and would so avert the report from again closing quickly upon staying opened.
How would this basically perform in exercise?
one. Generate your Report: 1st off, you would have to make a report a report in the Report Designer. You can do this by picking out the Reviews Node in the Undertaking Supervisor and then picking out the New button. When the New Report dialog box shows, you would then decide on New Report. Visual FoxPro would then make a new blank report named Report1 and open up it in the Report Designer
2. Layout your Report: Now that the report designer is open up, you can start laying out your report. Generally, you would do this by dragging fields from the info surroundings unto the report canvas. But in this situation, you have no info in the info surroundings so you have to set the attributes of the report manually. You have to therefore display screen the Reviews Controls Toolbar by picking out Reviews Handle Toolbar from the Watch primary menu. The moment the toolbar shows, you can layout your report area by performing the pursuing action:
a. Find a handle from the toolbox to be positioned on your report. If you pick a handle these types of as a textual content box handle and then simply click on the detail band of the report, Visual FoxPro mechanically opens the Area Attributes dialog box so you can set not only the source of info for the industry but also other traits of the industry.
b. In the Expression box on the Normal tabbed page, enter the expression that will server as a source of info for the report. You will enter it in the structure cursorname.fieldname where Cursorname will signify the title of the cursor that you will make (of program you have not produced it still usually you would simply have picked out the … button to allow for you pick it from the expression builder[i]). If you simply click a handle these types of as a Label handle, put it everywhere on the report and then type where the Insertion level seems to incorporate a descriptive label. For instance, simply click a label handle on the report and then incorporate a descriptive label for just about every textual content box in the Site header. You can incorporate descriptions these types of as Area One and Area Two. You can incorporate a report title (e.g. Test Report) by clicking on the Label handle, and then clicking at the correct posture on the Report Header. To set kinds and font dimensions for your captions, ideal-simply click the correct caption, decide on Attributes and then decide on the Style tabbed page. In the Style tabbed page, you can make the correct options beneath the Font box or make any other options as vital. Don’t forget to preserve your perform as you go alongside.
c. Now that you have gotten fields on to your report, you can use the common formatting to the report these types of as calculated fields, traces, and so on
3. Set Attributes for the Details Surroundings: You will even now want to avert your report from mechanically shutting down when you consider to operate it so you have to set attributes for the info surroundings as follows:
a. Appropriate-simply click your report and then decide on the Details Surroundings menu command from the shortcut menu that shows. The Details Surroundings – Report Designer opens. It is vacant, showing that no info resources have been additional to it.
b. Appropriate-Simply click within the Details Surroundings and then decide on the Attributes menu command to display screen the attributes sheet for the Details Surroundings.
c. Set the AutoOpenTables home to phony. When you set this home to phony, the report does not attempt to open up the tables or views in the info surroundings upon operating of the report. If this were to come about, the report would simply locate that the info surroundings did not include any info and then the report would shut abruptly. You are now ready to incorporate code for the report as described in the following stage (stage 4 – Producing code to make info obtainable).
4. Generate Code to make Details Obtainable: Don’t forget that the info is to be fetched or created obtainable only at operate time. So you have to now produce the code that will make this info obtainable for your report. You will do this in the Init function of the info surroundings by creating code these types of as:
a. Identify the Init function of the info surroundings in the attributes sheet and then double-simply click it to open up the code window.
b. In the code window, type the code that produces your cursor by making use of the Generate CURSOR command and also produce code to incorporate quite a few data to the cursor by making use of the APPEND command. This code could search as follows:
Generate CURSOR Testcursor (Field1 c(10) special,Field2 i)
Exchange TestCursor.Field1 WITH “Test”
Exchange TestCursor.Field2 WITH 50
Exchange TestCursor.Field1 WITH “TEST2”
Exchange TestCursor.Field2 WITH one hundred
Exchange TestCursor.Field1 WITH “TEST3”
Exchange TestCursor.Field2 WITH 150
c. Shut the code window and shut the info surroundings designer
d. Quickly examination your report by picking out the Print Preview button on the toolbar. The report should really open up in Print Preview manner.
five. Conserve and Run your Report: Now that you have produced your report and created info at operate time, you will want to preserve the report and then to operate it. You can examination no matter if your report will operate by performing the pursuing action:
a. Pick out the Conserve button on the toolbar to be certain that you have saved your report.
b. Shut the report by picking out the Shut button. The report seems beneath the Reviews node in the Undertaking Supervisor.
c. To operate this report, pick the report (Report1) in the challenge supervisor and then decide on the Preview button. The report opens in print preview manner.
six. Jogging Your Report By way of the User Interface: You would generally make your report obtainable to the consumers of your application via the application consumer interface. For instance, you may possibly give a variety via which consumers can pick your report and then push 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 consumer interface, you will have to perform the pursuing action:
a. Find the Kinds node on the Files tabbed page of the Visual FoxPro challenge supervisor and then decide on the New button. The New Sort dialog box shows.
b. Pick out the New Sort button. Visual FoxPro produces a new variety named Form1 and opens it in the Sort Designer.
c. Display the Kinds Handle Toolbox if it is not presently exhibited by picking out the Sort Controls Toolbar menu on the watch menu.
d. Find the Command Button handle on the Kinds Handle Toolbox and then simply click on the variety. Visual FoxPro will make a new command button named Command1.
e. Double-Simply click Command1 button to display screen the Code window for its Simply click function.
f. You can now enter the pursuing code:
REPORT Sort Report4.frx NOCONSOLE PREVIEW
g. Simply click the Conserve button on the button bar to preserve the variety you have produced.
h. Run the variety by both pressing Ctrl + E or by picking out the Run toolbar button on the toolbar. The variety runs.
i. Now simply click the command button to operate the report. The report seems in print preview window.
Making use of the concept to a True Existence Scenario
The instance presently illustrated has been stored deliberately easy. Now, this have to be applied in a actual-everyday living problem. In the instance shown, the cursor is populated by issuing APPEND BLANK instructions. In a actual-everyday living application on the other hand, it may possibly be probable that your info may possibly presently exist, needing only to be go through again and re-arranged into a structure that your report can print. Lets illustrate this by building a Trial Stability Report as at a given day (Trial balances are ordinarily printed as at a given interval finish date).
To be in a position to print these types of a path balance, we would have to know the balance of an account as at a given day. Lets now accept that these types of info is saved day-by-day as transactions are handed in a table named ActGLDayTot and this table could be produced with the pursuing SQL Assertion:
Generate Desk ActGLDayTot(BatchNo c(20) Primary Crucial,AccountCode c(fifteen),
ValueDate D,CurrYear i,NextYear i,PeriodNumb i,MonthNumb i,Debitamt Y,
CreditAmt Y,Stability Y)
For just about every account in the Normal Ledger Master file, we want to obtain the account’s balances as at the specified day so that we will be in a position to create the demo balance. Now think about that your Normal Ledger Master table could be produced with the pursuing SQL Assertion:
Generate Desk ActGLMast(AccountCode c(fifteen) Primary Crucial,AccountName c(50),
AccountType c(forty),CurrBal Y)
The pursuing code is positioned in the Init Occasion of the info surroundings of our report:
* This Code Constructs the cursor utilised in the Report
Neighborhood dValueDate AS Day,cBranchCode aS Character,intNoOfRows as Integer,oDT as Item ,lAnswer as Sensible
Keep “” TO cBranchCode
Keep ( / / ) TO dValueDate
Keep TO intNoOfRows
* one) Open up the variety and obtain the parameters
DO Sort frmMgtTBalByDate.Scx Joined
dValueDate = frmMgtTBalByDate.txtValueDate.Benefit
cBranchCode = frmMgtTBalByDate.txtBranchCode.Benefit
oDT = CREATEOBJECT(‘ActGLDayTot’)
lAnswer = oDT.GetTransByDate(dValueDate,cBranchCode,arrTR,intNoOfRows,chrProgTitle)
Generate CURSOR MgtTBalByDate (AccountCode c(20),AccountName c(50),AccountType c(50),TBalDate D,MTDDebit Y,MTDCredit Y,YTDDebit Y,YTDCredit Y,UserName C(10))
APPEND FROM ARRAY arrTR
In the code above memory variables are declared that will be utilised both to hold the user’s report conditions or to keep and transportation info. The DIMENSION command produces an array that will be utilised to return info from a Details Informed Course that encapsulates the features of the ActGLDayTot table. One more matter to observe is how we have authorized consumers to specify the info to be provided in the report by moving into the specified date for which they want a demo balance. To acquire this details from the consumers, the line DO Sort frmMgtTBalByDate.Scx runs the report parameter variety so consumers can enter conditions. When the consumer clicks the Okay button in that variety, a THISFORM.Disguise command briefly hides the variety from the consumer whilst the traces quickly pursuing the DO Sort command acquire the details on the variety. The line frmMgtTBalByDate.Release then removes the variety from memory.
Upcoming, the line oDT = CREATEOBJECT (‘ActGLDayTot’) produces an occasion of the class ActGLDayTot and stores a reference to it in the item variable oDT. The line lAnswer = GetTransByDate… calls the strategy in the class to return the certain info we want, passing the vital parameters collected variety the consumer alongside with the array that will be utilised to keep and transportation the info again to our report.
As we did previously, we then use a Generate CURSOR MgtTBalByDate command to make a cursor with the vital fields. The line APPEND FROM ARRAY arrTR populates the table. The report is then created to acknowledge this cursor in its info surroundings with the line THIS.OpenTables().
By separating the code that does the assortment and processing of details from the Report’s very own Init function, we are in a position to make this code obtainable constantly variety many places due to the fact it is contained in a info informed class. The code contained in just the GetTransByDate strategy of the ActGLDayTot class is as follows:
* Obtain Transaction By Day
Neighborhood cMsg AS Character,intRows AS Integer,lGLMastInUse AS Sensible
Neighborhood lGLDayTotInUse as Sensible
* Initialize your variables to the right styles to stay clear of any faults
Keep “” TO cMsg
Keep TO intRows
IF Style(‘dTranDate’) <> ‘D’
cMsg = “You have to enter transaction date!”
IF Style(‘intNoOfRows’) <> “N”
IF Style(‘arrTR’,one) <> “A”
cMsg = “Array of Transactions not uncovered!”
IF Style(‘cBranchCode’) <> “C”
cMsg = “ALL”
cMsg = ” FOR BranchCode = ‘” + cBranchCode + “‘”
lGLMastInUse = .T.
USE ActGLMast IN
lGLMastInUse = .F.
lGLDayTotInUse = .T.
USE ActGLDayTot IN
lGLDayTotInUse = .F.
intRows = intRows + one
arrTR(intRows,one) = ActGLMast.AccountCode
arrTR(intRows,2) = ActGLMast.AccountName
arrTR(intRows,3) = ActGLMast.AccountType
Case ActGLMast.CurrBal >
arrTR(intRows,7) = ActGLMast.CurrBal
Case ActGLMast.CurrBal < 0
arrTR(intRows,8) = ActGLMast.CurrBal
arrTR(intRows,7) = 0 && YTD Debit
arrTR(intRows,8) = 0 && YTD Credit score
*Identify FOR ALLTRIM(ActGLDayTot.AccountCode) = ALLTRIM(cAccountCode) AND ActGLDayTot.ValueDate = dTranDate
Identify FOR ActGLDayTot.ValueDate = dTranDate
Case ActGLDayTot.Stability >
arrTR(intRows,five) = ActGLDayTot.Stability
Case ActGLDayTot.Stability < 0
arrTR(intRows,six) = ActGLDayTot.Stability
arrTR(intRows,five) = 0 && MTD Debit
arrTR(intRows,six) = 0 && MTD Credit score
intNoOfRows = intRows
* Now shut all tables you dont want
IF NOT lGLMastInUse
USE IN ActGLMast
IF NOT lGLDayTotInUse
USE IN ActGLDayTot
The PARAMETERS command that commences the strategy identifies the parameters handed to the class. The plan works by using a SCAN…ENDSCAN loop to go via the ActGLMast table to isolate all accounts that meet up with the conditions and then to populate the array with matching info from the ActGlDayTot table. At the finish, the plan returns .T. if the strategy completes efficiently. You could now operate this report with a REPORT Sort command just as we did both from a variety or visual FoxPro menu.
Visual FoxPro’s info manipulation language is 1 of the points that will make Visual FoxPro standout among several products in its class. This write-up has shown how the richness of the Visual FoxPro language and enhancement surroundings will allow a developer to compile the info wanted for a report at operate time and even now be in a position to fairly a lot handle the report era course of action. Even nevertheless this write-up has assumed that the programmer is building a ‘pure fox’ application (after all, Visual FoxPro provides you fairly a lot every thing you want to create comprehensive strong info administration programs), with a very little tuning and alterations, you can use this strategy to derive info from powerful SQL Servers these types of as ORACLE, Microsoft’s very own SQL Server or Benefit Databases Server for use in just a Visual FoxPro report. If you can think about it, Visual FoxPro lets you create it.
[i] If you want to be in a position to use the expression building to pick from a record of fields making use of a industry picker, then you may possibly have to type a command in the command window that produces your cursor in progress in advance of proceeding to layout the report designer. If you do this, the fields of the cursor will be obtainable to you from the expression building. For instance, you could type the pursuing in the command window:
Generate CURSOR Testcursor (Field1 c(10) special,Field2 i)
The moment you have accomplished this, you will locate the fields of your cursor shown in the fields record on the expression builder, so you can just simply click the sought after industry to be additional to the report.