x Want to participate? Join the group to interact
  • State Suggested Answer
  • Replies 2 replies
  • Answers 1 answer
  • Subscribers 15 subscribers
  • Views 998 views
  • Users 0 members are here
Browse Forums
Announcements
105
topics
4
replies
General Discussion
1k
topics
2k
replies
Steve81uk

Possibly a question asked before, Excel365- Sage 200C pro Integration,

SUGGESTED
Posted By

I have tried looking through the forums, and after a long time of trying to do this all by myself, ( with no Sage training at all) I am now swallowing my pride and asking for help.

Basically I have been tasked with reporting and bringing forward my companies information from manual input, (where they copy and paste everything from sage into excel), to something a bit more automated. 

Weeks/ Months of looking into the Sage 200c Pro backend of tables within all the companies, Via Excel ODBC, SQL, Power Query and Power pivot, I just seem to be a little lost with actually getting a Trial balance to match the output you would from a print out on the Nominal Account Report. (Under 4000 to be running total. over 4000 to be YTD). This has been bugging me as am sure that this data should be held somewhere within the database without having to do any calculations surely?

I guess really I am looking for the most simplistic way of doing it with as little calculations as possible. The reason being as there are so many companies that I need to TB,  and then consolidate, that the majority of my attempts have turned into very large Excel files that will crash on most attempts, due to the massive amount of calculations being processed. I even tried to start doing this into Power BI, via SQL. that almost worked but then once I started getting nearer the final end result, the calculations started to get slow again.  What possibly also doesn't help is that during my (8 months) process of trying to do this in a multitude and various ways, I have found the Sage provider we have have been a little behind with us and the current sage is behind on the times. (3 Versions behind in fact) So am pushing for a new provider than can update our systems and hopefully help out.

But while I wait as this seems to be a lengthy process in itself. I am just trying, hoping, and really wanting to get some sort of output on this for my employers to show that I am not just sitting around doing nothing. 

Quite literally my last Shout at sanity and now trying to seek help from anywhere I can, I feel someone, somewhere must be able to help me.

I thank anyone and everyone who reads this and gives any advice they can, in advance, and hopefully, I can start to get some decent work completed soon. Just so that I can hopefully start another task soon.

Thank you for reading, and I hope that I can get some sort of help/ response soon.

Steve

  • 0
    SUGGESTED

    Not sure how easy this will be as if you look at the TB in the Report Designer in the program, its quite a complex report.  Excel Reporting has the option to do Asset and Liability report and Revenue and Cost reports (Balance sheets and Profit and Loss) but no Trial balance. Now behind the scenes, the TB roughly works by using the NLAccountYearValue for the Year to Date/Year End value (depending on the year) per Nominal code, and the NLAccountPeriodValue for each period per nominal code, so there will potentially be an amount of calculation involved and maybe pivoting on the raw data. Your Partner should be able to help - some partners would be able to do it for you but it would usually be a chargable thing.

  • 0 in reply to Gary Butler_1

    I actually thought that I had replied to you. Thank you for the input, it has certainly made me feel that I am on the right path. I have and had been looking within these Tables before and either got close to the figure that comes from the sage reports, some by about 2 to 3 pence, which is frustratingly annoying as I obviously need to get it exact. I suppose I knew that there would still be calculation involved, and will take a look at a possible route for using both Yearly and Period data combined, as have really only been using the period data. Will hopefully have something I can use soon. 

    Thank you again

*Community Hub is the new name for Sage City