Dynamic Report

Introduction

Report designing in MS-Access is very easy as we already know. Once we set up a procedure to run the macros/programs to prepare the data for a standard Report, all we need to do is to design the Report and add it to the Report List. Every time the user runs the report it is ready to Preview or Print. The Source data may change, based on the report period setting, but there will not be any change in the structure of the Report or source data or need any change in the Report design itself as all the standard labels, like the main heading, Report generated for which period, prepared date, Page Numbers, etc., are already taken care of as part of the initial report design task.

We are going to discuss a Report that is not so easy to plan on the above rules or based on a fixed source data structure. The structure of the source data is not predictable or shall we say field names of the source data can change every time we run this Report. If the data structure changes every time, then you cannot put the field names permanently on the report design as we normally do. Adding to the difficulty the number of fields for the report also may change. Even more difficult situation is to create Report Summary figures at Group-level and Report Footer level Grand-totals.

When someone asks for something difficult we can easily say it is not possible and the user may swallow it too, provided he/she doesn't know computer programming at all. In the Software world when someone says no to something, it simply means that he doesn't know how to do it, at least for the time being. I fall into this category too. But, then after saying that it keeps me disturbed, and start asking myself, can I write it off just like that or find a way somehow to do it? It may take a while to have a plan, get things organized, try something along those lines of ideas, and finally come up with something that works. Doesn't matter how many steps you have taken to achieve that. The final result is that matters. You can always go back and review the steps and refine them. But, there are times that we have to stick to the No answer too.

Let us get into problem-solving instead of beating around the bush. I was trying to be a little philosophical. It is not as big as you are imagining now, after listening to my bragging.

Report Preparation

Before getting into the Report preparation part, first I will show you some sample lines of data and the Report Format that we need to deal with. The sample data is taken from two familiar Tables from the Northwind.mdb sample database; Employees and Orders, which we have already used in earlier examples.

In the above table, the Shipped Date range is from July 10, 1996, to May 6, 1998. Our task is to prepare a report from the following Employee-wise, Year-wise, and Month-wise formatted data shown below:

sample data table view

Click to Enlarge

When a sample Report is designed using the above data as a source, it will look like the image given below.

Click to Enlarge

The Report contents should be for a period of 12 months and the data selection criterion is set for the period Between 199607 (July 1996) and 199706 (June 1997) in the data selection Query of the Report. In the Details section, the report fields are inserted. Report Footer controls are set with the Summary formula taking month-wise Totals. Field Heading Controls are defined with month-Year labels. So far so good, we can open the report in Preview or Print, with no issues.

Report Data Selection Criteria

But, when the data selection criteria change for a different period the Report will not run. It will show an error on the first field that does not match with the fields already placed on the Report TextBoxes and refuses to open to Preview or Print.

An alternative method is to create a Report Table with Field Names like M1, M2 to M12 for holding January to December Data, and use an Append Query to add the report data into this table and design a Report using these permanent field Names. We must find a way to define the field header labels with some formula taking the values of the report period parameter. The user can create report data for the full 12 months or a lesser period for a particular year. Then we are putting conditions on the user asking him to select data for a particular year only, not to cross from one year to the other when entering criteria.

If he/she goes for a trial run cross-over a period, then the data for the earlier period comes at the right end of the Report and the later period in the beginning. The field headings are another issue to take care of.  Finally, we cannot call it a good report or let us put it this way, is not a user-friendly report.

Sample Data Preparation Queries

We found a remedy for this issue by introducing a small program on the Report VBA Module to take care of the final stage designing task based on the source data structure, every time we open it to preview or print.

1. To get prepared for the Program, Import the Tables Employees, and Orders from the Northwind.mdb sample database, if you don't have them. If you don't know the location of the sample database visit the Page Saving Data on Forms not in the table for location references.

2. Copy and paste the following SQL String into new Query's SQL Window and save it with the Names as indicated below:

   Query Name: FreightValueQ0
   
   SELECT [FirstName] & " " & [LastName] AS EmpName,
    Val(Format([ShippedDate],"yyyymm")) AS yyyymm,
    Orders.Freight
   FROM Orders INNER JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID
   WHERE (((Val(Format([ShippedDate],"yyyymm"))) Between 199607 And 199706));
   
   Query Name : FreightV_CrossQ
   TRANSFORM Sum(FreightValueQ0.Freight) AS SumOfFreight
   SELECT FreightValueQ0.EmpName
   FROM FreightValueQ0
   WHERE (((FreightValueQ0.yyyymm)<>""))
   GROUP BY FreightValueQ0.EmpName
   PIVOT FreightValueQ0.yyyymm;

   Report Design Task

