Please assist, I am trying create a report indicating Purchase Order Lines received in a specific period however I need to exclude non stock items and PO's for utilities. I only want to look at Stock Items.

Thus the GLAccountsFull must be linked, I have tried every which way but with no luck.

I want to include or exclude certain GL Accounts in the parameters.

Here is what I have now:

Kindly Requested

Theo Verster

Views: 52

Replies to This Discussion

Hi Theo.

I suggest posting under the relevant category on the forum: http://www.sageintelligencecommunity.com/forum

Hi Theo

You do not mention the system involved, however, in general there needs to be something that will connect to create a connection!

If you are using purchase order lines, do they contain a GL code? If so, then you will need to link that field to your "GLAccountsFull" field that contains the GL account number.

Another way would be to add an analysis field to your inventory items that flags "stock" items and then connect PO lines to the stock master file and only include items flagged as "stock" items in the query.

Happy Reporting!

Colin

Thank you, Colin

Perhaps you can help me with this one, I need to see the spend per month however when I run this particular report (attached) it lists the Material number in sequence. 

 We are Using Sage Evolution Version 7.20.7.000

Attachments:
Hi Theo
You are welcome.
I would add an excel formula to your query to calculate the values out - currently your data has a price per unit and a quantity but no sales value for each line. I see that some of the UOM fields are populated with "M" so I'll assume that means metre and that all others are each.
Create a second excel field in your query to concatenate Material Number and description. so it reads "1204254 - GAS SPRING 1300 NM", for example.Create another excel field for Year-Month (=YEAR(InvoiceDate)&"-"MONTH(InvoiceDate))
Then, instead of your column report on sheet3, use a pivot table with the concatenated field for the rows and the Year-Month as the columns.Drop the calculated value as the values and you are done. If you want to get fancy, add a filter for supplier.
If you are looking for spend per month by supplier, drop that as your row field instead of the concatenated field.
Hope that helps!
Colin

Thank you, Colin

SELECT dbo.POPORH1.PONUMBER, dbo.POPORH1.DATE, dbo.POPORL.COMPLETION, dbo.POPORL.ITEMNO, dbo.POPORL.ITEMDESC, dbo.POPORL.ITEMEXISTS, dbo.POPORL.LOCATION, dbo.ICACCT.INVACCT,
dbo.GLAMF.ACCTDESC
FROM dbo.ICITEM INNER JOIN
dbo.POPORH1 INNER JOIN
dbo.POPORL ON dbo.POPORH1.PORHSEQ = dbo.POPORL.PORHSEQ ON dbo.ICITEM.FMTITEMNO = dbo.POPORL.ITEMNO INNER JOIN
dbo.ICACCT ON dbo.ICITEM.CNTLACCT = dbo.ICACCT.CNTLACCT INNER JOIN
dbo.GLAMF ON dbo.ICACCT.INVACCT = dbo.GLAMF.ACCTID
WHERE (dbo.POPORL.COMPLETION > 1)

RSS

The Sage Intelligence Blog

Like Sage Intelligence?

Follow @SageSupport

© 2019   Created by Sage Alchemex.   Powered by

Badges  |  Report an Issue  |  Terms of Service