Microsoft Access includes a powerful report building engine. However, when you double-click a report in the database window to view it, Access displays the printable version of the report in the Print Preview window. This window displays the report as it will be printed and does not contain any child controls that provide access to report data. So, how do you retrieve the data from the report?
One possible solution is to use TestComplete's Optical Character Recognition feature. This approach may be difficult to implement because you will have to code scrolling operations and determine coordinates of the rectangles holding the desired text. Also, the recognition may produce incorrect results for wrapped lines.
Another possible solution is to export the report data to a file and then analyze the exported data. This approach is easier to implement and it does not depend on the way the data is displayed (no "text wrapping" problems). Below you will see how you can export a report and obtain data from a file.
In further explanations, I will use the Summary of Sales by Year report from the Northwind database that is shipped with Microsoft Access 2003.
Microsoft Access lets you export a report to files of various formats: snapshot, Microsoft Excel, HTML, RTF, Text or XML. We will export the report to an HTML file. Why HTML? I tried other formats and found that --
- Export to snapshot and RTF formats is useless as the resultant files do not provide access to report data.
- Export to Microsoft Excel did not work properly (at least, I was unable to see data in cells).
- Export to XML produced a large XML file that contains sales data rather than summary data (however, export to XML may be useful, if you decide to calculate the summary in your scripts yourself).
- Export to HTML and TXT produced the files storing the summary values. I selected HTML to avoid parsing text strings.
To export the report to the HTML format, follow these steps (you can even record a script to automate the exporting actions in the future):
- Right-click on the report in the Database window and select Export from the context menu. MS Access will display the Export Report dialog, where you can select the file name and format.
- Specify the desired file name in the File name edit field.
- Select HTML in the Save as type box.
- Press Export.
- MS Access will display the HTML Output Options dialog, where you can specify additional export parameters.
- Don't change any fields in this dialog and press OK.
MS Access will create a new HTML file holding the exported data. Now you can parse the file to obtain the report data and retrieve summary values from it.
To parse the file, we will use TestComplete's WebTesting plug-in. This plug-in provides access to elements of the web page that is shown in Internet Explorer or a WebBrowser control. To parse the exported data file:
- Open the File | Install Extensions dialog in TestComplete and make sure that the Web Testing plug-in is installed.
- Open the HTML file in Internet Explorer.
- Switch to TestComplete's Object Browser panel and explore the page contents.
- As you can see, the page contains a lot of element names like
Item(nn). If you use the DOM model, the object tree will look like this:
It may seem impossible to determine which Item corresponds to the desired web page element.
However, in this case we can take advantage of the Object Properties window and its Finder tool:
- Right-click somewhere within the Object Browser and choose Object Properties from the context menu. TestComplete will display the Object Properties window.
- Drag the Finder tool (
) from the Object Properties window to the web page. While dragging, a red rectangle will appear around windows, controls and web page elements that TestComplete recognizes as objects.
- Release the mouse button over the Totals for 1996 text. The Object Properties window will display properties and methods of the selected web page element. The Name property will contain the string that lets you address the element in scripts:
- Similarly you can obtain the scripting name for all summary cells. In our examples they are --
Item(73) - Totals for 1996
Item(154) - Totals for 1997
Item(213) - Totals for 1998
Now we can write a script that will retrieve data from the page:
The main routine is ParseHTML. At the beginning, this routine obtains the Page object that corresponds to the tested web page (on my computer, this page belongs to the explorer process, not iexplore). Then, the routine obtains the web page element by its index, retrieves data and posts them to the log:
The cell variable refers to the web page element that contains the summary text. We pass this variable to the GetRow function that returns the TR element for the desired cell. Why do we use a special routine and don't obtain the TR by using the cell.parentElement property? The text may belong to a child element (B, I, FONT) rather than TD. In our example, the text belongs to the FONT element. The whole hierarchy looks like:
<TR>
<TD>
<B>
<I>
<FONT>text</FONT>
...
The special GetRow function that we wrote walks up the hierarchy and returns the TR element containing summary values.
After we obtain the TR element, we pass it to the ProcessData function for processing. This function uses the call.items method to obtain the TD elements and then uses their innerText property to get values:
After you execute the script, you will see the report data in the test log:
You can use this concept anywhere -- export data to HTML, XML or any other format and then parse the file contents to obtain the data.