Hi Bex,

Thanks for using Community Hub.

The following may be useful:

(32 bit) Link your data in Microsoft Excel using ODBC >

(64 bit) Link your data in Microsoft Excel using ODBC >

... and for help with the Excel parameters, this may be useful too:  Excel Help and Learning >

Regards,

Andy
Sage UKI

We offer report export with formulas into Excel within our software ProudNumbers Management Accounts for Sage 50. We can also design a bespoke report. 

Kind regards,

Pavol Rovensky 

The report designer sucks raw data from Sage and then processes it (summing, etc).  If you "report to excel" you get the summed version (often with merged cells); alternatively if you "export to excel" you get the raw data it sucked out of Sage without any processing.

You say drop it in: do you mean the raw data or the report?

As a charity we need to have the value of each fund within certain nominals.  As a result I found it easier to create a TB from scratch using the transaction data - this I call an "Exploded TB" as each nominal has been exploded into the funds that make it up.  Originally I hacked the TRANTB report to provide the source data for the exploded TB, but later changed it to use ODBC: it sucks the transaction data into a table which is then summed into another table by nominal by fund.

For reasons of speed, the created exploded TB is value copy-pasted into the workbook that creates our accounts - to avoid the summing being redone every time the workbook (it has many formulae), or any other workbook within the instance of Excel, is recalculated - along with the date-time the data was refreshed.  To make life easier, I have a macro I wrote behind a button which asks for the year end (current year end given as default, and updates the cell with returned value), does the refresh and updates the date-time the refresh was done.

When using legacy ODBC, for the parameters, they are created by a simple "?" within the query; the parameters apparently being filled in order that they are found.  You edit the parameters from the 'Data>Properties>Connection  Properties (box at end of connection name)>{Definition tab}>[Parameters...]' button (of the table for the sucked data) and can set the source of the parameters as either prompted, pre-set value or value of a cell.

I have a "data" sheet within my exploded TB workbook which contains the necessary parameters, and the parameters are set to pick up the values from cells in that sheet.  (How to rename them is still a mystery to me - I've only managed it by using the [Edit Query...] button, but [my] Microsoft Query Editor seems to have a tendency to bork if the query is not simple.)

If it's of any use the Query I use is:

SELECT nominal_ledger.account_ref AS 'N/C', nominal_ledger.name AS 'Name', audit_journal.amount AS 'Amount', audit_journal.fund_id AS 'Fund', audit_journal.date as 'Date', audit_journal.deleted_flag as 'Del'
FROM nominal_ledger nominal_ledger, audit_journal audit_journal
WHERE nominal_ledger.account_ref = Audit_journal.nominal_code(+) and (audit_journal.DATE>= ? And audit_journal.DATE<=?) and audit_journal.deleted_flag=0

Technically, the selection of the deleted_flag is unnecessary (as the deleted transactions are filtered out); similarly the start date (DATE>=?) criteria as the data needs to be from the first possible transaction (I have that date parameter set to be the date we started to use Sage - probably left over from converting the hacked TRANTB report to ODBC).  The Date selection isn't needed, but it does provide a way to look at the data and check if something doesn't seem quite right.

I think a lot will have improved in terms of raw speed with the upgrade to 64-bit.

I am a big fan of EIR (Excel Integrated Reporting). You can use the existing reports and create new ones by saving them to the reports\excel folder on your PC

Excel Integrated Reporting (EIR) (sage.com)

Now that this can be used with 64 bit office, I think Sage should promote this facility harder. I believe it is the easiest way to get a data table in Excel from Sage

Interesting, I tried to install this and it said that it's no longer supported. I have a session with Sage tomorrow so I shall ask! Thank you for your suggestion! I am loosing the will with all the spreadsheets I have. 

That link is for the legacy version of EIR. The latest, and supported, version of EIR is installed when you install Accounts. If you are using 64 bit Excel then you'll need to be on V30 Accounts as that is the first version that provides 64 bit support.

Hoop that helps

Thank you! I don't know if I'm trying to download from the wrong link possibly. But I like your suggestion of the exploded TB