Highlighting Reports ~ LEARN MS-ACCESS TIPS AND TRICKS

Thursday, August 16, 2007

Highlighting Reports

Introduction.

Analyzing data stored in tables and presenting the results through reports is a key function of any Database Management System.

An Access application may contain multiple logically related tables, maintained separately to simplify data management. When it's time to generate reports, these related tables are selected and joined using common fields. The data is then filtered and formatted for output reports. Well-designed, timely reports play a crucial role in effective decision-making.

Designing reports, like form design, is an art in its own right. MS Access provides powerful report design tools that, with a little practice, you can master effectively.

Let’s consider a scenario where we are tasked with creating a report on the company’s Sales Team to review their monthly sales target achievements. Each team member is assigned a mandatory monthly sales target of $100,000. At the end of each month, management requires a performance review report. If any team member falls short of this target, their performance is closely examined to identify and address potential issues in their assigned area.

We have prepared a sample report displaying the Sales Team’s month-end performance figures. Records of those who failed to meet the $100,000 target are visually highlighted with a red circle for easy identification.

The finished Report image is given below:

The tabular report shown above was created using the Report Wizard and is mostly straightforward in its design. However, what makes it stand out is the visual emphasis placed on values falling below the $100,000 target—each such value is marked with a red circle.

This effect is achieved with a simple VBA routine placed in the Detail_Print() event procedure. During the report’s printing or print preview, the routine evaluates each row's sales figure. If a value is found to be less than $100,000, the code dynamically draws a red circle around it, helping reviewers quickly identify underperformers.

Prepare the Report.

To prepare this report, we will need two tables from the Northwind.mdb sample database. If you're unsure of the file's location, please refer to the earlier post titled "Saving Data on Forms Not in Table" for details on where to find the Northwind.mdb file.

Once located, open your current database and import the following two tables from the Northwind.mdb file:

Orders
Order Details

Click on the Query Tab in the Database Window.
Click New to create a new Query and click the Close button on the Show Table dialog control to close it without selecting a Table from the list.
Click on the Toolbar button with the SQL label to display the Query's SQL Window.

Copy and paste the following SQL String into the SQL window and save it with the Query Name: SalesReportQ0.

SELECT Orders.OrderID,
 Orders.EmployeeID,
 [Order Details].UnitPrice,
 [Order Details].Quantity,
 [UnitPrice]*[Quantity] AS Extended_Price
FROM Orders 
INNER JOIN [Order Details] 
ON Orders.OrderID = [Order Details].OrderID;

Follow Steps 2 and 3 outlined above to create a new query based on the source data from the first query named SalesReportQ0.
1. Open the Query Design window.
2. Add the SalesReportQ0 query as the data source.
3. Switch to SQL View.
4. Copy and paste the SQL string provided below into the SQL window.
5. Save the query with the name: SalesReportQ.
```
SELECT SalesReportQ0.EmployeeID,
 Sum(SalesReportQ0.Extended_Price) AS Total
FROM SalesReportQ0
GROUP BY SalesReportQ0.EmployeeID;
```
Click on the SalesReportQ Query and select a Report from the Insert Menu. Select Auto Report: Tabular on the Report Wizard and make changes to the design to look like the sample image given below:

Step: Modify the Total Field Properties

Click on the Total field on your report design (in Design View).
Press F4 (or right-click and choose Properties) to display the Property Sheet.

On the Format tab of the Property Sheet, change the following values:

Name: Total
Border Color: 12632256

Add VBA Code to Report Module

Open the Report in Design View.
On the Ribbon, go to View → Code
(Or click the “View Code” button on the toolbar above.)
In the Visual Basic Editor, paste the following code into the Report’s code module:

vba

Option Compare Database
Dim MinTarget As Double, curVal As Double

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
'  Draw ellipse around controls that meet specified criteria.
Dim ctl As Control, ctl2 As Control, intPrintCircle As Boolean
Dim sngAspect As Single, sngYOffset As Single
Dim intEllipseHeight As Integer, intEllipseWidth As Integer
Dim sngXCoord As Single, sngYCoord As Single, mNewSpeedo As Long

On Error GoTo Detail_Print_Err

MinTarget = 100000
curVal = Me![Total]
If curVal < MinTarget Then
  intPrintCircle = True
