Sage Intelligence Consolidated Job Cost reports


I feel like the Not Equal join used in Crystal in order to combine the Company file in a report to be able to get the company name/code would not work in SI.  Is there a way of adding the company code for NON-FINANCIAL reports?

  • 0

    This is the code I use.

    Can add this to the Container - Properties - Source Container.

    I place it at the end.

    Will let you select from the SY_Company table.

    Replace AR with JC

    LEFT JOIN "AR_Options" ON
    "AR_Options"."CompanyCode" = "AR_Options"."CompanyCode")
    LEFT JOIN "SY_Company" ON
    "AR_Options"."CompanyCode" = "SY_Company"."CompanyCode")

  • 0 in reply to Doc102208

    I suck at SQL joins.  So is AR_Options linked to anything?  Or does a SQL join let you have one join, like AR_OpenInvoice to AR_Customer, and then a separate join like above?

  • 0 in reply to bethbowers

    Replace AR with JC. i.e. JC_Options.

    What is the Source Container Type for the report.

    If you C & P the SQL code I can show you were it goes.

  • 0 in reply to Doc102208

    I'm more trying to get the theory behind this than a specific thing.  So say I'm doing a report where I'm linking AR_OpenInvoice to AR_Customer using the DivisionNo and CustomerNo fields.  How does AR_Options come into play?  (I'm thinking I really probably only need that file because they just want the code, but will keep the info about SY.Company) 

  • +1 in reply to bethbowers
    verified answer

    Adding the code will let you when populating the container with data fields will let you select from both the AR_Option & SY_Company.

    AR_Option contains the Co Code

    SY_Company contains Co Code & Co Description.

    The following is linking AR_TransactionPaymentHistory to the AR_Customer files.

    INNER JOIN "AR_Customer"
      ON "AR_TransactionPaymentHistory"."ARDivisionNo" = "AR_Customer"."ARDivisionNo"  AND "AR_TransactionPaymentHistory"."CustomerNo" = "AR_Customer"."CustomerNo")

    LEFT JOIN "AR_Options" ON
    "AR_Options"."Divisions" = "AR_Options"."Divisions")
    LEFT JOIN "SY_Company" ON
    "AR_Options"."CompanyCode" = "SY_Company"."CompanyCode")

    If you use Graphical Linking you can make the link. Then change it from Graphic to Join. Than add the Company Code lines at the bottom.

  • 0 in reply to Doc102208

    EXCELLENT - exactly what I needed to know!

Reply Children
No Data