Stephen Smith's Blog

All things Sage ERP…

Reporting Via Macros

with 43 comments


Introduction

With our Sage 300 ERP 2012 release we updated our Crystal Reports runtime to the newest Crystal 2011 runtime (SP3 actually). The intent is to move to a fully supported version of Crystal Reports, so as they adapt to things like Windows 8 and Windows 2012 Server, we know we are fully supported and can get updates for any problems that show up. Plus it means that people customizing reports can take advantage of any of the new features there.

For reports you can print to preview, print to file or print directly to a printer. Then we have various options for printing from various web contexts like Quotes to Orders. You can drive reports from our regular forms, or you can write VBA macros that automate the reporting process.

This blog post is really for people that are controlling printing reports programmatically and are more affected by the changes in the Crystal runtime and more specifically changes in the Crystal Reports API.

Headache for Customizers

Our intent was that people performing customizations will use our API to drive Crystal Reports. Then your programs are upgrade safe since we maintain compatibility of our COM API. However it turns out that quite a few people have automated the report process by writing to the Crystal COM API directly.

This then leads to a problem because Crystal dropped support for their COM API. Not only did they drop support for it, but they removed it completely from the product. Hence anyone that is writing directly to the Crystal COM API will be broken by the Sage 300 ERP 2012 release. At least for new installs. If you had an older version and don’t un-install it, then you can still use the older version of the Crystal runtime (since it will be still there), but that isn’t a good long term solution as people upgrade computers and go to newer operating systems like Windows 8.

Crystal Reports now only supports a .Net Interface and a Java interface. For this version we had to change our internal interface to Crystal from COM to .Net. (The newer Web portal parts use the Java interface and so were ok).

Printing without User Intervention

It appears that one of the common reasons to go to the Crystal API directly is to print to file without any manual intervention. Often if you choose File as a print destination then we prompt you for the format and then prompt you for the file name to save to. People want to set these programmatically. Our API does have the ability to do this in a couple of situations.

Below is a macro I recorded to print O/E Quotes. I deleted any extra code, like the error handler to make it a bit more compact. Then I edited the destination and format to change the print destination to PD_FILE and the format to PF_PDF.

