660 likes | 814 Views
Advanced Reports. Dr. Peter Wayne. Here is the set we will use for the report. There is a 1:many link on the 'transactionid' field. Always begin your report with the Quick Report Genie. We will group by the linking field of the parent table. Add fields from the child table. Grouping.
E N D
Advanced Reports Dr. Peter Wayne
Move parent table fields from the Detail section to the Group section. Move child table fields to the left in the Detail section. Shorten all the lines created by the genie and make the report 8.5" wide. You now should have a report that looks like the one in the next slide. Save the report under a name, before any accident makes you lose all your work!
Now, let's add parameters to the report. • We'll ask the user for • a beginning date • an ending date • whether to include all transactions, or only reconciled transactions (status="R") We'll ask for these parameters in the OnPrintInitevent of the report.
Here, I choose ls_begin_date as the variable name. I specify that it is of type "Date", that its width is 12, and I will use a calendar control to enter the date.
End date is similar. A 3rd variable, "ls_include", specifies whether to include all transactions or just reconciled ones, and uses radio buttons for input.
This is important -- specify that the variables in this script are shared.
Insert generated code into code editor and modify it. In my case, remove the extra quotation marks added by the genie around the default for ls_include in the line, ls_include=""all transactions""
The xdialog returns 4 values: 1) varC_result contains "OK" or "Cancel" 2) ls_start_date, ls_end_date, and ls_include are all shared variables. If the user presses "Cancel", we want to abort the printing. A5v5's OnPrintInit will abort printing if a cancel() is issued. So let's add a few lines to the end of the Xdialog:
Now we use the variables from our Xdialog to create a filter that can be used in the Detail, Records specification of the report. Add these lines of code: dim shared flter as c flter="between(date,{"+dtoc(ls_begin_date)+"},{"+\ dtoc(ls_end_date)+"})" if ls_include="only reconciled transactions" then flter=flter+" .and. status='R'" end Add flter to the variables for the report:
Place the flter variable in the Report's Detail Properties section.
Make eval(flter) the filter expression for the Detail section's Records property. Change "Base report on current selection of records" to "None" in Additional Record Order and Filter Criteria.
We generate a very respectable xdialog when the report is previewed:
Since we made the different ls_xxx variables shared variables, we can put them on the report for a more professional appearance: Here I have added them to the form's variable definition.
Putting the variables on the report makes the report self-documenting.
The subpayee in the child is often the same as the payee in the parent record. We can use a conditional object to suppress the superfluous display of the subpayee field. Here I have put a conditional object around the subpayee field.
The first condition is Payee=Mycheckdetail->Subpayee
The default condition is to show the subpayee, but if the subpayee is the same as the payee, then the field is suppressed. A similar choice is made with the subamount field:
Notice that repetition of the subpayee and subpayment is now suppressed.
Make one report do the work of two with a conditional object. We'll create an Account Summary report to summarize transactions by account. Since the account field is part of the detail record, we first need to create a set in which the detail items are primary. Create a 1:1 link from mycheckdetail to checkbook:
Then create a Quick Report with Grouping by the Account field in Mycheckdetail:
Add a session-level logical variable, show_detail, to the report definition:
Place a conditional object around the entire set of fields in the Detail section of the report.
Check "Object can shrink" in the properties for the conditional object: The condition is "var->show_detail=.f."
Check "Shrink contents of band" in the properties for the entire Detail section: