In previous articles, I have written about the ability to take an Advent Report Writer report and make changes to it in the underlying REPLANG code. Though the program is actually called Report Writer Pro, I won’t be referring to it by that name since the “Pro” part seems a bit much.
In this article, I will take you through the basics of this process and also share some code I wrote recently to deal with a troublesome Report Writer exception. Please note, there are many modifications that can easily be done within the Report Writer interface, but this article is not about that.
You should have a good reason for attempting to modify Report Writer reports outside of the app. For me, two fundamental issues drive this decision. The modification either cannot be handled due to limitations of Report Writer or it is simply much more expedient to hack and slash REPLANG than it is to work within the confines of the Report Writer environment.
Okay. Let’s assume that like me you think you have a good reason to do this. There are a few things you should know before you modify a report created by Report Writer outside of Report Writer. First of all, any report created in the Report Writer has a RPW extension, but the underlying format is REPLANG. In most cases, any report with a REP extension was not created by Report Writer – except of course for reports like the ones we are talking about creating.
Second, your report containing code not created by Report Writer can no longer be modified by Report Writer in the future, so make a backup of the RPW file before any changes, and save the file you modify as a REP file. That way if you need to make a change to the report later – that Report Writer would be better suited to make – you can. In that scenario, once you have updated the report you can reapply your manual updates to the newer RPW file and then save it as REP report again.
The reason that your new REP report cannot be modified in Report Writer is not only due to the fact that the extension isn’t an RPW, but more specifically that a checksum created by Report Writer when it was last modified by the software will no longer match. The logic behind why this is done is understandable. Report Writer only works with certain predefined templates and does some really impressive things, but it is not designed to interpret code changes that were not created by Report Writer.
Lastly, if you haven’t waded into REPLANG code created by Report Writer it will take some getting used to. By nature of the fact that RPW files are created to be extensible the REPLANG code is more abstract. In other words, the code generated by Report Writer appears far less direct with regard to its purpose than code that an individual might write for a singular and well-defined purpose. If you are not familiar with REPLANG coding, I wouldn’t recommend trying to modify REPLANG reports created by Report Writer.
Some examples of the types of things I find myself doing when I modify Report Writer reports from REPLANG directly follow:
- I need to do a calculation that I find difficult or impossible within the Report Writer interface. If you have any experience using Report Writer to create user-defined formulas, this really isn’t that hard to imagine.
- Adding a piece of data that isn’t readily available from the Report Writer.
- For expediency sake, I know how to do something in REPLANG in a minute, but doing it in Report Writer might take an unreasonably long time.
Dealing with Report Writer Exceptions
Recently, while working on some reporting extracts for a client, I attempted to take what appeared to be a relatively simple Report Writer report and change the format to a CSV file. Doing so is a basic function of the product and only requires a few mouse clicks. It is something I tend to do on a regular basis and it usually works well. However, this time Report Writer went haywire.
Instead of taking the twenty-two thousand line report and making another similar sized report as I expected, it created a ridiculously long report (hundreds of thousands of lines of code) that Report Writer could not test or run. Even a twenty-two thousand line report sounds big and it is. By comparison, the longest standard report(persave.rep), which updates performance files, is under four thousand lines. Most of the standard reports are significantly less than a thousand lines of code.
It is not the first time I have seen this problem, but this time I decided to do something more proactive to deal with this issue in the future. The report that I was attempting to change is fairly simple. There are no summary records. So, the output is just a table of detail records and that helps to simplify the coding requirements of what I want to do. To deal with this issue in the past, I have simply renamed the RPW to a REP file and modified 10 to 20 lines specific to placing the output on the screen and reformatted them to be CSV friendly.
There are alternative ways to do this. You could create the Excel file output manually by exporting the report output to Excel once the report has been generated and saving it, or by writing a script to generate an Excel file, but in this particular instance I preferred to create a more flexible CSV report. We could also do a standard search and replace function within a text editor, but having seen this issue more than a few times I wanted to create a utility I could use going forward.
So I quickly wrote the code in the section below to change the original report, which was sending output to the report view screen, to send all the output directly to a file. I find it very easy to code things like this using Visual Basic (VB) or Visual Basic for Applications (VBA). Since most users also have access to VBA via Microsoft Office, it is a programming option you can find and use on almost any PC. As such, I frequently use VBA when I am doing spontaneous and simple coding tasks or projects that don’t require a more complex development environment. I am not a huge fan of verbose comments, but I thought they might be useful here.
Sub MAIN()
' This routine takes a REPORT WRITER report which sends output to the
' screen and creates a report that sends output to a file. The routine
' was created to address an exception where the normal function to make
' a change in a RPW file created an obscenely large Report Writer file
' that couldn't be tested or run.
' written in VBA by Kevin Shea (aka AdventGuru) & published 06/26/2019
' Disclaimer: This routine works fine for the specific instance it was
' created for, but could need additional modifications for different
' circumstances.
'Initialize variables
Dim InputFileHandler As Integer
Dim OutputFileHandler As Integer
Dim Filename$
Dim Record$
Dim IgnoreRecords As Boolean
Dim ReportLocation$
Dim OutputFolder$
ReportLocation$ = "f:\axys3\rep\"
OutputFolder$ = "f:\axys3\exp\"
' Axys users will probably need to change the two locations above to
' match their actual folder locations of Axys.
' APX users, depending on the APX server name, the preceding statements
' might need to change to ReportLocation$="\\apxappserver\APX$\rep\"
' and OutputFolder$ = \\apxappserver\APX$\exp\"
InputFileHandler = FreeFile 'fetch an unused file handler
IgnoreRecords = False
Filename$ = InputBox("Report file name?")
Open ReportLocation$ + Filename$ For Input As #InputFileHandler
OutputFileHandler = FreeFile 'fetch an unused file handler
Open ReportLocation$ + Left$(Filename$, Len(Filename$) - 4) + ".out" For Output As #OutputFileHandler
Do While Not EOF(InputFileHandler)
Line Input #InputFileHandler, Record$ 'read a record from the report
If Left$(Record$, 4) = "head" Then IgnoreRecords = Not (IgnoreRecords)
' REPLANG marks the beginning of the header definition with the
' statement "head" and ends the header definition with the same
' statement. We don't want to write the header to our output file,
' so we are going to ignore all of the code between the two head
' lines of code.
If IgnoreRecords = False Then
If InStr(Record$, ".") > 0 Then
' The "." in REPLANG is the syntax usually associated with
' sending output to the screen most, but not all, "." lines
' end with "\n", which marks the end of the output line. By
' making the "." a requirement of this logic we will only
' process REPLANG statements that create output.
For i = 1 To Len(Record$)
'ignore any code lines that are less than two characters
If i > 1 Then
' We may not need to be this specific, but I want to err
' on the side of caution. We will only process lines that
' start with a period. This prevents us from processing
' other lines that may have periods in the remarks or other
' areas of the REPLANG report.
If Trim$(Left$(Record$, i - 1)) = "" And Mid$(Record$, i, 1) = "." Then
'Somewhat self-explantory...
Record$ = Replace(Record$, "~", ",")
Record$ = Replace(Record$, "#,", ",#")
Record$ = Replace(Record$, " ,", ",")
Record$ = Replace(Record$, "?", "")
Print #OutputFileHandler, Record$ 'write the record we modified
Exit For
'Exit logic early if we are done processing the record.
End If
End If
Next i
Else
' This bit of code inserts the code that writes all records
' that weren't modified, but contains some minor code insertion
' (outfile) make the output go to a file. The proper place to
' insert the outfile statement is before any accounts are
' processed, which is immediately before the load cli statement.
' Writing to a file requires that we close (fclose) the file
' or the result will not get written properly. The right place
' to insert the fclose statement is when all of the processing
' has been completed, which is immediately after the next cli
' statement.
If Left$(record, 8) = "load cli" Then Print #OutputFileHandler, "outfile "+OutputFolder$+"outfile.csv n"
Print #OutputFileHandler, Record$
If record = "next cli" Then Print #OutputFileHandler, "fclose"
End If
End If
Loop
Close #OutputFileHandler
Close #InputFileHandler
End SubAs to why Report Writer erroneously attempts and fails to create such a long report, it likely has something to do with user-defined calculations and parsing of strings. However, for the purpose of this article we are not trying to fix that issue. It may just be a limitation of the Report Writer.
About the Author: Kevin Shea is the Founder and Principal Consultant of Quartare; Quartare provides a wide variety of technology solutions to investment advisors nationwide. For details, please visit Quartare.com, contact Kevin Shea via phone at 617-720-3400 x202 or e-mail at kshea@quartare.com.