Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Dynamic Report

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, 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 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 try something on those line of ideas and finally to 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 the problem solving instead of beating around the bush. I was trying to be little philosophical. It is not that big as you are imagining 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 view

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

The Report contents should be for a period of 12 months and 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, 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 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 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 it this way, 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.

  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 Demo Database


Share:

9 comments:

  1. Thanks for the post.

    I was recently tasked with updating some old legacy Access reports, and they were hoping to do what this article clearly outlines. It is safe to say that you saved me several hours of research and trail and error, seeing as how my experiance in Access is limited.

    Good luck in the future.

    ReplyDelete
  2. Hi there

    I am using Access 2003. I followed your instructions but I only get the header (the months in the right format), footer (the total for the month) and in the details section only the list of employee names. There are no values for each name. How do I get that to be displayed as well.

    I tried other sample codes from Northwind but also end up with the same problem. What am I doign wrong?

    ReplyDelete
  3. Please forward your Database in Zipped form (if it is small enough) so that I can have a look at it. You can find my e-mail address at the left panel.

    In the meantime I shall create a sample database of Dynamic Report and upload into the Website so that you can download it as well.

    Regards,

    ReplyDelete
  4. Hi

    I have done once a report for sawmill. Their request were to get specification of sawn material (columns by length).
    I gathered data with crosstab query and dynamically modified report. More or less same approach as used in this track, but I created and positioned dynamically "length columns" (used for selection criteria ao called "numerical" column names).
    But it workin well, so actually it is possible even position columns and in this way adjust report with variable amount of columns to left.

    /oleg

    ReplyDelete
  5. add-in to previous append
    /oleg (oleg@nassu.pri.ee)

    ReplyDelete
  6. [...] may take a look at the following link for an example of Cross-Tab Queries and Dynamic Report: LEARN MS-ACCESS TIPS AND TRICKS Blog Archive Dynamic Report __________________ http://www.msaccesstips.com (Learn MS-Access Tips & [...]

    ReplyDelete
  7. [...] LEARN MS-ACCESS TIPS AND TRICKS Blog Archive Dynamic Report [...]

    ReplyDelete
  8. [...] can provide, but take a look at it. Perhaps it can give you ideas to approach your problem: LEARN MS-ACCESS TIPS AND TRICKS - Dynamic Report __________________ http://www.msaccesstips.com (Learn MS-Access Tips & [...]

    ReplyDelete
  9. Hi,

    How can I handle a crosstab query which returns more columns then will fit on one line?

    Same as if the user had selected 24 months.

    I this case I can not just throw away this data.

    Thanks

    ReplyDelete

Translate



PageRank
Your email address:

Delivered by FeedBurner

Search

Infolinks Text Ads

blog.feedspot.com Microsoft Access Blogs

Popular Posts

Search This Blog

Blog Archive

Powered by Blogger.

Labels

Forms Functions How Tos MS-Access Security Reports msaccess forms Animations msaccess animation Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Queries External Links msaccess reports msaccess tips Menus and Toolbars Accesstips MsaccessLinks Process Controls Art Work Downloads msaccess How Tos Graph Charts msaccessQuery List Boxes Command Buttons Emails and Alerts Query Combo Boxes Custom Wizards DOS Commands ms-access functions msaccess functions msaccess graphs msaccess reporttricks Array msaccessprocess security advanced Access Security Custom Functions Data Macros Menus Property Report Top Values VBA msaccess email msaccess menus progressmeter Access2007 Auto-Number Calculation Command Button Copy Data Type Field Type Fields Form Join Microsoft Numbering System Records Security Split SubForm Table Utility Variables Workgroup database msaccess wizards Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting ChDir Color Palette Conditional Formatting Controls Data Filtering Defining Pages Diagram Disk Dynamic Lookup Error Handler Excel Export Expression External Filter Form Instances Formatting Groups Hexadecimal Numbers Import Labels List Logo Macro Mail Merge Main Form Memo Methods Monitoring Object Reference Objects Octal Numbers Operating System Paste Primary-Key Product Rank Reading Recordset Rich Text Sequence SetFocus Summary Tab-Page Tables Time Difference Union Query User Users Water-Mark Word automatically commands function hyperlinks iSeries Date iif ms-access msaccess msaccess alerts pdf files reference restore switch text toolbar tutorial updating upload vba code

Featured Post

Passing Two Dimensional Array to Function

First of all, I have some good news for you, our website: LEARN MS-ACCESS TIPS AND TRICKS has been selected by https://blog.feedspot.com pa...

Labels

Blog Archive

Recent Posts