Highlighting Reports ~ LEARN MS-ACCESS TIPS AND TRICKS

Introduction.

Analysis of data stored in Tables and providing resultant information in the form of Reports to the outside world is an important function of any Database Management System.

An Access Application may contain several Tables logically related, maintained separately for easier handling. When it is time to prepare Reports related tables are selected and joined on common information in them, filtered and placed the output on Reports. Meaningful and timely Reports play an important role in decision-making.

Report designing is an art in its own merit, like Form designing. MS-Access is equipped with excellent Report Designing Tools and with a little practice, you can master it.

We have been asked to prepare a report on the Company's Sales Team, to review their monthly Sales Target Achievement. A mandatory Target Amount of $100,000 is fixed on each member of the Team and the Management wants a month-end Report to review his or her performance. The management focuses on the performance of the Sales Team every month and if any member of the Team lags behind Target, they have to undergo a postmortem of their activity area to set things right for the future.

We have prepared a sample Report on the Sales Team's Month-end figures, highlighting cases that didn't meet the stipulated Target Amount of $100,000, with a red circle around them.

The finished Report image is given below:

The tabular Report above is designed with the Report Wizard and nothing special about it except to highlight Values less than $100,000 with a circle around them. A small VB Routine in the Detail_Print() Event Procedure compares the value on each row with the target figure of $100,000 when the printing/print previewing is in progress and if any of them is less than $100,000 then draws a red circle around that Value.

Prepare the Report

To prepare this report we need two Tables from the Northwind.mdb database. If you are not sure where the file can be located, visit the earlier Post: Saving Data on Forms not in Table for the location reference of the Northwind.mdb database. Import the following two Tables from the Northwind.mdb database.

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 on it 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;

Go through steps 2 & 3 above, to create another Query from the source data of the first Query SalesReportQ0, Copy and Paste the following SQL String into the Query's SQL Window and save it 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:
Click on the Total Field and display the Property Sheet. Change the following Property Values:
- Name: Total
- Border Color: 12632256
Display the VB Module of the Report, click on the Code Toolbar Button above, or select Code from View Menu.

Copy and paste the following Code into the VB Module of the Report:

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.
Open the report in Print Preview to see how it works. The Circle is drawn based on the size of the Total Textbox. If the control is too wide then upper and lower parts of the elliptical shape may not appear within the control boundary. To correct this problem you can reduce the width of the Total Field if it is too wide, or you may reduce the value in sngAspect = 0.3, to 0.25. You may increase or decrease this value and try out to perfectly fit the elliptical shape, within the textbox boundary.

Conditional Formatting

If you prefer Color Coding the Sales Values, rather than struggling with the above technique, then there is an easier way to do it, known as Conditional Formatting (MS-Office 2000 or later).

Let us say, we want all values above $100,000 should appear in Green Color, below $100,000 Red, and all $100,000s should print in normal Color. A sample image is given 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 control down to set more conditions. Up to three conditions you can set on the same field, with Different Formats. After setting the values, 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 we cannot use the conditional formatting method and need to go from drawing the elliptical shape method. In both examples given above, we are comparing individual values to a constant ($100,000) and then draw a circle or change color with conditional formatting.

Assume that we are running a Service Station and we have Service Contracts with Companies servicing their vehicles regularly for a certain period of time or the Vehicle's Odometer reading reaches a certain level. Till that time the Vehicle comes to servicing on a fixed interval of 3 months or after every 5000 kilometers. etc.

The management asks us to prepare a Report on all Vehicles on Service Contracts showing the Date of Service, Odometer Reading & Expenses. The Report is sorted on Service Date and if there is any Odometer recording found in Error (i.e. The meter reading is recorded less than the earlier service-time recording) that must be highlighted in the Report to quickly identify them for investigation. It can be tampering with the meter or it can be recorded wrongly by the Servicing personnel. In either case, it should be highlighted.

In this case, we have to compare each value with the previous meter reading and if the current value is found less than the previous one then it must be highlighted with a circle. There may be a repetition of lesser values than we have, to continue putting circles around all those values till we reach a value greater than the one that we are holding as key. In this kind of situation, the conditional formatting method cannot be used.