Sage 100

Hello,
 Converting a large customer from v2019 Advanced to Premium. They have over 100 custom reports on the menu. They all failed when using the report conversion wizard. The standard reports converted fine, but nothing in the MAS90\Reports folder did. So... do I have to manually map every single table in every single report?!
 This seems insane.

And, I remember having to map SY0_CompanyParameters to the SQL MAS_System table before (or so I thought), but can't seem to figure out which one now. I need to be able to put the company code & company name on the report since many of them are shared by about 12 different company codes. SY_Company does not work. 

Thanks!!! ~Dana

Btw, sorry if this is a duplicate thread but the search feature here sucks & I can't find an answer in the first few pages of thousands of hits.

------------------------------
Dana Young
Lehman Wesley & Associates
Lansing MI
------------------------------

I don't know if this is the BEST solution but when I've upgraded non-SQL to SQL I created an ODBC ( ie SAGEDATA ) to the SQL database and added it to the desktop and had people use that ODBC ( read-only ) to connect to the SQL data.

Be advised if your users are connecting to multiple companies and are used to SOTAMAS90 prompting for the company code during refresh - you'll have to devise a way to easily switch between company codes.

The ODBC to SQL lets you specify a default database but you can't have it prompt for the company code like SOTAMAS90 does without some behind-the-scenes work.

The reports I've converted were pretty quick to make the ODBC swap since the structure doesn't change -  though you can't run them through the Sage tool you will have to go into each one separately using Crystal. Here's a short YouTube outlining the steps - https://www.youtube.com/watch?v=9pJcAhk_ZhQ

I think if you add the reports to the CUSTOM menu then users would be prompted for the company code.

------------------------------
Wayne Schulz - Schulz Consulting - 860-516-8990
------------------------------

We create MAS_### DSN's with default database.  This makes changing company codes not easy for existing external queries, but new queries are very user friendly.
Reports run from Sage should auto-select the correct company code (not prompt).
I do not remember having to go report by report for conversion to SQL.  MASCRCW should work for *CUSTOM.RPT reports (unless they are being converted from pre-framework table structures).
Look at the way Sage brings CompanyName and CompanyCode into forms (blank formula fields...).  You can do that in other reports too, without a table link for the data.
One of the common issues with converting to Premium is Date fields to DateTime.  Why Sage chose to use DateTime in SQL is a complete mystery to me.

------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.
Toronto ON
------------------------------

Original Message:
Sent: 07-21-2020 10:47
From: Wayne Schulz
Subject: Custom Reports conversion Advanced to Premium

I don't know if this is the BEST solution but when I've upgraded non-SQL to SQL I created an ODBC ( ie SAGEDATA ) to the SQL database and added it to the desktop and had people use that ODBC ( read-only ) to connect to the SQL data.

Be advised if your users are connecting to multiple companies and are used to SOTAMAS90 prompting for the company code during refresh - you'll have to devise a way to easily switch between company codes.

The ODBC to SQL lets you specify a default database but you can't have it prompt for the company code like SOTAMAS90 does without some behind-the-scenes work.

The reports I've converted were pretty quick to make the ODBC swap since the structure doesn't change -  though you can't run them through the Sage tool you will have to go into each one separately using Crystal. Here's a short YouTube outlining the steps - https://www.youtube.com/watch?v=9pJcAhk_ZhQ

I think if you add the reports to the CUSTOM menu then users would be prompted for the company code.

------------------------------
Wayne Schulz - Schulz Consulting - 860-516-8990
------------------------------

I ran the MASCRCW program & all the custom reports failed, said it couldn't find the tables

------------------------------
Dana Young
Lehman Wesley & Associates
Lansing MI
------------------------------

Original Message:
Sent: 07-21-2020 11:04
From: Kevin Moyes
Subject: Custom Reports conversion Advanced to Premium

We create MAS_### DSN's with default database.  This makes changing company codes not easy for existing external queries, but new queries are very user friendly.
Reports run from Sage should auto-select the correct company code (not prompt).
I do not remember having to go report by report for conversion to SQL.  MASCRCW should work for *CUSTOM.RPT reports (unless they are being converted from pre-framework table structures).
Look at the way Sage brings CompanyName and CompanyCode into forms (blank formula fields...).  You can do that in other reports too, without a table link for the data.
One of the common issues with converting to Premium is Date fields to DateTime.  Why Sage chose to use DateTime in SQL is a complete mystery to me.

------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.
Toronto ON
------------------------------


Original Message:
Sent: 07-21-2020 10:47
From: Wayne Schulz
Subject: Custom Reports conversion Advanced to Premium

I don't know if this is the BEST solution but when I've upgraded non-SQL to SQL I created an ODBC ( ie SAGEDATA ) to the SQL database and added it to the desktop and had people use that ODBC ( read-only ) to connect to the SQL data.

Be advised if your users are connecting to multiple companies and are used to SOTAMAS90 prompting for the company code during refresh - you'll have to devise a way to easily switch between company codes.

The ODBC to SQL lets you specify a default database but you can't have it prompt for the company code like SOTAMAS90 does without some behind-the-scenes work.

The reports I've converted were pretty quick to make the ODBC swap since the structure doesn't change -  though you can't run them through the Sage tool you will have to go into each one separately using Crystal. Here's a short YouTube outlining the steps - https://www.youtube.com/watch?v=9pJcAhk_ZhQ

I think if you add the reports to the CUSTOM menu then users would be prompted for the company code.

------------------------------
Wayne Schulz - Schulz Consulting - 860-516-8990

Check the table names within the reports (they should be new-framework only), and that you used the SQL option when running MASCRCW... if you used the wrong connection type, or an account without access to the SQL database, it could fail.


