90 Minds Community

 View Only

  • 1.  Sage Intelligence linking issue

    Posted 01-27-2022 11:39
    I am working on an Inventory Report using Sage Intelligence.  I am trying to link the IM_PeriodPostingHistory, IM_ItemWhseHistoryByPeriod, and CI_Item files.  What I need to do is Use a Left Outer Join from the IM_PeriodPostingHistory file to the IM_WhseHistoryByPeriod file linked on Item Code, Warehouse, Fiscal Year, and Fiscal Period.  This allows me to get a beginning balance for the item in the warehouse as well as summary data for each period.  This works.  But when I try to link the CI_Item file so that I can get the description and the product line I invariably get an error.  Is there a way to combine a left outer join on two files then link to a third file?

    Thank you for the help.

    ------------------------------
    Chuck Peddy
    Smartbridge Partners
    ------------------------------


  • 2.  RE: Sage Intelligence linking issue

    Posted 01-27-2022 11:43
    I would add the description and the product line as sub-reports.

    ------------------------------
    Mary Jo Krueger
    Senior Software Consultant
    CLA (CliftonLarsonAllen LLP)
    Appleton WI
    920-996-1176
    ------------------------------



  • 3.  RE: Sage Intelligence linking issue

    Posted 01-27-2022 12:29
    Does Sage Intelligence's SQL implementation NOT follow either MS SQL or ANSI SQL rules? Chuck and I have gone around on this join problem for awhile and we keep getting "invalid column name" in the error message.

    ------------------------------
    Jerry Norman
    At-Large BOD Member, 90 Minds
    Smartbridge Partners
    512.419.1444 x112
    ------------------------------



  • 4.  RE: Sage Intelligence linking issue

    Posted 01-28-2022 07:02
    Could you take out the history posting report and do an Excel sumif’s on the warehouse one to get a total? Then you could get down to two data tables. Not sure what you have in the period posting table that you wouldn’t have in the warehouse posting one.

    Sent from my iPhone


    Original Message


  • 5.  RE: Sage Intelligence linking issue

    Posted 01-28-2022 10:35
    I want to thank everyone for your help!  I've been able to work the problem out.  I did a Left Outer Join from the IM_PeriodPostingHistory to the IM_Item WhseHistoryByPeriod then I was able to link on the ItemCode FROM the IM_PeriodPostingHistory to the CI_Item file.  The reason that the system kept throwing errors was because I was trying to type in the file names.  Apparently the Sage Intelligence doesn't like that.  When I used the "Insert Table" tool it recognized my SQL construct.  And, FWIW, the Graphical Tool is pretty worthless because it wouldn't let me arrange the tables in the proper order to make the joins work the way that I needed.

    ------------------------------
    Chuck Peddy
    Smartbridge Partners
    ------------------------------



Related Content