Sage 100

What DSN should you use for SAGE100 Prremium custom reports that run off menu.... Answer: I saw this on the Sage City and I thought I would pass it on. The point is if you create a report in Sage 100 Premium with dynamic parameters you need to use the MAS_Reports 'System' DSN with the MAS_Reports login or a user defined login with at least a 'Public' Server Role. Also use a single ODBC data source. >>> From Sage Forum I created a Crystal XI label report, Sage 100 SQL and am including a dynamic parameter for PO_Header batch number. If I run the report (no work tables) from within the Library Master Report Manager, it runs without error. However, when I pin it to the custom reports menu and run the report from there, it prompts me for the SQL server user name and password, and only the sa user and password will allow it to run. If I change the dynamic parameter to static, it runs correctly both from LM Report Master and from within the custom reports menu. I have tried both SOTASQL and the OLE DB SQL connector. Any thoughts? Thank you in advance for your assistance. Posted by jcnichols on19 Jun 2013 5:00 PM Hi Brian, All Sage 100 Premium (SQL) reports including user defined reports added to the custom reports menu connect to the DB (Sage 100 Data) using the MAS_Reports SQL login. The MAS_Reports login is created during Sage 100 installation and maintainable in the Windows program menu at the Server where Sage 100 Server is installed. You should create your label report using a MAS_Reports 'System' DSN with the MAS_Reports login or a user defined login with at least a 'Public' Server Role. When a dynamic parameter is created, it establishes an ODBC connection to the table containing the parameter value. Static parameters do not use an additional connection. When the report is run from the custom reports menu the connection to the DB is with MAS_Reports. Modify your report using to utilize a single ODBC connection to all tables (report tables and parameter) using Crystal Reports Set Datasource location. Hope this helps. John

Isn't this is a solution only for SQL systems? Non-SQL systems would still need to modify the report to use another DSN and set that up of each workstion. No?

You mean SQL vs non SQL.? Yes this is only for SQL aka Sage 100 Premium

Sorry - I read Premium, but registered Advanced. The are a lot of advantages with SQL and reporting.

Now I've run across yet another twist involving dynamic parameters. A custom report with dynamic parameters prints without prompting for user id/password but does when it is previewed. Am I missing something?

Or as Dan failed to mention, use his Access shadow mirror product to run reports through or create PTQ's within Access and put that database on the server. Neither solutions would require setting up the special DSN on each workstation.

Doesn't that kind of defeat the purpose of using SQL?

@JohnShaver - Yes, it does. The purpose of my post was for @DanBurleson first post where he talked about setting up a DSN on every workstation in a non SQL environment.

Got it...that makes sense!

@JohnShaver Is your environment Sage 100 Premium? It prompts in both preview and print for both 100 Advanced and standard @ 4.50.

Yes, version 4.45 Premium.

I vaguely remember a bug with the parameters on v5.0 and the new crystal .net drivers. I wonder if premium used those .net drivers. Subsequently sage did release patches for 5.0. Just a thought