3. After creating the above Queries one after the other design a Report is shown below using FreightV_CrossQ Query Data Source.

   

   Click to Enlarge

4. In the Detail Section of the Report create 13 Text Box Controls; make the leftmost one wider for the Employee's Name, and others month-1 to 12. Change the Name Property of the TextBoxes as M00, M01 to M12 from left to right (write two-digit numbers in the Name Property as 01, 02, etc., along with the prefix M). Leave the Control Source property empty.

5. In the Report Footer Section, create 13 TextBox controls and Name them from T00, T01 to T12. Leave the Control Source Property empty.

6. In Header Section creates 13 Label Controls, name them L00, L01 to L12, and leave the Caption Property empty.

7. Create a Label at the Top and set the Caption property value to EMPLOYEE-WISE FREIGHT VALUE LISTING.

8. While the Report is still in design view, select the Save option from the File menu and save the Report with the name FreightVal_Rpt.

9. Select Code from the View menu to display the VBA Module of the Report.

10. Copy and Paste the following Code into the VBA Module:

    Automating Report Control's Data Reference Setting

    Private Sub Report_Open(Cancel As Integer)
    Dim db As Database, Qrydef As QueryDef, fldcount As Integer
    Dim rpt As Report, j As Integer, k As Integer
    Dim fldname As String, ctrl As Control, dtsrl As Date
    Dim strlbl As String, fsort() As String
    
    On Error GoTo Report_Open_Err
    
    Set db = CurrentDb
    Set Qrydef = db.QueryDefs("FreightV_CrossQ")
    fldcount = Qrydef.Fields.Count - 1
    
    If fldcount > 12 Then
       MsgBox "Report Period exceeding 12 months will not appear on the Report."
       fldcount = 12
    End If
    
    Set rpt = Me
    
    ReDim fsort(0 To fldcount) As String
    For j = 0 To fldcount
        fldname = Qrydef.Fields(j).Name
        fsort(j) = fldname
    Next
    
    'Sort Field names in Ascending Order
    For j = 1 To fldcount - 1
        For k = j + 1 To fldcount
            If fsort(k) < fsort(j) Then
                fsort(j) = fsort(k)
            End If
        Next
    Next
    
    For j = 0 To fldcount
    'Monthwise Data    
    Set ctrl = rpt.Controls("M" & Format(j, "00"))
        ctrl.ControlSource = fsort(j)
        Set ctrl = rpt.Controls("T" & Format(j, "00"))
        If j = 0 Then
            ctrl.ControlSource = "=" & Chr$(34) & " TOTAL = " & Chr$(34)
        Else
            ctrl.ControlSource = "=SUM([" & fsort(j) & "])"
        End If
    
    'Header labels
    If j = 0 Then
        Me("L" & Format(j, "00")).Caption = "Employee Name"
    Else
        dtsrl = DateSerial(Mid(fsort(j), 1, 4), Right(fsort(j), 2), 1)
        strlbl = Format(dtsrl, "mmm-yy")
        Me("L" & Format(j, "00")).Caption = strlbl
    End If
    Next
    
    Report_Open_Exit:
    Exit Sub
    
    Report_Open_Err:
    MsgBox Err.Description, , "Report_0pen()"
    Resume Report_Open_Exit
    End Sub

11. After copying the code minimize the VBA Window, display the Property Sheet of the Report and check whether the On Open property is marked with the [Event Procedure] Value. If it is not, then the code we have pasted is not correctly loaded into this event. Select [Event Procedure] from the drop-down list and open the Code module, cut the Code (except the top and bottom lines that we have pasted from the Web page) and paste the Code within the newly opened Empty Report_Open() . . . End Sub lines, remove the orphaned lines, and save the Report.

    Print Previewing the Report.

12. Open the Report in Print Preview. Your Report must be loaded with the Values from the Source Query with correct heading Labels and the Report Footer Summary.

13. Open the First Query in the design view and change the Criteria values in different ranges, taking care that the Range of values are not exceeding 12 months (it can be less than 12), and try out the Report.

If the selected period exceeds 12 months the Report will open with the data that fits into the maximum number of fields, after showing a message indicating that the selected period exceeds 12 months.

If the selected period is less than 12 months, then the rightmost controls will be empty. In either case, the Report will open and we can view the contents.

Download Demo Database

Download Demo Database

• Highlighting Reports
• Saving Data on Forms Not in Table
• Edit Data in a Zoom-in Control - Access 2003
• Custom Menus and Tool Bars-2 - Access 2003
• Custom Menus and Tool Bars -Access 2003