Report designing in MS-Access is very easy as we already know. Once we setup 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 on the Report design itself as all the standard labels, like main heading, the period indicator, Report prepared date, Page Numbers etc. are already taken care as part of the designing 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.

When someone asks for something difficult we can easily say it is not possible and the user may swallow it too, provided if 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 in this category too. But, then after saying that it keeps me disturbed and starts 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 to do it. But, there are times that we have to stick to the No answer too.

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

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, Month-wise formatted data shown below:

sample data table viewsample data table view

Click to Enlarge

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

Click to Enlarge

The Report contents should be for a period of 12 months and data selection criterion is set for the period Between 199607 and 199706 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, no issues.

But, when the data selection criteria changes for a different period the Report will not run. It will show error on the first field that do not match with the fields already placed on the Report and refuse 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 Report using this permanent field Names. We must find a way to define the field header labels with some formula taking the values from the report period parameter. The user can create report data for full 12 months or 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 with cross over period then the data for earlier period comes at the right end of the Report and later period at the beginning. The field headings are another issue to take care. Finally we cannot call it a good report or let us put mildly it is not a user-friendly report.

We found a remedy for this issue by introducing a small program on the Report VB 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 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;

 

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

    

   
   

   Click to Enlarge

    

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

 

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

 

6. In Page Header Section create 13 Label Controls and name them as 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 Save from File menu and save the Report with the name FreightVal_Rpt.

 

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

 

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

     

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

 

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 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 File: DynamicReport2K.zip (Size:75K)