site home | calibration control | help | custom report

Creating Custom Report

Create a custom 'due cal' report

The most complicated part of creating a custom report is using 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. As a prerequisite to this help topic, read the SQL SELECT statement help topic first.

Create Custom Report

To begin, let’s assume that we want to create a new calibration due report for all of our equipment due before the end of next month. Begin by selecting the ‘Custom Report’ item from the ‘Reports’ menu (see picture below).

Create Calibration Management Custom Report

Report Designer

Clicking on the [Open] button in the Select Data Source dialog will open the Report Designer. This is a blank slate where you will be able to drag your fields into whatever location desired in the report ‘Detail.’

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 your database. The Query field defines the fields and records we need from specific table and in a defined order.

Calibration Management Report Data Source

Connection String - Choosing the Provider

To change your connection string, click the [Build] button to the right of the Connection String text box and you will see the Data Link Properties. 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. Otherwise, if you need to connect to an instance of SQL Server, you will usually need to select the SQL Server OLE DB Provider.

If this doesn’t work, ask your database administrator (DBA) for assistance in choosing the correct provider.

Connection String - Setting the Connection

Continuing the assumption that you need to connect to the apecal.mdb file, you need to paste the path to the apecal.mdb file in the Data Source field. If you need help 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 your database, click the [Test Connection] button and you should see a ‘Test connection succeeded’ response. If you do not get a successful response, repeat the above steps until you do.

Writing an SQL Select Statement

Remember that you should already be at least a little familiar with SQL and that you can learn more about SQL by reading the SQL SELECT statement help topic. When you are finished creating your SQL SELECT statement, it will look something like the following picture.

Adding Fields, Labels, & Report Info

In the following image, I performed the following actions to create My Due Cal Report:

Dragged the bound fields (txtEquipmentID, txtDescription, txtLocationName, and txtCalibrationDate) 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.
Selected the txtCalibrationDate field and edited is properties (bottom-right corner of screen) so that the OutputFormat was 'M/d/yyyy'.
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. I also created the page title (My Due Cal Report) the same way.
Dragged ReportInfo objects 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 FormatString property of the datetime field to '{RunDateTime:M/d/yyyy}'
- Set the FormatString property of the page number field to 'Page {PageNumber} of {PageCount}'
- Clicked the right-align button (top of page) for the page number field.
Performed other minor tasks involving bolding, underlining, aligning, and positioning to make the report look the way I wanted it to look.

Adding Fields to the Calibration Management Report Designer

Save Report Layout

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

Save Calibration Management Report Layout

Save As

Choose a file location and name your report ‘my due cal report’. The file location usually defaults to the program director or the same area where the Calibration Control files are stored.