Navigation:  50    U:  How to Use the Lati Reporter >

50.6 Design a Custom Report

Previous pageReturn to chapter overviewNext page

50.6.1 The following steps illustrate how to create a simple custom report.

1646_thumb

click here to zoom

 

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

1643_thumb

click here to zoom

 

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

1861_thumb

click here to zoom

 

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

1647_thumb

click here to zoom

 

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

1648_thumb

click here to zoom

 

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

1650_thumb

click here to zoom

 

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.