Forum

topBanner
First Previous - Page 1 of 1 - Next Last

Sage Integrating Reports Add in Tab Excel , Drillable Reports , previous years comparatives and other useful tips

sarah
  • Companion Fellow PM.Dip
  • Practice Licence
  • 1137 posts
  • # 70194

Hi 

I take part in sage user groups in newcastle and I have had articles published by accounting Web and responded to many questions on the Sage User group, which is under the discussion groups.   I highly recommend it for sage users as there is loads of useful tips for  Sage users 

I thought some of them may be useful to students and any members who use sage.   I can only print the elements though that I wrote .   But I hope it helps anyone who is using sage.

There will be a number of them as topics and done by comments covering varous areas.  If anyone has any further questions .  I will try and help when I get a chance.   







Edited at 13 Apr 2011 08:03 PM GMT

  • Sarah Douglas C.FICB

    HouseTree Business Ltd 


  • Quote
sarah
  • Companion Fellow PM.Dip
  • Practice Licence
  • 1137 posts
  • # 70195

Based on a number of sage questions and the seminar in Glasgow last year re excel reporting. I also thought this may be helpful to some members using both excel and the sage for reporting  .    Some of the things I have suggested  need Excel 2007 onwards.  Sage Versions are 2010 and 2011 , and I will have a look to see if I can find them in a earlier Version.  Some of the things are available in sage earlier versions but it also depends what level of sage you have. 

I have done them by a Comment Re each topic so sorry if it appears I have loads of comments .  I did it that way so it was easier to follow. The Areas i have Covered are Sage Integrated Reports , Bank Rec's , Drillable Reports , Locking Month End (Discussion ).  Entering  Comparative Figures after Shutdown.   I hope some of this is useful.   If anyone needs further info just ask in the comment and I will try to help.  Kind Regards Sarah.  

Please note there has been a number of updates.   If you are missing this report .  When you upgraded. 



Edited at 13 Apr 2011 06:52 PM GMT

  • Sarah Douglas C.FICB

    HouseTree Business Ltd 


  • Quote
sarah
  • Companion Fellow PM.Dip
  • Practice Licence
  • 1137 posts
  • # 70196

Bank Reports

 

Problem: One  needs a report to list Bank reconciled entries in date order.  So she can through the report line by line.

to find out what went wrong .  

There is a report called a Retrospective Bank Reconciliation (Detailed ) or Retrospective Bank Reconciliation, and this can be found in version 10 onwards .

  1    The report also puts it on the Db and Cr side the same as it would appear on a bank statement.

  2    The report show the date of reconciliation and each transaction and each transaction matched line by line as it was    matched .

Another problem mentioned  that when exporting to excel  it does not format well.  Sage Intergrated Reporting is much better then using the export button .  It also has a number of reports but this is how you would add one .  I have checked it does export with your debit and Credit correct as appears in your books , it is very easy to switch columns if you need to . 

-- Kind Regards Sarah@ Douglas Accountancy & Bookkeeping Services, Glasgow

  • Sarah Douglas C.FICB

    HouseTree Business Ltd 


  • Quote
sarah
  • Companion Fellow PM.Dip
  • Practice Licence
  • 1137 posts
  • # 70197

How to Save a Report to Use in Excel Sage Integrated Reporting

Using the Excel Add in Tab you can print off any report from sage and it is exactly the same way you use a sage report except you are using excel.  There are 35 standard reports, and you can add or design any reports that you wish to use and it is very easy to work with, it is very different to the export button used when in the preview button.  

It is then very easy to do want you want to do within the sheet.  If you do find it difficult , sometimes  it can be easier if you copy the info on to another sheet, I use another sheet to layout the P&L the way I want by linking the info from sheet to sheet 

How to add a report to excel Sage Integrated Reporting .

1 ) Pick your report that you would like to use , Press edit report and make changes or leave as it is.

2) Save As to your Program Data folder, then to the following folders .  Sage  . Accounts . 2011 or 2010 or 2009 folder depends which version you are using.  COMPANY.OOO.  REPORTS. Excel  and then save name of report.

This report will now appear in the excel integrated reports option for you.  My next reply is how to set up a company in Sage Integrating reports and you can set up as many companies as you like. 

- Kind Regards Sarah@ Douglas Accountancy & Bookkeeping Services, Glasgow

  • Sarah Douglas C.FICB

    HouseTree Business Ltd 


  • Quote
sarah
  • Companion Fellow PM.Dip
  • Practice Licence
  • 1137 posts
  • # 70198

Set up Company in Sage integrated Reports and How to use it in the EXCEL ADD IN TAB

Firstly Close down your Sage 

1) Open Excel in 2007 or 2010 Version this will only work with one spreadsheet open at a time. The Add In Tab will not show if you have another spreadsheet open 

2) Go to the ADD IN TAB 

3) Pick Select Company and you will be asked for your Logon Name and Password  which is for your sage accounts .