Sub MainSub()
Dim temp As Boolean
 Dim rpt As AccpacCOMAPI.AccpacReport
 Set rpt = ReportSelect("OEQUOT01[OEQUOT01.RPT]", "      ", "      ")
 Dim rptPrintSetup As AccpacCOMAPI.AccpacPrintSetup
 Set rptPrintSetup = GetPrintSetup("      ", "      ")
 rptPrintSetup.DeviceName = "HP LaserJet P3010 Series UPD PS"
 rptPrintSetup.OutputName = "WSD-ad0e8bc6-396c-4e50-84c7-fab17beaf18a.006a"
 rptPrintSetup.Orientation = 1
 rptPrintSetup.PaperSize = 1
 rptPrintSetup.PaperSource = 15
 rpt.PrinterSetup rptPrintSetup
 rpt.SetParam "PRINTED", "0"              ' Report parameter: 4
 rpt.SetParam "QTYDEC", "0"               ' Report parameter: 5
 rpt.SetParam "SORTFROM", " "             ' Report parameter: 2
 rpt.SetParam "SORTTO", "ZZZZZZZZZZZZZZZZZZZZZZ"   ' Report parameter: 3
 rpt.SetParam "SWDELMETHOD", "3"          ' Report parameter: 6
 rpt.SetParam "PRINTKIT", "0"             ' Report parameter: 7
 rpt.SetParam "PRINTBOM", "0"             ' Report parameter: 8
 rpt.SetParam "@SELECTION_CRITERIA", "(({OEORDH.ORDNUMBER} >= """") AND 
     ({OEORDH.ORDNUMBER} <= ""ZZZZZZZZZZZZZZZZZZZZZZ"")) AND (({OEORDH.COMPLETE} = 1) OR 
     ({OEORDH.COMPLETE} = 2)) AND ({OEORDH.TYPE} = 4) AND (({OEORDH.PRINTSTAT} = 1) OR 
     ({OEORDH.PRINTSTAT} = 0) OR ({OEORDH.PRINTSTAT} = -1))"   ' Report parameter: 0
 rpt.NumOfCopies = 1
 rpt.Destination = PD_FILE
 rpt.Format = PF_PDF
 rpt.PrintDir = "c:\temp\quote.pdf"
 rpt.PrintReport
End Sub

 

Basically this technique will silently export to PDF files. The Format member also accepts PF_RTF which will export silently in RTF format. The file they export to is specified in the PrintDir property. If this is a folder, the filename will be the same as the report name, if it’s a filename, it will be that (make sure you get the extension right).

You can also export silently to HTML format by setting the Destination to PD_HTML. For HTML if there is one file then the PrintDir specifies the filename, but sometimes HTML required multiple files, in which case it will use the PrintDir as a folder name.

These are a few ways you can print reports to a file silently without user intervention.

Secret Parameters

In the recorded macro above, you might notice the strange parameter “@SELECTION_CRITERIA”. If you look in the report, there is no such report parameter. Basically our API lets you set report parameters and then print the report. However there are a few other things you might want to do with the Crystal Reports API. Below is a list of these special parameters that might help you get a grip on some more aspects of the Crystal Reports API:

“@SELECTION_CRITERIA”: PESetSelectionFormula (job, asParamValue). This parameter is changed into the API to set the Selection Criteria in the report.

“@SELECTION_CRITERIA_xxxx”:  where  xxxx is the name assigned to the subreport when it was first created. This call will be translated into

job = PEOpenSubreport( parentJob, xxxx)
PESetSelectionFormula (job, asParamValue)

To set the selection criteria in the designated subreport.

“@SELECTION_ADDCRITERIA”: will add the parameter specified to the selection criteria that exists inside the report.

“@TABLENAME”: The parameter value must be in the form:

“table” “name”

Each instance of “table” will be switched to “name” before each PESetNthTableLocation call. This is done for both the main report and all subreports. You must put the table and name in quotes or the parameter will be rejected. Table names are not treated as case sensitive.

“EMAILSENDTO”:   The call will be set the email address when you are using PD_EMAIL.  This will suppress the popping up of the address book dialog.

“EMAILSUBJECT”: The call will be set the email subject when you are using PD_EMAIL.

“EMAILTEXT”: This parameter will be set the email body when you are using PD_EMAIL.

“EMAILPROFILE”:  This parameter will be set the email profile when you are using PD_EMAIL.

“EMAILPROFILEPWD”: This parameter will be set the email password when you are using PD_EMAIL. This password will be used to sign-in to MAPI.

The following are some parameters that if you create them in your report, then the system will automatically set them without you having to do any programming.

“CMPNAME”: The company name.

“ACCPACUSERID”: The Sage 300 User ID.

“ACCPACSESSIONDATE”: The session date from signon.

“ALIGNMENT”: The current alignment option.

“REGIONALFMT” : The current regional format.

Summary

We went through this Crystal API pain once before when Crystal dropped their DLL interface (crpe32.dll). At that point we had to change all API access over to COM. At that point we had similar issues due to various people using the DLL interface and now having to re-code for the COM interface. At that point we did add some functionality to our API, namely we added the TABLENAME special parameter, so that a number of people could start using our API.

Hopefully most people can switch from using the Crystal COM API directly to using the Sage 300 ERP API, however if something is missing, please comment on this blog, so we can consider expanding our API in the future. I’ve already gotten a couple of requests to add silently exporting to Excel format in addition to RTF and PDF. Keep in mind that like we have gone from DLL to COM to .Net, chances are in a few versions, the Crystal API will change again (perhaps to a REST web services API) and we will have to go through this again.

About these ads

Written by smist08

September 15, 2012 at 7:50 pm

43 Responses

Subscribe to comments with RSS.

  1. will Sage remove the requirement with xxrpt.ini in the future so we can pass parameter directly to Crystal without the need of ini modification?

    Khanh

    September 16, 2012 at 2:51 am

    • This is a good suggestions, noted.

      smist08

      September 20, 2012 at 3:01 am

  2. Hi,

    Does the Sage 300 ERP 2012 contain full Crystal Report 2011 .NET library? We have a few external applications using Crystal Reports .NET library, and distributing the runtime as a part of standard installation. It will be fine if the runtime will be already there:)

    Sergey

    September 17, 2012 at 6:30 am

    • It should be. We install the standard Crystal Runtime redistributable package and then use the .Net library to run reports ourselves.

      smist08

      September 17, 2012 at 4:39 pm

  3. Just 3 questions:

    1) We clearly prefer to use the Sage 300 API for reporting, but we sometimes have to add tables to Sage 300 databases. The Sage 300 API seems to have trouble with the data source for these tables, so we have to use the Crystal API directly to get around this. Are we missing something in the Sage 300 API, or does this mean we will have to migrate to a .NET solution?
    2) Some of our customers do not or cannot have a MAPI client, or we have unattended processes resulting in emailed documents. Therefore there is a frequent need for email via SMTP. Will this be available in the Sage 300 API?
    3) Not sure what the @TABLENAME parameter is used for. In what circumstances would it be used? Better still, rather than keep asking here, is there a reference source for this sort of information for all those who use the API but are not on the developer program?

    Andy

    September 19, 2012 at 10:06 am

    • 1) The intent is that we only set the login information for the tables not in our data dictionary, so that we don’t interfere with other tables (usually existing in another database). This might currently be broken, so beware. This way people can hard code the information for a given site in the report or a data source.
      2) Currently no plans, but probably not a bad idea. This sort of thing is why people want the silent export to file, so they can export the reports from our API then attach and send them through emails throw another means.
      3) This is intended for when you dynamically create tables, so if say you create a new table each day like tmonday, then you can dynamically set the report to the correct table at runtime.

      smist08

      September 20, 2012 at 3:00 am

  4. Hi Stephen

    Just a quick question on the Crystal Runtime, I thought the new version allowed us to make use of .RPTR files to block modifications by customers/third parties. I’ve tried this but it doesn’t work, I just get an error in Accpac when I try to run the report. Am I missing something?

    I saw this stated in the new release notes but I can’t remember if it’s on launch or due in a future product update for Accpac.

    Thanks
    Andy

    Andy Pickup

    September 22, 2012 at 10:27 am

  5. Crystal Report Web and Java viewer (v13 & 14) has known issue with displaying unicode for some languages (e.g.: Turkish, Vietnamese, Russian…) while ActiveX viewer still display properly.
    Is it fixed in this 2011? If it’s still there, our clients won’t be able to upgrade to ERP 300 2012 since your viewer will be .NET viewer isnt it?

    Khanh

    September 25, 2012 at 4:51 am

    • I mean .NET and Java viewer..

      Khanh

      September 25, 2012 at 4:52 am

    • We don’t work with those particular languages. But we do provide the Chinese (Simplified and Traditional) versions. These have been fine. For the Web Portal, the version of the Java Report Runtime and HTML viewer are the same as in 6.0A. The new Crystal 2011 .Net viewer is built off of the old ActiveX viewer, so I would hope its ok. I think the Crystal Reports team does a good job of addressing international issues, is I think it will be ok. But as always its best to test out your situation yourself.

      smist08

      September 25, 2012 at 8:16 pm

  6. A way to programmatically find out the parameters a report has would be useful. We can read the ini file but if the report is not in the ini file (eg a custom report) then we can’t.

    Dan Gibson

    October 12, 2012 at 4:53 am

    • That’s a good point. In the old days,CARET didn’t have a way to do that. But the Crystal API certainly does. Another work around is to run macro recording when you print the report.

      smist08

      October 12, 2012 at 4:03 pm

  7. We are using an ACCPAC VB macro to produce some custom Crystal reports. In previous versions there would be a pop up box that would show the progress of each crystal report as it was being produced. With sage erp 2012 that screen no longer appears so our users wonder if anything is actually happening when the macro is running. Is there a way to get it back?

    Andy Bruner

    April 10, 2013 at 3:47 pm

    • I passed this posting on to the developers maintaining the Crystal integration since I think this is a bug that needs fixing.

      smist08

      April 12, 2013 at 5:10 pm

      • thanks!

        Andy Bruner

        April 12, 2013 at 6:01 pm

      • Will there be any way to get an update from the dev team at some point?

        Andy Bruner

        April 15, 2013 at 12:34 pm

      • Just following up again. Can you find out if this will be fixed in the next version? Our users are still complaining to me about it. Thanks.

        Andy Bruner

        September 5, 2013 at 6:53 pm

  8. […] These can be either Crystal Reports of Financial Reports. I blogged on printing through macros here and a bit more information on customizing reports here. Plus from the Business Logic you have […]

  9. What is the best way to run a report from the Sage 300 Desktop that uses stored procedures as a datasource? Currently all I get is a “Cannot open SQL Server” error and the DBSPY shows that it cannot find the table Proc(MyProc;1). if using VBA what is the best way to use as much of standard Sage 300 functions to do this without having to store DB logon details outside of Sage 300?

    Thanks in advance

    Jacques Correia

    July 15, 2013 at 12:37 pm

    • What we do is set the database credentials for any table in our data dictionary. Ie for any table created via our API. Then we leave everything else alone. The intent here is that you can link to other databases or anything else you want. The downside is that you do have to store the DB logon information in the report.

      smist08

      July 15, 2013 at 3:26 pm

      • Thank you, appreciate the feedback. Sage 300 does not seem to ignore the stored procedure and then fails the complete report. Could be an issue because it is a procedure call. If it ignored it we could go the route of setting logon in the report. I am looking at options for a report launcher that uses windows authentication as I do not want to maintain logon info separately from Sage 300. If I could get access to the SQL logon detail that Sage uses then that would also be an option.

        Jacques Correia

        July 16, 2013 at 12:47 pm

  10. In the above recorded macro to print O/E quotes, how could I alter the macro to allow the user to enter FROMSELECT and TOSELECT parameter values? I tried by creating variables and setting them using InputBox, but ran into problems I can’t solve.

    Chuck Emery

    August 28, 2013 at 4:35 pm

    • There aren’t parameters for FROMSELECT or TOSELECT in this report. You would need to ask for these and get them in variables then build a new @SELECTION_CRITERIA parameter string where these are substituted into the correct places.

      smist08

      August 28, 2013 at 5:10 pm

  11. The CHM file shows nothing in the right panel on my 64 bit Windows 7 computer. What do I need to add to see it?

    My associate can’t find any Macro information in the Sage 300 User Guides version 5.4 to 6.1, could you be more specific where these chapters can be found?

    Thanks

    Chuck Emery

    August 30, 2013 at 5:00 pm

    • If you look in one of the older versions like 5.5A there is a “Sage Accpac SM User Guide”.pdf which contains a “Chapter 13: Using Macros” and “Chapter 14: Writing Visual Basic Macros”. I think the file is being blocked by Windows security, probably because it sees it has been downloaded from the Internet. Make sure its on a local drive (Windows help files won’t load from a shared or network drive) and perhaps try using a cmd prompt to copy it to another folder (so Windows forgets you downloaded it).

      smist08

      August 30, 2013 at 7:54 pm

      • I’ve found the 5.5A document. Thanks very much. As to the .CHM file, I have a suspicion that what gets downloaded, ACCPACControlsRef.chm, and ACCPACControlsRef.chw, which are 1 and 0.1 Mb in size, are not the full thing, so no solution putting them anywhere special. What I’d like is the file ACCPACControlsRef.zip, which I can see the name of using the URL above, but how can I download it?

        Chuck Emery

        August 31, 2013 at 5:10 pm

  12. I’ve downloaded it with current firefox using the File Download commands on the site. Moved it to folder and extracted ACCPACControlsRef.chw and now see the same error code provided from earlier downloads. In the right hand panel:

    “Navigation to the webpage was canceled

    What you can try:
    Retype the address. ”

    No idea what address to retype or where

    is there an alternative place where a working copy of this file might be available, or is there a supporting website which has been taken down? Sorry for all the trouble.

    Chuck Emery

    August 31, 2013 at 5:17 pm

  13. That does it. At last.

    Thank you

    Chuck Emery

    September 1, 2013 at 3:19 am

  14. I am having trouble with a macro similar to your recorded macro, using Sage ERP 300 2012, and wonder if you can help.

    I have inserted an input box to allow the user to set the starting and ending order numbers, and I wish any previously printed orders to reprint.

    The macro works the first time only, the second and subsequent attempts using the same order numbers yield a blank report and an error: SAGE ERP 300 VBA has encountered a problem and needs to close…

    My feeling is that I need to use the ReInit() function, but not sure where to insert it into the code.

    Sub MainSub()

    ‘ TODO: To increase efficiency, comment out any unused DB links.
    Dim mDBLinkCmpRW As AccpacCOMAPI.AccpacDBLink
    Set mDBLinkCmpRW = OpenDBLink(DBLINK_COMPANY, DBLINK_FLG_READWRITE)

    Dim mDBLinkSysRW As AccpacCOMAPI.AccpacDBLink
    Set mDBLinkSysRW = OpenDBLink(DBLINK_SYSTEM, DBLINK_FLG_READWRITE)

    ‘ Input Box
    Dim FromNum As String
    FromNum = InputBox(“Starting Order”, “Print Picking Slips”)
    Dim ToNum As String
    ToNum = InputBox(“Ending Order”, “Print Picking Slips”, FromSelect)

    Dim temp As Boolean
    Dim rpt As AccpacCOMAPI.AccpacReport

    Set rpt = ReportSelect(“OEPICK01[C:\USERAPPS\SAGE\SAGE ACCPAC\OE61A\ENG\OEPICKORDER2.RPT]“, ” “, ” “)

    Dim rptPrintSetup As AccpacCOMAPI.AccpacPrintSetup
    Set rptPrintSetup = GetPrintSetup(” “, ” “)
    rptPrintSetup.DeviceName = “Brother HL-2270DW series”
    rptPrintSetup.OutputName = “BRW008092889918″
    rptPrintSetup.Orientation = 1
    rptPrintSetup.PaperSize = 300
    rptPrintSetup.PaperSource = 7
    rpt.PrinterSetup rptPrintSetup
    rpt.SetParam “SELECTBY”, “0” ‘ Report parameter: 2
    rpt.SetParam “SORTBY”, “1” ‘ Report parameter: 3
    ‘rpt.SetParam “FROMSELECT”, “ORD000000000067″ ‘ Report parameter: 4
    ‘rpt.SetParam “TOSELECT”, “ORD000000000067″ ‘ Report parameter: 5
    rpt.SetParam “FROMLOC”, ” ” ‘ Report parameter: 6
    rpt.SetParam “TOLOC”, “ZZZZZZ” ‘ Report parameter: 7
    rpt.SetParam “PRINTBY”, “0” ‘ Report parameter: 14
    rpt.SetParam “SERIALLOTNUMBERS”, “0” ‘ Report parameter: 15
    rpt.SetParam “PRINTKIT”, “0” ‘ Report parameter: 11
    rpt.SetParam “PRINTBOM”, “1” ‘ Report parameter: 12
    rpt.SetParam “REPRINT”, “1” ‘ Report parameter: 8
    rpt.SetParam “QTYDEC”, “0” ‘ Report parameter: 9 – O/E Sales History:Detail,Sort by Item Number
    rpt.SetParam “COMPLETED”, “0” ‘ Report parameter: 10
    rpt.SetParam “SESHNDL”, “861909” ‘ Report parameter: 13
    rpt.NumOfCopies = 3
    rpt.Destination = PD_PREVIEW
    rpt.PrintDir = “”
    rpt.PrintReport

    End Sub

    Sanda

    September 5, 2013 at 10:57 pm

  15. Hi Stephen,

    I am trying to use the ODBC.command (Sql command line for datasource) in crystal report for accpac.
    I notice that the datasource of the sql command line do not change by company when running the report from diff company.

    Can this feature in crystal be used in accpac reporting?

    et

    May 4, 2014 at 4:44 pm

    • This is probably something that our API doesn’t know about, so won’t touch it. You might try using @SELECTION_CRITERIA instead.

      smist08

      May 4, 2014 at 5:23 pm

  16. How could I suppress the generation of an exported PDF file if there are no records?

    Rodney

    May 7, 2014 at 3:14 pm

    • All I can think of is to check beforehand using the API to see if there are any records to be reported and if not then don’t run the report process.

      smist08

      May 7, 2014 at 3:31 pm

      • Thanks – amd just another thing, I guess I need to then update the record in OEINVH to reflect that the report has been printed?

        Rodney

        May 7, 2014 at 3:33 pm

      • If there is an invoice to be marked as printed, then wouldn’t something be in the PDF?

        smist08

        May 7, 2014 at 3:37 pm

      • Sorry… I had changed the subject. :) New question, after printing a document that needed printing, (i.e. INVPRINTED (in the case of OEINV) was 0 at running the macro, after the macro runs, I would need to update the INVPRINTED = 1 so that it did not print the next time the macro ran. Sorry for the confusion. And thanks for a great blog. Very useful info.

        Rodney

        May 7, 2014 at 3:41 pm

      • Then yes marking them as printed would be good.

        smist08

        May 7, 2014 at 3:46 pm

  17. When trying to automate this using a small vb.NET program I am having trouble with the equivilent for these two statements in vb.NET.

    Set rpt = ReportSelect(“OEQUOT01[OEQUOT01.RPT]“, ” “, ” “)

    and

    Set rptPrintSetup = GetPrintSetup(” “, ” “)

    Are you able to help with the proper way to express this in vb.NET ?

    Thanks.

    Rodney

    May 7, 2014 at 8:46 pm

    • Worked it out.

      Dim mSession As New ACCPAC.Advantage.Session
      mSession.Init(“”, “XY”, “XY1000″, “61A”)
      mSession.Open(“ADMIN”, “ADMIN”, “SAMINC”, DateTime.Today, 0)

      rpt = mSession.ReportSelect(“OEINV01[OEINV01.RPT]“, ” “, ” “)
      rptPrintSetup = mSession.GetPrintSetup(” “, ” “)

      Rodney

      May 7, 2014 at 11:50 pm


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 264 other followers

%d bloggers like this: