Connect reports directly to the Sage 100 Contractor database
By Kelcie Jensen Flores
Do you need the ability to write reports in Excel? Here’s how!
Sage 100 Contractor has a robust report designer, but some reports are either too cumbersome or take too long to export from Sage directly to Excel. For that reason, sometimes it is beneficial to connect your report directly to the Sage 100 Contractor database. Here is a guide to make that connection. Please note, that this looks different depending on which version of Excel you are running, but the fundamentals are the same.
Excel needs to “see” SQL
First, and most importantly, you need to be in Excel, on a version that can “see” your SQL Server Instance.
Then click Data -> From Other Sources ->SQL Server
From there, Microsoft will ask for your SQL instance info. Enter your SQL Server Name (typically your computer name/server name and then “\Sage100Con,” although this can be different for everyone). This information is displayed when you log into Sage 100 Contractor or the database administrator.
Windows Authentication tends to work best for these types of connections, but Excel allows for SQL Server Authentication as well.
In this example, I need to identify my login credentials first, then I will select the company. In others, you would enter the company name and then select your credentials.
Please note, that some versions of Excel will ask where the login credentials are “applied.” This is just asking if your login gets you into the database or into the company. Typically, you need to select that security is applied at the company level.
Once the company is selected, the table(s) can be selected to come into your report. Click Next to define the connection or Finish to load the data table. The Vendor table (actpay) was selected for this example.
The biggest issue then becomes, how do you know what tables and fields contain the information you want from reporting? Sage provides a data dictionary that lists all the tables and table names and field names along with the connection points for related tables. To find this, print the 13-7 Print Database Format.
This should allow you to run just about anything you need. Once you pull in the table(s) you need, you can search for Excel functions to perform various functions. The most common ones used in reports are vLookup, SumIfs, and IfError.