Learning Axys Reporting

April 10, 2013

Sending Reports through Email in Advent Axys

Filed under: guest post — Tags: , , , , , , , , , — admin @ 8:13 am

Guest Post by Carlos Capellán,Director of IT, J. Goldman & Co., L.P.

Thanks so much to Carlos Capellán for kindly submitting this post. Guest posts are welcome, and dialogue within the Axys community is highly encouraged.

Using Axys automation, you can send reports through email by using a few batch files and the free command line email utility Blat (http://www.blat.net).

Blat requires you to have an SMTP server set up on your network. If you run Microsoft Exchange, you can point blat.exe directly to your Exchange server; talk to your IT staff to determine any authentication/permission requirements. For example, we block access to SMTP from desktops as a security measure; we had to work around this for Axys.

I have three scripts/batch files:

  • BOTTOM LEVEL: email_report.bat – This batch file takes the generated report file and copies it to an archive directory, renaming it with the portfolio group and report date, and then runs blat.exe, using four parameters passed to it: report name, portfolio group, email address, and report date.
  • MIDDLE LEVEL: reprun.scr – This Axys script file runs the Axys reprun command with the supplied parameters, places the report in a temp folder, and calls email_report.bat using four parameters: report name, portfolio group, email address, and report date (hard coded to use the Axys {yest} parameter).
  • TOP LEVEL: master.scr – This Axys script file is a simple list of reports that need to be run that we allow our end users to edit directly in Notepad. Each line simply calls the reprun.scr script with three parameters: The Axys report macro name, the portfolio group, and the email address to send to. This script is run directly from the Axys scripting menu.

While the reports are run, the machine is tied up, so we actually set up a separate machine so our user can use their PC while we run around 170+ reports. Depending on the volume of reports you run, you might find it helpful as we did to put the user’s own email as the last item so you can confirm the entire batch has run through.

If desired a second version of reprun.scr and email_report.bat can be made to support sending other file types besides PDF by changing the –vs parameter in reprun.scr to the desired file format, and changing the .pdf extension in email_report.bat to the desired format.

On the next pages I provide samples of the three scripts/batch files.

Axys Script: master.scr


REM *************************************************************
REM ****************HOW TO EDIT THE MASTER SCRIPT****************
REM *************************************************************
REM Each line should begin with "reprun.scr". This is a script to
REM run a given report macro and send it out via email. Each row
REM needs three parameters seperated by spaces:
REM
REM 1. Name of the report macro -- This will show up in the email
REM subject line
REM
REM 2. Portfolio group the report is run for (Do not use the @ in
REM front of the port group, also the script is set up to run the
REM report in consolidated mode ("+" in front of port group))
REM
REM 3. Email address of recipient(s) -- Enter one or more
REM recipients separated by commas with NO SPACE in between
REM addresses in quotes
REM Example: "email1@domain.com,email2@domain.com"
REM *************************************************************
REM *************************************************************
REM *************************************************************

reprun.scr RepMcro1 grp1 bob@domain.com
reprun.scr RepMcro1 grp2 alice@domain.com
reprun.scr RepMcro2 grp2 alice@domain.com
reprun.scr RepMcro1 grp3 alice@domain.com
reprun.scr RepMcro1 grp4 charlie@domain.com
reprun.scr RepMcro2 grp4 charlie@domain.com

 

 

The master.scr file is located in your Axys install directory under the auto directory. RepMcro1 and RepMacro2 refer to Axys macro files recorded in the Axys Reports program and saved as .mac files. As you can see, the @ symbol is NOT placed in front of the portfolio group, it is added later.

 

Axys Script: reprun.scr


SET n=%0
SET p=%1
SET e=%2
SET z=$({yest})

reprun -m%n -p+@%p -vf -tX:\Axys3\Users\TEMP\%n -u
SHELL CMD /C x:\utility\email_report.bat %n %p "%e" %z

REM *************************************************************
REM **************FOR TROUBLESHOOTING PURPOSES ONLY**************
REM *************************************************************

REM SET n = RepMcro1
REM SET p = grp1
REM SET e = "user@domain.com"

REM *************************************************************
REM *************************************************************
REM *************************************************************

 

As you can see from the previous page, the three parameters passed in master.scr become the variables %n, %p, and %e. The parameter %z is set directly in the script using the Axys date shortcut {yest} for the last trading day.

 

The reprun command is called with –m parameter specifying the report macro to run (passing through %n), the –p for the portfolio group (here we tack on a “+” and “@” in front of %p), and we save it to the exact path specified in –t (avoid spaces; the macro name is being used for the report file name). The -vf specifies we want a PDF file. Refer to the Automating Axys manual for more documentation on the reprun command.

 

After the report is generated and saved to the directory location, email_report.bat is called to send the email, passing through the same macro name (%n), portfolio group (%p), email address (%e), and date (%z) parameters we saw earlier.

Windows Batch File: email_report.bat


SET n=%1
SET p=%2
SET e=%3
SET rdate=%4
x:
cd X:\Axys3\USERS\TEMP
move %n%.pdf X:\reports\%p%_%n%_20%rdate:~-2,2%-%rdate:~0,2%-%rdate:~2,2%.pdf
X:\Utility\blat x:\utility\blankmessagebody.txt -to %e% -f axys@domain.com -server smtp.domain.com -subject "Axys Reports for "%p%" -- "%n% -attach x:\reports\%p%_%n%_20%rdate:~-2,2%-%rdate:~0,2%-%rdate:~2,2%.pdf

 

 

The email_report.bat file takes the four parameters passed to it from reprun.scr. I forget why the parameter numbering is %0, %1, %2 in the Axys .scr files and %1, %2, %3 in the windows batch file, but I can say that it works!

 

After setting up parameters, the batch file changes to the directory where the report was saved to in reprun.scr and moves to a report archive folder while at the same time renaming it the file with the format <Portfolio Group>_<Report Macro Name>_20<Report date in YY-MM-DD>.pdf.

 

I assemble the report date by using some old-school DOS commands to get it in the right format so it sorts properly when viewed in a directory. For more info on string manipulation used in this example, please refer to this site: http://www.dostips.com/DtTipsStringManipulation.php#Snippets.MidString.

 

Finally the blat utility is called (the executable is copied to any directory, there is no installer) with the following parameters:

  • A text file for the message body is required, so a blank file is used.
  • -t is the recipient, the %e% email parameter is passed.
  • -f is the sender, set to axys@domain.com
  • -server is the SMTP server (ensure you are permissioned to use by testing blat by itself with a basic email first!)
  • -subject has the portfolio group and report macro name plus some standard text
  • -attach is the actual report attachment being sent with the same string as when the file was renamed in the move command earlier

 


Carlos Capellán

Director of IT, J. Goldman & Co., L.P.

2 Comments »

  1. I wonder if you can automate the save to encrypted pdf as well?

    Comment by Michael — April 11, 2013 @ 4:22 pm

  2. Although I have not personally done it, the free pdftk can add security as shown in http://www.pdflabs.com/docs/pdftk-cli-examples/. Maybe this will help. Let me know if anybody has any other ideas.

    Comment by admin — April 11, 2013 @ 6:11 pm

RSS feed for comments on this post. TrackBack URL

Leave a comment

Powered by WordPress