4) In this menu at the bottom Press the Grey Area Look for another Company   then pick Program Data.    Sage . Accounts.  COMPANY.001 or COMPANY.002 extra . ACCDATA . SET.DTA   .

This will bring you to the Welcome to Sage Accounts Window and you will see your company .  Press OK

USING THE SAGE INTEGRATED REPORTS ONCE SET UP 

1) In the ADD IN Ribbon you will now see the company you have set up and you also follow the same procedure to set up as many companies as you like. 

2) Press the Drop down button in between a light grey box and the word insert.  All your reports you saved and the 35 other reports will be there.

3) Highlight the report you want and then press insert beside the drop down box were you picked your report .

4) You will now see that a window from sage comes up  just like as if you were picking it through sage .  

Your report will now appear in excel spreadsheet .   And you can now play with it as much as you like.

5) Save your sheet, and close.

Please note you can do as many reports as you like on various sheets .  For example you could have all the Various Reports your clients wants each month on one spreadsheet,  when you open your sheet your computer may a security Warning DATA CONNECTIONS HAVE BEEN DISABLED . If you just confirm you would like to Enable this content if you want to to.

The Great use of this is it will update data that has been entered since you ran this report. 

1) GO to ADD IN Tab and Press the Refresh Button , you will receive a message and if you want to refresh the date press NO and NO again .  And you will receive a message your report has been refreshed .

I use this for all my clients as they all have their own requests and it is really easy to use.

 

  • Sarah Douglas C.FICB

    HouseTree Business Ltd 


  • Quote
sarah
  • Companion Fellow PM.Dip
  • Practice Licence
  • 1137 posts
  • # 70199

To Change Prior year Comparative figures , when accountants providing closing down Journal after Shut down

To Change your comparatives after you have closed down and received further Journals from an accountant .

1) Go into Nominal Ledger , highlight Nominal Code , Details Tab , 

                          and then 

Go to the Column where you have each year end and you can highlight each month and change the figures to want you want them to be .  Use a - minus if you want it to be a credit Balance.   

When you print off any comparative or prior reports these will now be changed.

  • Sarah Douglas C.FICB

    HouseTree Business Ltd 


  • Quote
sarah
  • Companion Fellow PM.Dip
  • Practice Licence
  • 1137 posts
  • # 70200

Drillable Reports for for your P&L Accounts , Aged Debtors and Aged Creditors and Seeing Detailed info in Customer and Supplier

Reports 

There is a very good Drillable Report which I like to use, and it can be found in the FInancials Module Reports under 

Management Anaylysis Reports , Management Anaysis Reports - Standard Budgeting  and the Report is Called 

       Profit and Loss Monthly Breakdown  .

The rest of these reports in this section are also Drillable .  There is also an Aged Debtors Drillable and Aged Creditors Drillable Reports.

Info in Customers and Suppliers 

Within each Supplier or customer you drill to the Sales Invoice , you can also see which invoices and  credit notes are allocated to which Purchase Payments  or Lodgement or Bacs . And Also which Nominal Code it went to.  Only see outstanding Invoices , Purchase Payment and Sales Receipts, you even see Payments on account only.  

Entering Customer Receipts and Supplier Payments 

You can now in Version 11 put the invoices in date order , pull up only invoices in a date range and specific date. Only Invoices that are due.  I am not sure About V10 I think you could do this also.

If you want to look at last years information it is quite easy to restore a backup. 

  • Sarah Douglas C.FICB

    HouseTree Business Ltd 


  • Quote
sarah
  • Companion Fellow PM.Dip
  • Practice Licence
  • 1137 posts
  • # 70201

Lock Items after Month End

This is open to discussion , and I feel it should be a option ,

The reason they don, t is because Sage is extremely sensitive with dates for the Vat Returns and especially CIS returns.  Locking the figures would cause people to enter late invoices and adjustments with the wrong dates .

The incorrect dates could lead to invoices been chased to late and paying Suppliers earlier.

I also think that it could lead to misleading P&L for each month.  With my clients we always said them the previous P&L with the Currrent P&L for their Financial Meetings to take this into account.  Also if the figures where locked then it would be difficult to use the Comparative Reports

  • Sarah Douglas C.FICB

    HouseTree Business Ltd 


  • Quote
sarah
  • Companion Fellow PM.Dip
  • Practice Licence
  • 1137 posts
  • # 70202

Reports

Hi 

The reason I have  mention these reports is because some sage users  mention that Sage is not capable of doing certain things .  

I have just spoken to Sage and they have said  that the Profit and Loss Monthly breakdown Drillable report  and the Bank Retro report was not available according to Sage on any Version of Sage earlier then 2010 no matter what level.  

It also depends what level you have of Sage,  for example Sage Instant will not have most of these.

  • Sarah Douglas C.FICB

    HouseTree Business Ltd 


  • Quote
First Previous - Page 1 of 1 - Next Last
bottomBanner
loading