Highlighting Reports

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 but 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 a very important roll 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 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 the Target, they have to undergo a postmortem 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 highlighting of Values less than $100,000 with a circle around them. A small VB Routine in the Detail_Print() Event Procedure compares 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.

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

1. Click on the Query Tab in the Database Window.


2. Click New to create a new Query and click Close button on the Show Table dialog control to close it without selecting a Table from the list.


3. Click on the Toolbar button with the SQL label on it to display the Query’s SQL Window.


4. 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;



5. 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;

6. Click on the SalesReportQ Query and select 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:




7. Click on the Total Field and display the Property Sheet. Change the following Property Values:



Name         :  Total 
Border Color :  12632256



8. Display the VB Module of the Report, click on the Code Toolbar Button above or select Code from View Menu.


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



‘Global declaration. 
‘The next line (Dim MinTarget As Double,  curVal As Double) 
‘must be placed just below the Option Compare Database line 
‘ (and Option Explicit line, if present) at top of the Form Module.


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


On Error GoTo Detail_Print_Err


MinTarget = 100000
curVal = Me![Total]
If curVal < MinTarget Then
  intPrintCircle = True
Else
  intPrintCircle = False
End If

'slightly increase/decrease this value 
'to adjust the oval shape.

  sngAspect = 0.3

'Determine height and width of ellipse.

 intEllipseHeight = Me.Total.Height 
 intEllipseWidth = Me.Total.Width 
 sngYCoord = Me.Total.Top + (intEllipseHeight \ 2)

        
    Set ctl2 = Me.Total
    If Not IsNull(ctl2) Then
        Set ctl = ctl2

 'Determine coordinates of ctl and draw ellipse.

    If intPrintCircle Then
      sngXCoord = ctl.Left + (intEllipseWidth \ 2)
      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



10. Save the report with the Name: Sales Report.


11. 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 in the program into say 0.25. You may increase or decrease this value and try out to perfectly fit the elliptical shape within the textbox boundaries.

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 in Red and all $100,000s should print in normal Color. Sample image is given below:

1. Make a copy of the Sales Report and delete the Code from the VB Module.


2. Click on the Total textbox in Design View.


3. Select Conditional Formatting… from the Format Menu.


4. Set the conditions and the Color as shown below:




5. 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.


6. Open the Report in Print Preview to display the Values in different colors based on the conditions that we set on the control.

There are situations that we cannot use the conditional formatting method and need to go by the drawing the elliptical shape method. In both examples given above we are comparing individual values to a constant ($100,000) and then draws a circle or changes color with conditional formatting.

Assume that we are running a Service Station and we have Service Contracts with Companies for 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 for servicing on a fixed interval of 3 months or after every 5000 KMs etc.

The management asks us to prepare a Report on all Vehicles on Service Contracts showing Date of Service, Odometer Reading & the 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 on the Report to quickly identify them for investigation. It can be tampering of 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 repetition of lesser values further then 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 these kind of situations the conditional formatting method cannot be used.

---

Download Demo Database

---

Startup Screen Design
Animated Floating Calendar
Edit Data in Zoom-in Control