This is a utility that I wrote to identify differences in WebI reports during an upgrade. I built it for an XIr2->XI3 upgrade, but it can be used to compare reports on any combination of versions or patch levels (such as before and after an SP). It works by comparing the Excel output of the report from the two environments.
There are two parts to ExtractXLS – one Java and one Excel VBA.
[list][:7c860e6742]The Java code is run first, in each BO environment. This will extract all reports in a folder (and its subfolders), and save them as an Excel file in c:\extractxls\output.
[:7c860e6742]The Excel VBA code is then run to do the comparison. It will open each pair of reports and do a cell-by-cell comparison. Cells with different values are colored yellow; reports with at least one difference are saved and the number of found differences recorded on the “Results” tab.[/list]
What this does not do[list][:7c860e6742]Check DeskI, Crystal, or anything else other than WebI.
[:7c860e6742]Check instances (through this can be changed in the code if necessary; I figure if the parent report is ok, then instances will be too)
[:7c860e6742]Refresh the reports (though this can easily be done with one line in the Java program
[:7c860e6742]Identify any visual differences (formatting, placement, etc.)
[:7c860e6742]Identify any differences in the reports’ SQL or microcube
[:7c860e6742]Identify any other differences in the report that do not directly affect the generated output[/list]
Prerequisites
[list][:7c860e6742]The two BO environments must be up and running.
[:7c860e6742]The reports must have been recently migrated from one environment to the other. Since the utility does not refresh the reports, you will get incorrect results if the report has been refreshed and saved in one environment but not the other.[/list]
Running the utility
Modify the Java program to suit your environment. You will need to change:
[list][:7c860e6742]Logon credentials
[:7c860e6742]Folder name to recurse through
[:7c860e6742]fileSuffix (“XIr2” or “XI3”, or any other names to identify the two environments being compared
[:7c860e6742]outputCUID as true or false. Setting this to true will export the files using the report’s CUIDs as the file name; setting it to false will use the report’s name. Using CUID guarantees that there will be no collision of naming if reports with the same name exist in multiple subfolders, but makes it more difficult to work with the generated files.
[*:7c860e6742]Change the output directory if you want to save the reports somewhere other than c:\extractxls\output (search for “extractxls” in the code)[/list]
Compile and run. The program will output a list of reports extracted, along with the name and CUID.
Open up CompareReports.xls and go to the VBA editor. Set fileSuffixOne and fileSuffixTwo to match the values you used for fileSuffix in the Java program. Also search for and change the value of “homedir” as necessary.
On the “Results” tab, hit the Start Check button. This will open each pair of reports in the output directory, compare them, and save the ones with identified differences. The count of differences found will be in the “Differences” column.
One the compare is done, you can open each pair of reports to see the identified differences. Click on any cell in the applicable row and hit the “Open File” button.
I found that there were some differences in float values less than .000001. I didn’t care about these, so I excluded them from the compare logic. You can change this if you like in the CompareReport function.
(BOB member since 2002-08-29)
(BOB member since 2003-11-27)