General Consultant Discussion

This was in the original email, but only the attachment got uploaded.

Session Overview:
A Session Overview is attached to give you an idea what we will cover.

Presentation Style:
This is designed to be lots of hands on. I’ll present a topic, then you do a lab.
You can download the training outline at the beginning of class, which you keep for reference.

 Prerequisites:

• Local install of Sage 100 with ABC Demo Company
• We will primarily use ABC demo data from your local Sage 100 installed on your laptop.
• A working SOTAMAS90 DSN ODBC connection

 

MS Excel 2013 or higher

• Excel 2016 has the complete Power Query / Power Pivot functionality installed already
• Excel 2013 download and install the Power Query function
  https://www.excelcampus.com/install-power-query/
• https://www.microsoft.com/en-us/download/details.aspx?id=39379&CorrelationId=e0c53701-154a-4232-86af-07e35abec5e3 

Excel 2013, you might need enable the Power PIvot Add-in
https://support.office.com/en-us/article/start-the-power-pivot-add-in-for-excel-a891a66d-36e3-43fc-81e8-fc4798f39ea8 
 
Interest Level for converting a Sage Report to data table

• I will schedule this exercise for the end of the session so you can choose if you want to participate or not.
• Jeff is right, you can often do a query of the Sage data itself. But…
• Transforming data from the report provides a snapshot of the data at that moment in time, like Freezing inventory counts in Physical Inventory.
• You can’t easily do a query of the AR Open table and ask what it looked like a week ago.
• This method enables you to do a repeatable process that accumulates (appending a cumulative table) timestamped data over time so you can the data values were at that time
• Some tables are not as easy to query directly. Try recreating the Inventory Valuation Report
• And with this method you could do a monthly process to show the inventory value at that point in time, similar to the Inventory Value By Period, but coming directly from the source Inventory Valuation Report document.
• In Audit situations, this provides an audit trail back to the source document
• And this exercise is the most comprehensive example in the class of using the Power Query functions to transform data

 Let me know what other questions you have.
Thanks,
Clark