x Want to participate? Join the group to interact
Options
  • State Suggested Answer
  • Replies 7 replies
  • Answers 1 answer
  • Subscribers 19 subscribers
  • Views 313 views
  • Users 0 members are here
Browse Forums
Announcements
150
topics
113
replies
General Discussion
5k
topics
13k
replies
Payroll & Tax
631
topics
887
replies
Reporting and Analytics
573
topics
1k
replies
Service Alerts
1
topics
0
replies
Software Development Kit (SDK)
434
topics
671
replies
Year End
83
topics
128
replies
BamaDon

Inventory Turns Report

SUGGESTED
Posted By

I have been tasked to create an Inventory Turns Calculation in Crystal,  I have a report with all the items, a date range parameter and COGS calculation,  Piece of cake.  How would get average inventory?  Is there a table that gives you historical inventory on hand?  

Any insights appreciated.  I've been writing crystal for 20+ years but somewhat new a Sage 50.

  • 0
    SUGGESTED

    There is not a table that gives historical Inventory on Hand Quantities; you would have to build a SQL table for that purpose and then use that table within the report. We work with a custom programmer who has done that for other clients in the past. Please reach out to us if interested in a referral.

    Tammi M. Ermerins ([email protected])

    Certified Sage 50/Peachtree Accounting & Crystal Reports Consultant & Trainer

    800-780-0700 / 954-961-0600, Visit Us on the Web at https://pcosupport.com

  • 0

    You can schedule a Crystal Report to export the date and Inventory on Hand figures to a table every month (or whatever the desired frequency is. 

    Some of the 3rd-party Crystal Reports Schedulers listed here include that functionality. 

  • 0 in reply to ixm7

    Visual CUT can do that, but I have my doubts it can write to the Sage 50 database.  If we had a full, working version of Actian, fka Pervasive SQL, this would be a snap.  But I am not sure what we can do with the limited version of Actian that Sage installs.

  • 0 in reply to BamaDon

    Visual CUT can export the data to any ODBC target.
    So the scheduled process can read from SAGE and write to any desired database target (MS Access, MySQL, SQL Server, etc.).
    The report that uses the recorded monthly snapshot data would run against the desired target database.

  • 0 in reply to ixm7

    Yes it can, but I am not sure we can create an ODBC data source with the limited version of Actian that Sage installs.  For example the crystal reports for Sage 50 do not use ODBC at all, they use OLE and a Microsoft Data Link File (.udl).  Another example: we cannot even go to Actian Control Center and create a table to hold this data.  Why?  Because Sage does not install Actian Control Center.

    When I used Actian/Pervasive SQL with other ERP systems, most notably Macola, we could create tables, views, stored procedures, etc., but not with Sage 50.

    I admittedly am new at Sage 50 but if I am incorrect someone please show me how to do this.  It is a serious limitation on reporting.

  • 0 in reply to BamaDon

    Creating an ODBC DSN is done using Windows ODBC Admin dialog. It is not limited in any way by Sage.

  • 0 in reply to ixm7

    Quantity on Hand is not a standard field within Sage 50 so it can't be pulled without a formula using ODBC, which is where the GetPeach formulas come in play with Crystal Reports.

    Tammi M. Ermerins ([email protected])

    Certified Sage 50/Peachtree Accounting & Crystal Reports Consultant & Trainer

    800-780-0700 / 954-961-0600, Visit Us on the Web at https://pcosupport.com

*Community Hub is the new name for Sage City