Sage 100

 View Only

  • 1.  How to update a custom report that has SY0_CompanyParameters table and uses it in Record Selection Criteria

    Posted 09-23-2020 19:03
      |   view attached
    I'm re-writing a PR report for an upgrade from v2016 to v2020/PR 2.x, which has this Record Selection criterion:  
    (if {SY0_CompanyParameters.CompanyCode} = "SJK" then {PR_23PerptHistoryDetail.DeductionCode} in ["05", "11", "27", "28", "37", "38", "81", "83", "84", "87", "88"] else
    if {SY0_CompanyParameters.CompanyCode} = "HJD" then {PR_23PerptHistoryDetail.DeductionCode} in ["11", "29", "30", "32", "33", "39", "40", "42", "43", "81", "83", "84", "87", "88", "89"] else
    {PR_23PerptHistoryDetail.DeductionCode} in ["03", "04", "05", "11", "27", "28", "37", "38"])

    How is this selection criterion updated?  SY0_CompanyParameters is no more, correct?  Although I saw a Sage KB article that reads like you can add it in reports after Sage 100 v2016, I didn't think you could do it, and in Crystal 2016, I can't find a History folder.

    ------------------------------
    Barbara Goldstein
    Sr. Consultant
    DSD Business Systems
    San Jose CA
    800.627.9032 x221
    ------------------------------

    Attachment(s)

    pdf
    How to add parameter fields like Compan...pdf   76 KB 1 version


  • 2.  RE: How to update a custom report that has SY0_CompanyParameters table and uses it in Record Selection Criteria

    Posted 09-23-2020 19:04
    If Standard or Advanced it is still there.  Not available in Premium.

    ------------------------------
    Nancy Hanson
    Blytheco LLC
    Eagan MN
    ------------------------------



  • 3.  RE: How to update a custom report that has SY0_CompanyParameters table and uses it in Record Selection Criteria

    Posted 09-23-2020 19:17
    What folder @Nancy Hanson?  I am not seeing it in v2020 either.

    @Barbara Goldstein - What about using sy_company?​​

    ------------------------------
    Jeff Schwenk
    FORMER 90M Board Member
    Bottomline Software, Inc.
    Waynesboro VA
    540-221-4444
    ------------------------------

    Original Message


  • 4.  RE: How to update a custom report that has SY0_CompanyParameters table and uses it in Record Selection Criteria

    Posted 09-23-2020 19:26
    Well, I'll be.  I can't find it in Windows Explorer, but I can select it in Crystal Database Expert.  I could have sworn that years ago, we learned that that table was no more.

    ------------------------------
    Barbara Goldstein
    Sr. Consultant
    DSD Business Systems
    San Jose CA
    800.627.9032 x221
    ------------------------------

    Original Message


  • 5.  RE: How to update a custom report that has SY0_CompanyParameters table and uses it in Record Selection Criteria

    Posted 09-23-2020 19:27
    Maybe I'm misunderstanding the question.  I created a new report in 2019 over the weekend and was able to add SY0_CompanyParameters.  I apologize if that is not the question.  In Premium, that table is not available to add to the report.
    N

    ------------------------------
    Nancy Hanson
    Blytheco LLC
    Eagan MN
    ------------------------------

    Original Message


  • 6.  RE: How to update a custom report that has SY0_CompanyParameters table and uses it in Record Selection Criteria

    Posted 09-23-2020 19:34
    No, you understand the question.  I seem to recall from years ago that that table was "going away" with new business framework. In fact, that's when I learned about the "built-in" formula fields we could use to print the Company Name on custom reports as a replacement for SY0_CompanyParameters.

    ------------------------------
    Barbara Goldstein
    Sr. Consultant
    DSD Business Systems
    San Jose CA
    800.627.9032 x221
    ------------------------------

    Original Message


  • 7.  RE: How to update a custom report that has SY0_CompanyParameters table and uses it in Record Selection Criteria

    Posted 09-24-2020 08:38
    Edited by Steve Iwanowski 09-24-2020 08:38
    You can also fake the equivalent of SY0 in Premium if you run the following query once in SQL in each MAS_ company:

    CREATE VIEW SY0_CompanyParameters AS
    SELECT CompanyCode, CompanyName FROM MAS_SYSTEM.dbo.SY_Company where CompanyCode = right(db_name(),3)

    ------------------------------
    Steve Iwanowski, NextStep Technology Advisors, aka DSD Lancaster PA ¯\_(ツ)_/¯
    ------------------------------

    Original Message


  • 8.  RE: How to update a custom report that has SY0_CompanyParameters table and uses it in Record Selection Criteria

    Posted 09-24-2020 10:03
    No table or View should be necessary for reports run through Sage 100.  Just add specifically named formulas and the value will be passed into them runtime.

    if {@CompanyCode} = "SJK"...


    ------------------------------
    Kevin Moyes
    Technical Systems Analyst
    Munjal White Consulting Co.
    Toronto ON
    ------------------------------

    Original Message


  • 9.  RE: How to update a custom report that has SY0_CompanyParameters table and uses it in Record Selection Criteria

    Posted 09-25-2020 02:25
    There's a whole list of these passed in runtime fields common to every worktable report.  @Robert Wood wrote an article / blog about it at one point.​

    ------------------------------
    Alnoor Cassim

    Email: alnoor@asifocus.com
    Ph: 949-689-9887
    Orange County, CA
    ------------------------------

    Original Message


Related Content