Directly write reports in Excel
Connect reports directly to the Sage 100 Contractor database
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 reports directly to the Sage 100 Contractor database. Here is a guide to directly write reports in Excel. 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
1. Open Excel and Check Compatibility
First, and most importantly, you need to be in Excel, on a version that can “see” your SQL Server Instance.
2. Connect to SQL Server
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). Sage 100 Contractor or the database administrator displays this information when you log in.
3. Choose Authentication Method
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.
4. Set Login Scope
Please note that some versions of Excel will ask you where to apply the login credentials.” 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.
5. Select Tables
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.
6. Find the Right Tables and Fields
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.
7. Use Excel Functions to Build Reports
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.
If you liked this Sage 100 Contractor tip, check out “How to capture outside data in Sage 100 Contractor”. Or, search SageCity for more help.
You can always get help with customized reports from our Sage Certified Consultants at ETHOSystems. Shoot us an email here.
By Kelcie Jensen Flores