web
You’re offline. This is a read only version of the page.
close
Skip to main content

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics GP (Archived) / A reporting problem ...
Microsoft Dynamics GP (Archived)

A reporting problem (Crystal Reports)

(0) ShareShare
ReportReport
Posted on by Bill Stoltz 95

I am trying to create a report that will  list current inventory, as well as total sales for a specified period of time from that inventory. 

The problem I am having is once I add sales, inventory counts are added for each sale (so if an item is sold on 10 invoices, the current inventory is counted 10 times in the summary), or if I list total inventory by item and them try to sum sales, any item that has not sold in the specified period will not be included in the inventory count.

Is there a simple work around for this other than a subreport?  The boss wants the report to be exported cleanly to excel.

Thanks

 

*This post is locked for comments

I have the same question (0)
  • K Day Profile Picture
    K Day 7,365 on at

    I am not exactly clear on what your problem is.  What are these inventory counts you are talking about?  In your grouping, are you using the "Count" Function or the Sum?  Do you want to see every item regardless of if it was sold or not?  In that case, you can make your main table the inventory master, and then joun the sales line item table on that with a left join so that every item is listed.  You probably want to put a calculated field for quantity sold saying if it in null, then return a 0.

    If I remember Crystal properly, your details section would be the list of items and quantity sold, then you would group by item, then do a Sum of Qty Sold.  You can then hide the details section and all you would have is item number and sum of qty sold.

  • Bill Stoltz Profile Picture
    Bill Stoltz 95 on at

    Yes, we need to see every item regardless of sales or not.  We have listed the sales data in the details section, and the item quantity in the group, but once we add the sales data, any item that has not had any sales is dropped from the report. 

    The idea is to calculate a variation of a  GMROI.   I used the inventory master, and can generate that part of the report without a problem, but when I join the sales data, I get one of the two errors, either the inventory count becomes overstated as the sum function will add inventory for each line of sales data, or the inventory becomes understated, as  any item which has had not sales within the time period is unreported.

  • K Day Profile Picture
    K Day 7,365 on at

    When you say that once you add the sales data all items are dropped off the report, that tells me your  join is wrong linking the tables.

    Details Section - Start with the Inventory Item Master - gets you a list of all your items.  Bring in the Sales line item table and join them on Item number = Item Number.  Make sure your join is a left join because if it is an inner join (which I think is the default in CR), then if the item is being looked up in the Sales Line item table and there was no sale, then it won't return that line because it is looking for an exact match.  If you left join, it will return NULL.  Then you can handle that null.  Make a calculated field that says something to the effect of "If QTY SOLD IS Null, then 0, else QTY SOLD'  Drag that calculated field into the details section.  Run it and see your results.  You can even drag in your real QTY SOLD field to see the difference where some are returned as NULL and the corresponding calculation is 0.  ( i don't remember if CR actually returns NULL or just blank)

    Then insert your group, Group by item number, and then make the calulated field using Sum of calculated field QTY SOLD.  Hide the details section.  Then your list will look like this

    Item Number         Sum of QTY Sold

    Item A                            50

    Item B                            100

    Item C                           0

    Is this what you want?

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Introducing the 2026 Season 1 community Super Users

Congratulations to our 2026 Super Stars!

Meet the Microsoft Dynamics 365 Contact Center Champions

We are thrilled to have these Champions in our Community!

Congratulations to the March Top 10 Community Leaders

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics GP (Archived)

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans