General Inquiries

Financial and WIP Reporting in Sage 300

  • 1.  Financial and WIP Reporting in Sage 300

    Posted 02-14-2020 18:49
    I am working with Sage 300 for the first time, so pardon my lack of knowledge of the capabilities of the product as I have used Spectrum and Vista in the past.

    First the canned WIP report in Sage 300 looks fairly weak as I can't find one which has a date cutoff with all the expected columns for a WIP with YTD numbers. I loved the contract status reports in Spectrum and Vista had a decent WIP report, but Sage 300 I can't find a WIP report I like. I have spoken to Cabbage product looks solid, but I am curious what other Sage 300 users are doing to produce a financial statement worthy WIP report?

    Speaking of financial statements what are most Sage 300 users using for financial statements? I have used Spreadsheet Server, a little pricey, but I liked the ability to quickly pull in Excel easily customize for quick financials and creating forecast.

    I am fairly decent at Sql Server, but not Crystal Reports. What is the best tool for Sage 300 to pull quickly pull data in Excel to be able to do a variety of analytics?

    Thank you,

    Matt Upchurch

    ------------------------------
    Matt Upchurch Accounting
    Chief Financial Officer
    Frisco TX
    (469) 576-8156
    ------------------------------


  • 2.  RE: Financial and WIP Reporting in Sage 300

    Posted 02-15-2020 09:49
    Good morning Matt,

    I've worked on Sage 300 for years and unfortunately my go to is to download data from the inquiry screens  in sage and prepare the WIP in excel.  There is a Sage Connector product that states that it will import Sage data into excel, however, I have not found it easy to use.  Maybe due to my lack of time to learn the product.

    Would love to hear other perspectives as well.

    Sincerley,

    ------------------------------
    Terry Russell
    Controller
    TEEL Construction Inc
    Fairfax VA
    (703) 759-4754
    ------------------------------



  • 3.  RE: Financial and WIP Reporting in Sage 300

    Posted 02-15-2020 10:06

    Matt,

    We use Office Connector (Core Associates) a 3rd party reporting tool that uses Sage 300 data to create any Excel report you like.  We use it for our Job Cost Report/projecting tool, with multiple workbook tabs for drill down capability.  We also use it for our WIP.  You are correct, the Sage canned WIP does not allow for ME cutoff.  We've found this to be a great tool, and once you learn it, it's not difficult.

     

    Also, we still use the Financial Statement designer.  I've modified the reports to give me the various analyses – like Month over Month comparison, this year to Last year, and budget comparison.  I've not switched over to Office Connector for financial reporting yet, but that is certainly an option.

     

    Hope this helps!  Also, if you attend the TUG conference in May in New Orleans, they have a whole tract dedicated to Office Connector.

     

    Denita Schreier

     

          

     

    Chief Financial Officer

    100 Tower Drive | Beaver Dam, WI 53916

    P: (920) 356-1255 | C: (717) 413-6446

     






  • 4.  RE: Financial and WIP Reporting in Sage 300

    Posted 02-17-2020 15:00

    I incorrectly identified the 3rd party associated with Office Connector.  It's actually Event One software.  And I highly recommend it.  Easy and flexible.

     

    Denita Schreier

     

          

     

    Chief Financial Officer

    100 Tower Drive | Beaver Dam, WI 53916

    P: (920) 356-1255 | C: (717) 413-6446

     






  • 5.  RE: Financial and WIP Reporting in Sage 300

    Posted 02-15-2020 12:20
    Edited by Joe Parton 02-15-2020 12:34
    Thank you for the kind mention Matt! I'm happy to discuss our solution with anyone who has the same needs! You'll find our solution is custom-tailored to solve these exact problems.



    ------------------------------
    Joe Parton
    Owner
    Cabbage Software
    https://cabbage4sage.com/
    Cheshire CT
    (203) 217-0075
    ------------------------------



  • 6.  RE: Financial and WIP Reporting in Sage 300

    Posted 02-15-2020 16:23
    Matt,

    Since you have SQL knowledge, you might want to look at using Power Query built into Excel to access data from Timberline. It's my understanding that you need to have 32-bit Excel installed to access Sage 300 data due to the outdated database/ODBC drivers. Using Power Query you can pull the data directly into excel, without any need for additional licensing and you can find many resources online to learn Power Query, for a basic start look at this Sage KB Article. Power Query allows you to manipulate the data using calculated columns, grouping data, etc. I've used both Office Connector & Power Query to pull data from Sage 300. Both work, but I prefer Power Query as I use it for many different software packages/databases. Learning Power Query also gives you a heads start to learning Power BI, which you should really look into long term.

    I have heard complaints from a Sage 300 user that pulling data using Office Connector (and likely Power Query) locks up the tables and causes slowness/errors in processing transactions. I haven't verified this, but you might watch for it. The ODBC connection to Pervasive tends to be much slower than pulling data from SQL Server. You might want to consider using SQL Replicator if you can. If your Sage 300 is installed on your domain controller, you can't use SQL Replicator.

    Best,

    ------------------------------
    Kevin Halme CCIFP
    Constructive Tech Solutions
    Omaha, NE
    www.consttech.solutions
    (402) 915-5050
    ------------------------------



  • 7.  RE: Financial and WIP Reporting in Sage 300

    Posted 02-15-2020 17:56
    All,

    Since we are exploring all ways to get Financial data from Sage 300 CRE - I would suggest looking at Anterra Data Center that is very reasonable.  Go to https://anterratech.com/financialbi - they offer much more.  They are really a great option. I would definitely take a look - you will not be disappointed.

    ------------------------------
    William Kormoski
    President, Sage 300 CRE Consultant
    Kormoski & Associates LLC
    Mount Juliet TN
    (615) 351-4953
    ------------------------------



  • 8.  RE: Financial and WIP Reporting in Sage 300

    Posted 02-17-2020 09:47
    We have a custom WIP report we set up and run it as a text file so we can convert to excel. It has date cutoff.

    Another note, I've just started playing with MS Power BI and Sage. I can see potential in it but realize there's quite a bit to learn about the tool to make it useful. Have any of you looked into it or used it? Would love some pointers.


    ------------------------------
    Emily Ellis
    C. T. Wilson Construction Company, Inc.
    ------------------------------



  • 9.  RE: Financial and WIP Reporting in Sage 300

    Posted 02-17-2020 11:36
    Emily,

    I worked with MS Power Bi , great tool, but is you are using Sage 300 CRE Anterra is way better.

    ------------------------------
    William Kormoski
    President, Sage 300 CRE Consultant
    Kormoski & Associates LLC
    Mount Juliet TN
    (615) 351-4953
    ------------------------------



  • 10.  RE: Financial and WIP Reporting in Sage 300

    Posted 02-17-2020 13:19
    Edited by Kevin Halme 02-17-2020 21:44
    Emily,

    I think that majority of the learning required to utilize Power BI is learning the database. If you know the database structure, there are multiple ways to access the data. I haven't used Anterra so I don't know how it compares to Power BI. What I like about Power BI is that many companies already have a license through Office 365, Power BI can pull data from many different sources for a dashboard, and it has natural language query capabilities.

    Writing the queries to use natural language queries takes some effort, but can be useful. An example of Natural Language Queries, is as follows: if you need the amount spent on power by year rather than just what was in your utilities GL Account which includes phone, water, etc, you could ask Power BI in a Q&A box "show sum amount by year where description contains power in table" and Power BI will run the query on the dataset and summarize the power transactions assuming you use power in the description or we could have used a specific vendor. If the time is spent to build a good dataset, the natural language queries will be extremely beneficial in producing one-off reports that normally would involve running a canned report and adding additional filters to get the data you need.

    Since Power BI can pull data from so many sources (not just Sage), it's a good skillset to obtain. You might want to start by learning the basics of Power Query and the Sage 300 database then learning Power BI. Here's a link to a Power BI course that has been recommended on this forum in the past.

    Best,

    ------------------------------
    Kevin Halme CCIFP
    Constructive Tech Solutions
    Omaha, NE
    www.consttech.solutions
    (402) 915-5050
    ------------------------------



  • 11.  RE: Financial and WIP Reporting in Sage 300

    Posted 02-17-2020 13:54
    All,

    Anterra is not limited to Sage 300 CRE it can pull data from any source. Sage, Procore, Viewpoint, Prolog, Yardi....
    The big advantage is many of the KPI's and dashboards you would build are already built.

    If you have any questions feel free to reach out bill@kormoski.com or 615-351-4953.

    ------------------------------
    William Kormoski
    President, Sage 300 CRE Consultant
    Kormoski & Associates LLC
    Mount Juliet TN
    (615) 351-4953
    ------------------------------



  • 12.  RE: Financial and WIP Reporting in Sage 300

    Posted 02-17-2020 14:41
    My firm has been a Sage 300 user since 1998, I've been using it here since 2001. We have a custom WIP as of a specific date report and also built that report as a CSV file. However, for years I've used ODBC to extrapulate the data to excel. I like ODBC because I don't have to reformat the report every month. I use excel because of formatting, sorting the data as well as it is easy to keep prior year revenue, cost, profit and calculate current year revenue, cost and profit. I use the custom report to validate the revised contract, estimated costs and JTD costs, but I haven't found a report or a way to calculate prior year and current year information out of Sage. ODBC works great, have been using it with out fail every month for over 18 years.

    ------------------------------
    David Miller CCIFP, CRIS, CTP
    Cfo / Treasurer
    Jokake Construction Services, Inc.
    Phoenix AZ
    (602) 224-4573
    ------------------------------



  • 13.  RE: Financial and WIP Reporting in Sage 300

    Posted 02-18-2020 07:52
    We are Sage 300  & use the Office Connector product mentioned in this string - for WIP & Financials.

    For WIP - we use it to pull monthly cost & billings from the Sage database and then the WIP schedule itself was built with excel functionality.

    For financials - we use OC to pull monthly balances and then excel functionality to create the financial reports.

    James Medders
    Harper General Contrctors
    864 527-2530

    ------------------------------
    James Medders
    Vice President - Finance & Administration
    Harper Corporation
    Greenville SC
    (864) 527-2530
    ------------------------------



  • 14.  RE: Financial and WIP Reporting in Sage 300

    Posted 02-18-2020 10:43
    Office connector (Event1) is another get source for WIP and Financials.  Only down side is the data is not accessible via browser.  Fantastic product as well. Great group and training options. (One or one and video)

    ------------------------------
    William Kormoski
    President, Sage 300 CRE Consultant
    Kormoski & Associates LLC
    Mount Juliet TN
    (615) 351-4953
    ------------------------------



  • 15.  RE: Financial and WIP Reporting in Sage 300

    Posted 02-18-2020 16:23
    Thank you everyone who responded to my questions regarding reporting in Sage. Your responses were very useful in helping point me in a direction to pursue a solid solution to my challenges.

    Matt Upchurch

    ------------------------------
    Matt Upchurch Accounting
    Chief Financial Officer
    Venus TX
    (469) 576-8156
    ------------------------------



  • 16.  RE: Financial and WIP Reporting in Sage 300

    Posted 02-18-2020 17:21
    Matt,
    I replied to your thread privately as I think that the CFMA forum works best with member to member interaction versus associate members posting promotional messages about their respective technologies.  Given that there is already content about other technology on this thread I decided to add information regarding the Sage Office Connector Product Suite.

    I am familiar with Spreadsheet Server, originally by Global Software and now by InsightSoftware, which you indicate you are familiar with from use with Viewpoint (Vista or Dexter Chaney).  We have technology for Vista and Spectrum as well - Liberty Reports.   The Standard version is comparable to what you would get as part of Vista and our Designer license would be similar to their Platinum version.

    Our company, Event 1 Software, created the Office Connector product suite, which is a bi-directional Excel-based technology specifically designed to be used with Sage 300 CRE.   It is sold by Sage (via Business Partners and your Sage Customer Account Manager).  it has been OEM'd by Sage (sold as part of Sage 300 CRE offering) since 2011.      There is a limited functionality solution called Office Connector Starter that every Sage 300 CRE customer gets as part of their Sage Care.   These workbooks can refresh however you can't customize them.   Additionally there are three Office Connector products families:  Query, Write and Import.   Query and Write have Standard and Designer concurrent use licenses.   Query has a site license extension called Office Connector Financials.    The Import product is sold via import feature (each as a site license).

    People have used this technology to create whatever financial or operational report they desire.  As you already know from comparable technology, once you lay information out the way you wish, when you refresh the information is updated, preserving the desired format.   There has been a simple Work in Progress report on the Launch Pad for years.   An enhanced Work In Progress with cutoff was added last year.   Office Connector can see any Sage 300 CRE table that Sage's ODBC driver exposes so adding current or prior year financial information to a report is accessible if desired.  You would not have to push static data into Excel and then add it to a WIP report you manually assembled and repeat this process any time the data in Sage changed... you would simply create it the way you desired once and then refresh the report with a click of a button.

    Office Connector Financials would allow you to run financials for up to 6 future periods (so if you haven't closed December you can run financials for January, February, etc.) and for up to 10 years in the past (depending on how long you've used Sage 300 CRE and when a company began using Sage version 9.3 when Sage added the Account History Activity table).    There are a number of financial reports showcasing this functionality on the Financial tab of the Launch Pad or you can create your own financials to suite your needs.

    If you are interested in learning more, contact your Sage Business Partner or Sage Customer Account Manager.  Sage can easily demonstrate this capability to you.   It has been a Sage product since 2011 so any Sage consultant has had the fully functional software since then and I would think they could demonstrate it to you.  Sage's Sales Engineers could certainly provide you fully functional software to evaluate and satisfy yourself.

    ------------------------------
    James Coyle
    VP of Business Development
    Event 1 Software
    Vancouver WA
    (360) 356-1937
    ------------------------------



  • 17.  RE: Financial and WIP Reporting in Sage 300

    Posted 02-18-2020 20:02

    Your challenges are certainly not unique (I used to experience them myself)! It's really neat to see all of the different ways people tackle this issue and the different needs that are out there to fill. I've had a few ideas for some new articles for our blog just based on some of the conversation here. Thanks for posting.

    And thanks again for the kind mention, best of luck on your search!



    ------------------------------
    Joe Parton
    Owner
    Cabbage Software
    https://Cabbage4Sage.com/
    Cheshire CT
    (203) 217-0075
    ------------------------------



  • 18.  RE: Financial and WIP Reporting in Sage 300

    Posted 03-02-2020 12:26
    One last note - for Event1 Office Connector you can go to https://www.youtube.com/channel/UCAtFmfsBCORpJ8uQWuCIejA
    and see many videos of there products for WIP & GL.

    ------------------------------
    William Kormoski
    President, Sage 300 CRE Consultant
    Kormoski & Associates LLC
    Mount Juliet TN
    (615) 351-4953
    ------------------------------