Create A Custom Report

Create a custom 'due cal' report

To create a custom report from scratch Calibration Control (our Calibration Management Software) uses a database language called SQL (pronounced ‘sequel’).  We use SQL SELECT statements to tell the database what fields and records from what tables and in what order we want our data.  Therefore, as a prerequisite to this help topic, read the SQL SELECT statement help topic first.

Create a Custom Report

To begin, let’s assume we want to create a new calibration due report for all of the equipment due before the end of next month.  Select the ‘Report Designer’ item from the Utilities tab of the Menu Ribbon.  This is a blank slate to drag fields into whatever location desired in the report ‘Detail’.

Report Designer

Define Report Data Source

If you click on the Edit Data Source link in the bottom-right corner of the Report Designer, a blank Report Data Source dialog will appear (below).  The Connection String field defines the connection to the database being used.  The Query field defines the fields and records we need from a specific table and in a defined order.

Report Data Source

Connection String - Choosing the Provider

The easiest way to change the connection string is to select the Show Connection String menu option from the Utilities Tab.  This will show the same connection string the Ape application is using to connect to the database.  Click the [Copy to Clipboard & Close] button and paste the result into the Connection String field of the Report Data Source dialog.

Show Connection String in Ribbon Menu

Show Connection String

Otherwise, build the connection string by clicking the [Build] button to the right of the Connection String text box and the Data Link Properties will be shown.  Click on the Provider table if is not already displayed.  Assuming you want to connect to an Access database (i.e., apecal.mdb), select the 'Microsoft Office 12.0 . . .' provider and click the [Next] button.  If connecting to an instance of SQL Server select the SQL Server OLE DB Provider.

If this does not work, ask your database administrator (DBA) for assistance in choosing the correct provider.

Data Link Properties

Connection String - Setting the Connection

Continuing the assumption that you need to connect to the apecal.mdb file, paste the path to the apecal.mdb file in the Data Source field.  With help on finding your database, read the locate your calibration management database help topic.  The entire path will look something like this:

C:\Users\Public\Documents\Ape Software\Calibration Control\apecal.mdb

After entering the path to the database, click the [Test Connection] button and a ‘Test connection succeeded’ response should show.  If the connection is unsuccessful, repeat the above steps until it is.

Data Link Properties - Connection

Writing a SQL Select Statement

When the SQL SELECT statement has been created, it will look something like the following picture.  Note that the Calibration Due field is set to a specific filter of less than 5/1/2011.  This date is called a parameter (report variable), which can be changed with every printing.  See the help topic on Report Parameters to learn how to add Parameters to SQL statements.

Report Data Source

Adding Fields, Labels, and Report Info

In the image at the top of this page, I performed the following actions to create the Calibration Due report:

  1. Dragged the bound fields (e.g., Equipment ID, Model & Description) from the right side of the page (Fields - Bound tree) to their current location in the Detail band and resized them to fit their contents.
  2. Selected the Date fields and edited their properties (bottom-right corner of screen) so that the OutputFormat = 'M/d/yyyy'.
  3. Dragged Label objects from the left side of the screen to locations above each field in the page header, resized them to fit their corresponding fields, bolded, and underlined them.  The page title (Calibration Due Report) was created the same way.
  4. Report Info objects were dragged from the left side of the screen to the left and right side of the footers.  I clicked on each object and changed their properties (bottom-right) to . . .
    • Set the Format String property of the datetime field to '{RunDateTime:M/d/yyyy}'
    • Set the Format String property of the page number field to 'Page {PageNumber} of {PageCount}'
    • Clicked the right-align button (top of page) for the page number field.
  5. Other minor tasks involving bolding, underlining, aligning, and positioning were made to make the report look the way I wanted it to look.

Menu Settings

Assuming the custom report is stored in the Ape application Reports folder, the default file name of the new report in the report menu will be the file name.  Override the file name displayed by entering a preferred name in the User Data field of the report properties.  Also, to place the new report under the first or second node of the report tree, place a 1 or 2 before the other text in the User Data field.  For example, entering ‘1 My New Report’ in the User Data field will place the name ‘My New Report’ under the first node.

Save and Preview Report Layout

Save the report layout by selecting ‘Save Layout’ from the Report Designer dropdown menu.

Choose a file location and name your report ‘my due cal report’.  The file location defaults to the Report folder under the Files Folder (select Open Files Folder from the File dropdown menu) for the Ape application to find it.

Select the Preview tab at the bottom of the Report Designer.

Save Layout and Print Preview

Last updated:  18 Jan 2017