50.6.1 The following steps illustrate how to create a simple custom report.
In this example, the aim is to create a Christmas mailing list of all/selected Clients. The Business Contacts txtUserField11 is used to identify whether a Client is included in the Christmas mailing list or not. Further, the report must have an option to include all contacts or only the main contact for the selected Client.
You can rename txtUserField11 as 'xmas mailing list?'.
For more information on how to rename a field, refer to "How do I Customise Fields on Screens and Reports" Category in this Guide.
50.6.2 Identify the Fields and Tables
Whether your report is a simple listing of records or a grouped summary, you must first determine which fields contain the data you want to see in your report, and in which tables or queries (datasource) they reside.
If a custom report involves one datasource then select Table. Usually if all the information you need is only on one form, such as Business Contacts, then you only need to identify the Table.
If a custom report requires two or more datasource then you need to create a Query.
In this example, it involves two datasource tblClients (Business Contacts) and tblClientsContacts (Contacts). The fields include the Contacts name and Postal address
50.6.3 Select a Table or Create a Query
Click on the Navigation Pane on the extreme left to view All Access Objects. (Tables, Queries, Forms, Reports, Pages, Macros and Modules)
Click All Access Objects
Select Query
Click the Create tab (from the top)
Click Query Wizard
Select Simple Query Wizard and Click OK
Where it says Tables/Queries, dropdown list and select Table: tblClients
The Available Fields will show all the fields for the Client table
Click each field you want to include and then press the '>' button so that the field appears on the Selected Fields. Select the following:
Company Name
Postal Address
Postal State
Postal PostCode
bolUserField11
Go back to Tables/Queries dropdown list and select Table: tblClientsContacts
Click each field you want to include and press the '>' button so that the field appears on the Selected Fields. Select the following:
ContactSalutation
ContactGivenName
ContactSurname
Click Next twice
When it prompts 'What title do you want for your query?', type xmasMailingListQuery
Tick Modify Query by design and click Finish
The Query design screen shows the tables and fields selected
Go to the column 'bolUserField11'
On the Criteria row enter [Enter 0 for all or -1 for selected Clients only]
Click File -> Save
Close the Design view to go back to the LatiReporter main switchboard
50.6.4 Add parameters in queries and reports
When running a report in Latitude, dialog boxes appear to ask users to select criteria. These dialog boxes are complex to design and require some programming. However, you can create your own simple dialog boxes using parameters to ask users to enter their criteria.
Open/Edit your newly created query and add parameters. Add simple parameters to ask user to enter a specific client code for the report to display only the specific client or all. Parameters can be text, numeric, currency, or date/time data.
For more information on adding parameters in queries and reports, click this link:
http://office.microsoft.com/en-us/access-help/use-parameters-in-queries-and-reports-HA010096314.aspx#_Toc267474486
50.6.5 Create Report
Click on the Navigation Pane
Click All Access Objects
Select Reports
Click the Create tab (from the top)
Click Report Wizard
Where it says Tables/Queries, dropdown the list and select the query you created, Query: xmasMailistListQuery
Select all except bolUserField11
All the fields will now appear on the Selected Fields
Click Next
Where it says 'How do you want to view your data?' select 'by tblClientsContacts'
Click Next four times (3x)
Where it says 'How would you like to lay out your report?, tick Tabular and click Next
Where it says 'What Title do you want for your report?', type xmasMailingList
Tick Modify the report's design and click Finish
50.6.6 Modify/Edit Report
After clicking Finish, the Report Wizard will show the report in design view
Under the Page Header, click each label and press delete
Click the Detail section and drag it below the Page Header
Drag the Page Footer Downwards and then reposition the fields
In the Page Footer, remove all Page Footer items by clicking each one and pressing delete
After deleting the footer items, move the Report Footer section and drag it below the Page Footer
Click File -> Save
To Edit, click the Navigation Pane
Select Reports from the objects list
Right Click on the report you are trying to edit and select Design View
50.6.7 Add the Report to the LatiReporter Switchboard
Close all views & minimize the Navigation Pane to go back to the LatiReporter screen (switchboard)
Click Change SwitchBoard Items
Click Reports SwitchBoard and Click Edit
Click New
Where it says 'New Switchboard Command' replace it with Christmas Mailing List
Where it says 'Go to Switchboard' dropdown list and select Open Report
Where it says 'Report', dropdown list and select your newly created report 'xmasMailingList'
Click Ok
In the Items on this Switchboard, your new Christmas Mailing List appears
Click Christmas Mailing List and Click Move up button and position it above Return to Main Switchboard
Click Close twice
50.6.8 Run the Report
From the LatiReporter Switchboard, click Preview Reports
Click Christmas Mailing List
Enter 0 to show all contacts or -1 for selected contacts only
Click OK
To adjust the positioning of the fields, refer to the Modify/Edit Report section of this guide
NOTE: -1 means the txtUserField11 on the Business Screen is ticked while 0 means it is not ticked.