Else
  intPrintCircle = False
End If
        
    Set ctl2 = Me.Total
If Not IsNull(ctl2) Then
    Set ctl = ctl2
    
    If intPrintCircle Then
   ' change this value to adjust the oval shape of the circle.
    sngAspect = 0.25 'Increase/Decrease this value to adjust the shape
    
    ' Get Height and Width of Control
    intEllipseHeight = Me.Total.Height
    intEllipseWidth = Me.Total.Width
    
    'Calculate Center X,Y Corordinate
    sngXCoord = ctl.Left + (intEllipseWidth \ 2)
    sngYCoord = Me.Total.Top + (intEllipseHeight \ 2)
    
    ' Draw Ellipse within the Control
            Me.Circle (sngXCoord, sngYCoord), intEllipseWidth \ 2, _
            RGB(255, 0, 0), , , sngAspect
           intPrintCircle = False
        End If
    End If

Detail_Print_Exit:
Exit Sub

Detail_Print_Err:
MsgBox Err.Description, , "Detail_Print"
Resume Detail_Print_Exit
End Sub

Save the report with the Name: Sales Report.

Testing and Fine-Tuning the Report

Open the Report in Print Preview to see the red circles in action.
The circle is drawn relative to the size of the Total textbox. If the textbox is too wide, the top and bottom parts of the ellipse might not be visible within the control boundary.

Fixing Ellipse Display Issues

To ensure the circle (ellipse) fits neatly around the value:
- Option 1: Reduce the width of the Total textbox slightly.
- Option 2: Adjust the aspect ratio used for drawing the ellipse.
For example, if you're using a sngAspect variable in your code like this:
```
vba
sngAspect = 0.3
```
Try changing it to a smaller value, such as:
```
vba
sngAspect = 0.25
```
A smaller aspect ratio makes the ellipse taller (more circular), while a larger one makes it flatter. You can experiment with values (e.g., 0.2 to 0.4) to find the best fit for your textbox.

Tip:

After each change, open the report in Print Preview to visually verify the result.

Conditional Formatting

If you prefer to highlight sales values using color coding instead of drawing circles, there's a simpler and more effective method called Conditional Formatting—available in MS Access 2000 and later.

For example, suppose we want to visually emphasize performance as follows:

Green for sales above $100,000
Red for sales below $100,000
The default (black) for sales is exactly $100,000

This can be achieved without writing a single line of code.

A sample image demonstrating this formatting is shown below:

Make a copy of the Sales Report and delete the Code from the VB Module.
Click on the Total textbox in Design View.
Select Conditional Formatting. . ., from the Format Menu.
Set the conditions and the Color as shown below:
After setting the first condition, click Add >> to expand the Conditional Formatting dialog box and define additional conditions. You can set up to three different conditions on the same field, each with its own distinct formatting. Once you've configured all the desired conditions and formatting options, click OK to save the changes.
Open the Report in Print Preview to display the Values in different colors based on the conditions that we set on the control.

When Conditional Formatting is not feasible

There are situations where Conditional Formatting cannot be used, and we must rely on the method of drawing elliptical shapes to highlight data. In both of the earlier examples, we were comparing individual values to a constant value ($100,000) and either drawing a circle or applying color formatting accordingly.

Now, imagine we’re running a Vehicle Service Station that holds Service Contracts with various companies to regularly service their vehicles. These services are scheduled either at fixed time intervals (e.g., every 3 months) or when a certain odometer reading is reached (e.g., every 5,000 kilometers).

Management requests a report showing each vehicle's Service Date, Odometer Reading, and Service Expenses. The report must be sorted by Service Date, and if any odometer reading is lower than the previous service's reading, it should be highlighted for investigation. This discrepancy may indicate meter tampering or data entry errors by service personnel.

In such cases, each odometer reading must be compared with the previous reading. If a lower value is detected, it should be circled in the report. This comparison continues throughout the report, and all such occurrences must be marked until a higher reading appears, which then becomes the new reference.

This type of dynamic, row-to-row comparison is not possible using Conditional Formatting, as it cannot access or compare values across different rows. Therefore, custom VBA code using the Detail_Print() event and drawing methods is essential for such reports.