Using ODBC sage 50 project activity report

[cpetty]

Hi, I'm not very good in Sage Report Designer, so not sure which columns to look for.

I want to use the ODBC link to run the project activity report in excel so I can manipulate it quicker (the report is so big it takes forever to run in Sage and export to excel)

Can you please help me chose my query columns to create this report? and so I can filter by date in the new pivot table

Thanks

[brucedenney]

I am not sure, you select the field you want, I don't know what field you want.

If you are trying to find the names of fields, then there is a tool here to help you do that.

https://my.sage.co.uk/public/ask/images ... 8.html?v=2

[Onion RS]

Hi,

Having done this for a pivottable report recently, I think the columns you'll need are:

PROJECT.CUSTOMER_REF, SALES_LEDGER.NAME, PROJECT.REFERENCE, PROJECT.NAME, PROJECT_TRAN.PROJECT_TRAN_ID, PROJECT_TRAN.AUDIT_TRAIL_ID, STOCK_TRAN.TYPE, PROJECT_ONLY_TRAN.TYPE, AUDIT_SPLIT.TYPE, PROJECT_TRAN.DATE, PROJECT_RESOURCE.NAME, STOCK_TRAN.STOCK_CODE, AUDIT_SPLIT.ACCOUNT_REF, PROJECT_ONLY_TRAN.REFERENCE, STOCK_TRAN.REFERENCE, AUDIT_SPLIT.INV_REF, PROJECT_ONLY_TRAN.EXTRA_REF, AUDIT_SPLIT.EXTRA_REF, PROJECT_COST_CODE.REFERENCE, PROJECT_ONLY_TRAN.NOMINAL_CODE, AUDIT_SPLIT.NOMINAL_CODE, AUDIT_SPLIT.DEPT_NUMBER, PROJECT_ONLY_TRAN.DEPT_NUMBER, PROJECT_ONLY_TRAN.DETAILS, AUDIT_SPLIT.DETAILS, STOCK_TRAN.DETAILS, PROJECT_ONLY_TRAN.QUANTITY, PROJECT_ONLY_TRAN.RATE, STOCK_TRAN.QUANTITY, STOCK_TRAN.COST_PRICE, AUDIT_SPLIT.NET_AMOUNT

10 different tables I think. You can get the joins required in report designer.

Kind regards,

Ian

[brucedenney]

The trouble with linking in all the tables is that is get very slow, so if you can skip some you can speed it up.

The relationships can be found by editing one of the project reports

Project Joins.png

[Onion RS]

Hi Bruce,

Eeek! By my count that report has 18 tables (although I'm not sure what to make of the top right table which doesn't appear to have any fields).

Do you mind me asking what report that screenshot is from?

Kind regards,

Ian

[brucedenney]

Screenshot 2024-03-18 104009.jpg

Project Activity PJACT.report

[Onion RS]

Hi Bruce,

That's really helpful. I'd clearly been looking at that in the development of my pivottable but had forgotten about the extra tables seen there. I put it down to advancing years. I must have twigged that a number of tables don't actually feature in the output of the PJACT.report and omitted them as per your suggestion "so if you can skip some you can speed it up". Cutting the number down to 10.

Kind regards,

Ian