Excel Friendly Reports

Question

Anyone have any luck modifying reports to be more Excel friendly? I'm used to pulling G/L details and Job Cost details into Excel to put them into PivotTables or Filter by specific jobs and/or codes, but the System reports have too many page breaks. It's very time consuming to manually delete all of the blank lines in order to work with a PivotTable or Filters. I'm considering taking a Report Writing training class on Sage University, but not sure if it will address my issue in order to help me.

Kelcie Jensen · Answer

You may also connect directly to Excel using the standard Excel database queries built into Excel. The other functions mentioned are easier to use, but if you know what you are looking for, Excel can do everything you need. Especially now that Sage 100 Contractor is on version 20.

Kelcie Jensen · Answer

Yes. Ultimately you need to be on 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. This looks different depending on which version of Excel you are running, but the fundamentals are the same. Enter your SQL Server Name (typically your computer name/server name and then a "\Sage100Con", though this can be different for everyone). This information is displayed when you log into Sage 100 Contractor or the database administrator.

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. 
 
 Once I've picked the company and credentials, now I have access to pull Sage data tables into Excel. 
 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 needed. Once you pull in the table(s) you need, you can search for Excel functions to perform various functions. The most common ones I use are vLookup , SumIfs , IfError 
 Hope that helps!

Sage Construction & Real Estate

Excel Friendly Reports

COMMUNITY

COMPANY

PARTNERS

SUPPORT & TRAINING