x Want to participate? Join the group to interact
  • State Suggested Answer
  • Replies 10 replies
  • Answers 2 answers
  • Subscribers 145 subscribers
  • Views 4162 views
  • Users 0 members are here
Browse Forums
Sage 100 Contractor Announcements
119
topics
57
replies
Sage 100 Contractor General Discussion
1k
topics
4k
replies
Sage 300 CRE Announcements
168
topics
526
replies
Sage 300 CRE General Discussion
3k
topics
9k
replies
Sage Construction Management Announcements
4
topics
0
replies
Sage Construction Management General Discussion
17
topics
21
replies
Sage Estimating Announcements
39
topics
3
replies
Sage Estimating General Discussion
189
topics
448
replies
Lindsay P

Excel Friendly Reports

SUGGESTED
Posted By

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.

Parents Reply Children
  • 0 in reply to Carsten
    SUGGESTED

    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!

*Community Hub is the new name for Sage City