------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.
Toronto ON
------------------------------

Original Message:
Sent: 07-21-2020 11:18
From: Dana Young
Subject: Custom Reports conversion Advanced to Premium

I ran the MASCRCW program & all the custom reports failed, said it couldn't find the tables

------------------------------
Dana Young
Lehman Wesley & Associates
Lansing MI
------------------------------


Original Message:
Sent: 07-21-2020 11:04
From: Kevin Moyes
Subject: Custom Reports conversion Advanced to Premium

We create MAS_### DSN's with default database.  This makes changing company codes not easy for existing external queries, but new queries are very user friendly.
Reports run from Sage should auto-select the correct company code (not prompt).
I do not remember having to go report by report for conversion to SQL.  MASCRCW should work for *CUSTOM.RPT reports (unless they are being converted from pre-framework table structures).
Look at the way Sage brings CompanyName and CompanyCode into forms (blank formula fields...).  You can do that in other reports too, without a table link for the data.
One of the common issues with converting to Premium is Date fields to DateTime.  Why Sage chose to use DateTime in SQL is a complete mystery to me.

------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.
Toronto ON


Original Message:
Sent: 07-21-2020 10:47
From: Wayne Schulz
Subject: Custom Reports conversion Advanced to Premium

I don't know if this is the BEST solution but when I've upgraded non-SQL to SQL I created an ODBC ( ie SAGEDATA ) to the SQL database and added it to the desktop and had people use that ODBC ( read-only ) to connect to the SQL data.

Be advised if your users are connecting to multiple companies and are used to SOTAMAS90 prompting for the company code during refresh - you'll have to devise a way to easily switch between company codes.

The ODBC to SQL lets you specify a default database but you can't have it prompt for the company code like SOTAMAS90 does without some behind-the-scenes work.

The reports I've converted were pretty quick to make the ODBC swap since the structure doesn't change -  though you can't run them through the Sage tool you will have to go into each one separately using Crystal. Here's a short YouTube outlining the steps - https://www.youtube.com/watch?v=9pJcAhk_ZhQ

I think if you add the reports to the CUSTOM menu then users would be prompted for the company code.

------------------------------
Wayne Schulz - Schulz Consulting - 860-516-8990

Thank you!
I did a test in one report & re-mapped the tables manually to the MAS_XXX database in SQL using the sa password, then opened the report from the Sage menu & it worked for another company code. Its like it knew to switch it & look a that particular MAS_company code. So, I think that will work; I was just concerned having to do the manual mapping on 100+ reports & if there wasn't a better way.

And, still trying to figure out where to grab the current Company Code & Company Name from.

------------------------------
Dana Young
Lehman Wesley & Associates
Lansing MI
------------------------------

Original Message:
Sent: 07-21-2020 10:47
From: Wayne Schulz
Subject: Custom Reports conversion Advanced to Premium

I don't know if this is the BEST solution but when I've upgraded non-SQL to SQL I created an ODBC ( ie SAGEDATA ) to the SQL database and added it to the desktop and had people use that ODBC ( read-only ) to connect to the SQL data.

Be advised if your users are connecting to multiple companies and are used to SOTAMAS90 prompting for the company code during refresh - you'll have to devise a way to easily switch between company codes.

The ODBC to SQL lets you specify a default database but you can't have it prompt for the company code like SOTAMAS90 does without some behind-the-scenes work.

The reports I've converted were pretty quick to make the ODBC swap since the structure doesn't change -  though you can't run them through the Sage tool you will have to go into each one separately using Crystal. Here's a short YouTube outlining the steps - https://www.youtube.com/watch?v=9pJcAhk_ZhQ

I think if you add the reports to the CUSTOM menu then users would be prompted for the company code.

------------------------------
Wayne Schulz - Schulz Consulting - 860-516-8990
------------------------------

Create blank formula fields with the correct name, and Sage should pass values in automatically.


------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.
Toronto ON
------------------------------

Original Message:
Sent: 07-21-2020 11:14
From: Dana Young
Subject: Custom Reports conversion Advanced to Premium

Thank you!
I did a test in one report & re-mapped the tables manually to the MAS_XXX database in SQL using the sa password, then opened the report from the Sage menu & it worked for another company code. Its like it knew to switch it & look a that particular MAS_company code. So, I think that will work; I was just concerned having to do the manual mapping on 100+ reports & if there wasn't a better way.

And, still trying to figure out where to grab the current Company Code & Company Name from.

------------------------------
Dana Young
Lehman Wesley & Associates
Lansing MI
------------------------------


Original Message:
Sent: 07-21-2020 10:47
From: Wayne Schulz
Subject: Custom Reports conversion Advanced to Premium

I don't know if this is the BEST solution but when I've upgraded non-SQL to SQL I created an ODBC ( ie SAGEDATA ) to the SQL database and added it to the desktop and had people use that ODBC ( read-only ) to connect to the SQL data.

Be advised if your users are connecting to multiple companies and are used to SOTAMAS90 prompting for the company code during refresh - you'll have to devise a way to easily switch between company codes.

The ODBC to SQL lets you specify a default database but you can't have it prompt for the company code like SOTAMAS90 does without some behind-the-scenes work.

The reports I've converted were pretty quick to make the ODBC swap since the structure doesn't change -  though you can't run them through the Sage tool you will have to go into each one separately using Crystal. Here's a short YouTube outlining the steps - https://www.youtube.com/watch?v=9pJcAhk_ZhQ

I think if you add the reports to the CUSTOM menu then users would be prompted for the company code.

------------------------------
Wayne Schulz - Schulz Consulting - 860-516-8990