x Want to participate? Join the group to interact
  • State Not Answered
  • Replies 8 replies
  • Subscribers 9 subscribers
  • Views 2519 views
  • Users 0 members are here
Browse Forums
Announcements
660
topics
57
replies
General Discussion
2k
topics
5k
replies
Intelligence Reporting
349
topics
837
replies
Theo Verster

Purchase Order Lines Received

Posted By

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.

  • 0

    Hi Theo.

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

  • 0

    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

  • 0

    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

  • 0
    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

  • 0

    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)

  • 0

    Dear Craig,

    Getting the following error, I am using Sage Evolution

    Description : Invalid sql query near SE WHEN "IM_PeriodPostingHistory"."FiscalCalYear" = CAST( AS CHAR) THEN (IM_PeriodPostingHistory.BeginningBalExtendedStdCost + IM_PeriodPostingHistory.PeriodChangeExtendedStdCost) ELSE 0 END

    ,
    CASE WHEN IM_PeriodPostingHistory.FiscalCalYear = CAST( AS CHAR) AND (IM_PeriodPostingHistory.FiscalCalPeriod = '01') THEN IM_PeriodPostingHistory.BeginningBalExtendedStdCost + IM_PeriodPostingHistory.PeriodChangeExtendedStdCost ELSE 0 END


    ,
    CASE WHEN IM_PeriodPostingHistory.FiscalCalYear = CAST( AS CHAR) AND (IM_PeriodPostingHistory.FiscalCalPeriod = '01' OR IM_PeriodPostingHistory.FiscalCalPeriod = '02') THEN IM_PeriodPostingHistory.BeginningBalExtendedStdCost + IM_PeriodPostingHistory.PeriodChangeExtendedStdCost ELSE 0 END


    ,
    CASE WHEN IM_PeriodPostingHistory.FiscalCalYear = CAST( AS CHAR) AND (IM_PeriodPostingHistory.FiscalCalPeriod = '01' OR IM_PeriodPostingHistory.FiscalCalPeriod = '02' OR IM_PeriodPostingHistory.FiscalCalPeriod = '03') THEN IM_PeriodPostingHistory.BeginningBalExtendedStdCost + IM_PeriodPostingHistory.PeriodChangeExtendedStdCost ELSE 0 END


    ,
    CASE WHEN IM_PeriodPostingHistory.FiscalCalYear = CAST( AS CHAR) AND (IM_PeriodPostingHistory.FiscalCalPeriod = '01' OR IM_PeriodPostingHistory.FiscalCalPeriod = '02' OR IM_PeriodPostingHistory.FiscalCalPeriod = '03' OR IM_PeriodPostingHistory.FiscalCalPeriod = '04') THEN IM_PeriodPostingHistory.BeginningBalExtendedStdCost + IM_PeriodPostingHistory.PeriodChangeExtendedStdCost ELSE 0 END


    ,
    CASE WHEN IM_PeriodPostingHistory.FiscalCalYear = CAST( AS CHAR) AND (IM_PeriodPostingHistory.FiscalCalPeriod = '01' OR IM_PeriodPostingHistory.FiscalCalPeriod = '02' OR IM_PeriodPostingHistory.FiscalCalPeriod = '03' OR IM_PeriodPostingHistory.FiscalCalPeriod = '04' OR IM_PeriodPostingHistory.FiscalCalPeriod = '05') THEN IM_PeriodPostingHistory.BeginningBalExtendedStdCost + IM_PeriodPostingHistory.PeriodChangeExtendedStdCost ELSE 0 END


    ,
    CASE WHEN IM_PeriodPostingHistory.FiscalCalYear = CAST( AS CHAR) AND (IM_PeriodPostingHistory.FiscalCalPeriod = '01' OR IM_PeriodPostingHistory.FiscalCalPeriod = '02' OR IM_PeriodPostingHistory.FiscalCalPeriod = '03' OR IM_PeriodPostingHistory.FiscalCalPeriod = '04' OR IM_PeriodPostingHistory.FiscalCalPeriod = '05' OR IM_PeriodPostingHistory.FiscalCalPeriod = '06') THEN IM_PeriodPostingHistory.BeginningBalExtendedStdCost + IM_PeriodPostingHistory.PeriodChangeExtendedStdCost ELSE 0 END


    ,
    CASE WHEN IM_PeriodPostingHistory.FiscalCalYear = CAST( AS CHAR) AND (IM_PeriodPostingHistory.FiscalCalPeriod = '01' OR IM_PeriodPostingHistory.FiscalCalPeriod = '02' OR IM_PeriodPostingHistory.FiscalCalPeriod = '03' OR IM_PeriodPostingHistory.FiscalCalPeriod = '04' OR IM_PeriodPostingHistory.FiscalCalPeriod = '05' OR IM_PeriodPostingHistory.FiscalCalPeriod = '06' OR IM_PeriodPostingHistory.FiscalCalPeriod = '07') THEN IM_PeriodPostingHistory.BeginningBalExtendedStdCost + IM_PeriodPostingHistory.PeriodChangeExtendedStdCost ELSE 0 END


    ,
    CASE WHEN IM_PeriodPostingHistory.FiscalCalYear = CAST( AS CHAR) AND (IM_PeriodPostingHistory.FiscalCalPeriod = '01' OR IM_PeriodPostingHistory.FiscalCalPeriod = '02' OR IM_PeriodPostingHistory.FiscalCalPeriod = '03' OR IM_PeriodPostingHistory.FiscalCalPeriod = '04' OR IM_PeriodPostingHistory.FiscalCalPeriod = '05' OR IM_PeriodPostingHistory.FiscalCalPeriod = '06' OR IM_PeriodPostingHistory.FiscalCalPeriod = '07' OR IM_PeriodPostingHistory.FiscalCalPeriod = '08') THEN IM_PeriodPostingHistory.BeginningBalExtendedStdCost + IM_PeriodPostingHistory.PeriodChangeExtendedStdCost ELSE 0 END


    ,
    CASE WHEN IM_PeriodPostingHistory.FiscalCalYear = CAST( AS CHAR) AND (IM_PeriodPostingHistory.FiscalCalPeriod = '01' OR IM_PeriodPostingHistory.FiscalCalPeriod = '02' OR IM_PeriodPostingHistory.FiscalCalPeriod = '03' OR IM_PeriodPostingHistory.FiscalCalPeriod = '04' OR IM_PeriodPostingHistory.FiscalCalPeriod = '05' OR IM_PeriodPostingHistory.FiscalCalPeriod = '06' OR IM_PeriodPostingHistory.FiscalCalPeriod = '07' OR IM_PeriodPostingHistory.FiscalCalPeriod = '08' OR IM_PeriodPostingHistory.FiscalCalPeriod = '09') THEN IM_PeriodPostingHistory.BeginningBalExtendedStdCost + IM_PeriodPostingHistory.PeriodChangeExtendedStdCost ELSE 0 END


    ,
    CASE WHEN IM_PeriodPostingHistory.FiscalCalYear = CAST( AS CHAR) AND (IM_PeriodPostingHistory.FiscalCalPeriod = '01' OR IM_PeriodPostingHistory.FiscalCalPeriod = '02' OR IM_PeriodPostingHistory.FiscalCalPeriod = '03' OR IM_PeriodPostingHistory.FiscalCalPeriod = '04' OR IM_PeriodPostingHistory.FiscalCalPeriod = '05' OR IM_PeriodPostingHistory.FiscalCalPeriod = '06' OR IM_PeriodPostingHistory.FiscalCalPeriod = '07' OR IM_PeriodPostingHistory.FiscalCalPeriod = '08' OR IM_PeriodPostingHistory.FiscalCalPeriod = '09' OR IM_PeriodPostingHistory.FiscalCalPeriod = '10') THEN IM_PeriodPostingHistory.BeginningBalExtendedStdCost + IM_PeriodPostingHistory.PeriodChangeExtendedStdCost ELSE 0 END


    ,
    CASE WHEN IM_PeriodPostingHistory.FiscalCalYear = CAST( AS CHAR) AND (IM_PeriodPostingHistory.FiscalCalPeriod = '01' OR IM_PeriodPostingHistory.FiscalCalPeriod = '02' OR IM_PeriodPostingHistory.FiscalCalPeriod = '03' OR IM_PeriodPostingHistory.FiscalCalPeriod = '04' OR IM_PeriodPostingHistory.FiscalCalPeriod = '05' OR IM_PeriodPostingHistory.FiscalCalPeriod = '06' OR IM_PeriodPostingHistory.FiscalCalPeriod = '07' OR IM_PeriodPostingHistory.FiscalCalPeriod = '08' OR IM_PeriodPostingHistory.FiscalCalPeriod = '09' OR IM_PeriodPostingHistory.FiscalCalPeriod = '10' OR IM_PeriodPostingHistory.FiscalCalPeriod = '11') THEN IM_PeriodPostingHistory.BeginningBalExtendedStdCost + IM_PeriodPostingHistory.PeriodChangeExtendedStdCost ELSE 0 END

    ,
    CASE WHEN IM_PeriodPostingHistory.FiscalCalYear = CAST( AS CHAR) AND (IM_PeriodPostingHistory.FiscalCalPeriod = '01' OR IM_PeriodPostingHistory.FiscalCalPeriod = '02' OR IM_PeriodPostingHistory.FiscalCalPeriod = '03' OR IM_PeriodPostingHistory.FiscalCalPeriod = '04' OR IM_PeriodPostingHistory.FiscalCalPeriod = '05' OR IM_PeriodPostingHistory.FiscalCalPeriod = '06' OR IM_PeriodPostingHistory.FiscalCalPeriod = '07' OR IM_PeriodPostingHistory.FiscalCalPeriod = '08' OR IM_PeriodPostingHistory.FiscalCalPeriod = '09' OR IM_PeriodPostingHistory.FiscalCalPeriod = '10' OR IM_PeriodPostingHistory.FiscalCalPeriod = '11' OR IM_PeriodPostingHistory.FiscalCalPeriod = '12') THEN IM_PeriodPostingHistory.BeginningBalExtendedStdCost + IM_PeriodPostingHistory.PeriodChangeExtendedStdCost ELSE 0 END

    FROM IM_PeriodPostingHistory INNER JOIN CI_Item ON IM_PeriodPostingHistory.ItemCode = CI_Item.ItemCode

    WHERE
    (IM_PeriodPostingHistory.FiscalCalYear = '2018'
    )

    Number : -2146233088
    Source :
    Driver Error : Unknown
    SQL State : Unknown

*Community Hub is the new name for Sage City