Sage 100

I've been working on this stuff so long, i think I've forgotten as much as i still know...I'm wondering about launching a report from a script button in Sage 100. I've seen posts about launching std reports. I'm actually trying to run a custom report. Is this doable?

It depends. How integrated must the report be? Or are you simply launching a 'standalone' Crystal Report (and the user has Crystal installed locally)?

That's a possibility Steve. In a perfect world, I'd like the report to auto-preview for the entity i'm on when i run the script. For instance, i'm on a specific customer in Customer Maintenance. I'd like to automatically run the report for the customer i'm on by clicking a button.

I was thinking you could use a button script to collect the CustomerNo and then execute Crystal, but you would then need to pass the CustomerNo to Crystal as a parameter from a command-line, and that's the part that won't work easily. I know there are third-party add-ons that could do it, but I don't think there's a native way.

Here is a script I use at a customer to print custom labels in shipping data entry. It requires a silent DSN or else it will pop up a MAS login prompt. Not sure where I got the original source - it was many years ago - there are some options commented out. Dim App, RptFile, User, Comp, Pw, Dest, RptSelect Dim Rept, Login, ExportFile, ParamFields, Year Dim NumberOfCopies '********************************************************* ' **** SET VARIABLES ******************************************************************************** 'Report File: Change to the full path and file name RptFile = ""\\server\acct\MAS 200\Version4.4\MAS90\Reports\Custom\ScriptLabelShipping.rpt"" 'Export File: Change to the full path and file name ExportFile = ""<full_path_to_export_file.csv>"" ParInvoice = Cstr(SO_Shipping_bus_InvoiceNo) NumberOfCopies = inputbox (""Enter the number of labels to print"",""Number of Copies"",""1"") ' language VBScript ' ' panel variables ' SO_Shipping_bus_InvoiceNo ' ' system variables ' MAS_SCR_CMP : company code ' MAS_SCR_USR : user code ' MAS_SCR_MOD : module code ' MAS_SCR_DTE : current app date ' MAS_SCR_LIB : library ' MAS_SCR_PNL : panel ' MAS_SCR_OBJ : control [BT_LINK_x] ' MAS_SCR_CS : 1 if running Sage MAS 200 on client ' MAS_SCR_DBG : 1 to show script before and following execution ' 'Login Info, you may want to setup a dummy user for all of this. 'User = MAS_SCR_USR 'Comp = MAS_SCR_CMP 'PW = """" 'UserInput( ""Enter Password"" ) 'Destination Export, Print or Preview - set it to 2 to Export, 1 to Print Dest = 1 ' Set App = CreateObject (""CrystalRuntime.Application"") Set Rept = App.OpenReport (RptFile) Set objShell = CreateObject(""WScript.Shell"") 'RegLocate = ""HKEY_CURRENT_USER\SOFTWARE\ODBC\ODBC.INI\SOTAMAS90\"" 'objShell.RegWrite RegLocate & ""UID"",USER 'objShell.RegWrite RegLocate & ""PWD"",PASSWORD 'objShell.RegWrite RegLocate & ""COMPANY"",COMP Login = App.LogOnServerEx(""p2sodbc.dll"", ""ScriptLabel"") 'Login = App.LogOnServerEx(""p2sodbc.dll"", ""ScriptLabel"", , , , , ""DSN=SOTAMAS90; UID=""+User+""; PWD=""+PW+""; Company=""+Comp) 'Login = App.LogOnServerEx(""p2sodbc.dll"", ""ScriptLabel"", , , , , ""DSN=ScriptLabel; UID=label; PWD=script; Company=USR"") ' **** SET EXPORT OPTIONS ******************************************************************************** ' **** FORMAT TYPE VALUES AVAILABLE 'crEFTNoFormat 0 'crEFTCrystalReport 1 'crEFTDataInterchange 2 'crEFTRecordStyle 3 'crEFTRichText 4 'crEFTCommaSeparated-Values 5 'crEFTTabSeparatedValues 6 'crEFTCharSeparatedValues 7 'crEFTText 8 'crEFTTabSeparatedText 9 'crEFTPaginatedText 10 'crEFTLotus123WKS 11 'crEFTLotus123WK1 12 'crEFTLotus123WK3 13 'crEFTWordForDOS 15 'crEFTQuattroPro50 17 'crEFTExcel21 18 'crEFTExcel30 19 'crEFTExcel40 20 'crEFTExcel50 21 'crEFTExcel50Tabular 22 'ODBC not permitted 23 'crEFTHTML32Standard 24 'crEFTExplorer32Extend 25 'crEFTNetScape20 26 ' **** NOW ACTUALLY SET THE OPTIONS Rept.ExportOptions.DiskFileName = ExportFile Rept.ExportOptions.DestinationType = 1 Rept.ExportOptions.FormatType = 5 ' 5 IS FOR COMMA-SEPARATED FORMAT Rept.ExportOptions.CharFieldDelimiter = """" Rept.ExportOptions.CharStringDelimiter = """" ' **** HANDLE USER INPUT AND REPORT PARAMETERS ********************************************************** 'batchNumber= UserInput( ""Enter Batch Number"" ) 'WScript.Echo ""You entered: "" & batchNumber 'beginInvNumber= UserInput( ""Enter Beginning Invoice Number"" ) 'WScript.Echo ""You entered: "" & beginInvNumber 'endInvNumber= UserInput( ""Enter EndingInvoice Number"" ) 'WScript.Echo ""You entered: "" & endInvNumber 'Set ParamFields = Rept.ParameterFields 'Set Invoice = ParamFields.Item(1) 'Invoice.SetCurrentValue ParInvoice 'msgbox ParInvoice Rept.ParameterFields.Item(1).SetCurrentValue Cstr(ParInvoice) 'Rept.ParameterFields.GetItemByName(""Invoice"").AddCurrentvalue ParInvoice 'Rept.ParameterFields.GetItemByName(""BegInvNbr"").AddCurrentvalue UserInput( ""Enter Beginning Invoice Number"" ) 'Rept.ParameterFields.GetItemByName(""EndInvNbr"").AddCurrentvalue UserInput( ""Enter EndingInvoice Number"" ) ' **** FINALIZE OPTIONS ********************************************************** If Not IsNull(Rept.ReportTitle) then RptWindow = Rept.ReportTitle else RptWindow ""Crystal Reports"" end If If Dest = 1 then Rept.Printout false, Cint(NumberOfCopies) End If If Dest = 2 Then Rept.Export(False) End If ' **** AWAY WE GO ********************************************************** 'set WshShell = WScript.CreateObject(""WScript.Shell"") 'While WshShell.AppActivate(RptWindow) = TRUE 'wscript.sleep 10000 'Wend 'Set WshShell=Nothing Set Rept=Nothing Set App=Nothing Set objShell = Nothing 'WScript.Echo ""Label Printed"" ' **** FUNCTIONS SECTION **************************************************************************************** Function UserInput( myPrompt ) ' This function prompts the user for some input. ' When the script runs in CSCRIPT.EXE, StdIn is used, ' otherwise the VBScript InputBox( ) function is used. ' myPrompt is the the text used to prompt the user for input. ' The function returns the input typed either on StdIn or in InputBox( ). ' Check if the script runs in CSCRIPT.EXE If UCase( Right( WScript.FullName, 12 ) ) = ""\CSCRIPT.EXE"" Then ' If so, use StdIn and StdOut WScript.StdOut.Write myPrompt & "" "" UserInput = WScript.StdIn.ReadLine Else ' If not, use InputBox( ) UserInput = InputBox( myPrompt ) End If End Function

I never saw Crystal launched as a COM object with VBScript. Very cool, @PhilMcIntosh!

I agree @SteveIwanowski! Thanks @PhilMcIntosh , although i am a bit worried about it working on newer versions of Crystal Reports. I seem to remember something about this not working a couple versions back. I'll give it a shot, though.

Well, let me know what happens, because that customer may upgrade soon...:)

Phil that example unfortunately won't work with version 2013 and higher. Flipping SAP intentionally did not provide support for VB scripts for Crystal 2011 .NET runtime version. IOW you have to create a C-Sharp .NET or VB .NET app that can connect to the runtime and put a BOI button on your MAS screen to kick it off. Having said all that, your script (which incidentally is in the class materials too - look on the 2nd tab on the Excel file) will absolutely work on v2013 and v2014 if it the machine happens to have the Crystal XI R2 runtime (not dot net version) on it. IOW if you're upgrading a client from say 4.50 and do not uninstall Wkstn Setup (or perhaps even if you do), then the script works on 2013 / 2014 (because the main Craxdrt.dll is still there ergo so is CrystalRuntime.Application in the registry). Conversely if you were to somehow ""accidentally"" run 4.50 Wkstn Setup on a brand new 2013/2014 wkstn, it would also also work. One more thing. There is another way to deal with it. Instead of your script directly previewing a report, it can use the listing object. IOW when you choose a report from Custom Reports menu and you get the dialog to Print / Preview / Setup, well you can invoke that in a script. It would mean the customer would have to press a button twice before their report previewed but it would work. I'll post an example if you want. @Sage100AdvancedScripting

Thanks Alnoor, i thought that SAP had changed something to screw that up. I'd be interested to see the launch of the print, preview. setup option. Would it be possible to pass data to the report as a parameter ""auto-fill""?

So for 2013+ (assuming no legacy workstation installs) you could put the Crystal in a dotNet app and pass the parameters as arguments when launching the program from a script? @AlnoorCassim - Was my source for that script a post of yours from whatever the Sage forums were back when 4.4 was current?

I ""accidentally"" did what @AlnoorCassim suggested by installing the v4.5 WK client on a v2013 workstation. It does indeed work. Unfortunately, when I upgraded client to v2014, it stopped working and I have yet to figure out why even though I can get same script to launch report in my test version of v2014. Still scratching on the reason why it is not working.....

Here is the listing example where the dialog for Print / Preview / Setup appears. I'm not sure if you have to first add your report to Custom Reports menu. If IsObject(oListing) = False Then Set oListing = oSession.AsObject(oSession.GetObject(""SY_Listing_ui"")) End If retVal = oSession.InitiateUI() retVal = oListing.Process(""SY_ReportManagerGeneratedListing_rpt, ItemWhse_PO2_IM2_custom.RPT,Unmatched Item/Whse pairs"") In oListing the 2nd argument is name of the RPT file, the 3rd is the description that shows on the listing dialog. There is also another direct way where you can actually hit the Print or Preview button without having the listing dialog appear first. It's something like this but I can't get it to work (yet): oRpt = GetObject(SY_ReportManagerGeneratedListing_rpt, <name of rpt>, <desc>) If oRpt <> 0 Then Set oRpt = oSession.AsObject(oRpt) End If retVal = oRpt.ProcessReport(""PREVIEW"") ' or PRINT or DEFERRED maybe Paperless too Phil - Yes your understanding is absolutely correct on launching it as a dotNet app. That's what Sage 100 actually does too but it's through their own Sage Reporting DLL. I don't remember the name off the top of my head. If we can just find any doc on it our script could do CreateObject(""SageReporiting-whatever it is called in registry""), then pass the RPT file and a few other params as arguments to it and we are home free. This would be the best way, better than either of the above. The source for your script may have been mine, or Dawn Anastasi's, or @DanBurleson , or somebody from SageTalk I don't remember. It was pretty much open source :)