Question

SSRS Part supply and demand

  • 24 November 2021
  • 3 replies
  • 193 views
M
Userlevel 1

I am trying to build a report that shows our total supply and demand. A very condensed version of a time phase. I’d like a 1 row per part set of information.  I have got everything I need except for Forecast totals by part.

I am using Part, Part Whse, Part Plant, and Job info all linked up and good. As soon as I try to add the ForeQty to the report and do the query relationships it all goes awry and I get a bunch of duplication and summing issues on most of the fields I already had showing correct values

 

Good so far

Add forecast field and relationship and it goes wonky

If I unhide the details it looks like it is repeating for the 7 firm sales orders we have, but I don’t even have SO info on here.

Any ideas on what I am doing wrong or do I just have to build this a different way? 

3 replies

D

Can you post the query from the SSRS report? Also how are you grouping the report?

D
Userlevel 1

Forecast is usually entered monthly (at least this is what we do) so you’ll need a group by there, possibly a subquery for that. Posting the query will help us see what’s going on there.

Did you try the PartQty table ? Sounds like what you’re after.

Dragos

Epicor ERP 10.2.700.18
M
Userlevel 1

I am grouping by Part.ClassID then Part.PartNum. Here is the rest of query:

SELECT
  Erp.Part.PartNum
  ,Erp.Part.ClassID
  ,Erp.Part.ProdCode
  ,Erp.PartWhse.OnHandQty
  ,Erp.PartWhse.DemandQty
  ,Erp.PartClass.Description
  ,Erp.PartWhse.WarehouseCode
  ,Erp.Part.CostMethod
  ,Erp.PartPlant.MfgLotSize
  ,Erp.PartPlant.MinMfgLotSize
  ,Erp.Part_UD.Character13
  ,Erp.PartPlant.SafetyQty
  ,Erp.JobHead.ProdQty
  ,Erp.JobHead.JobClosed
  ,Erp.JobHead.JobNum
FROM
  Erp.Part
  INNER JOIN Erp.PartWhse
    ON Erp.Part.Company = Erp.PartWhse.Company AND Erp.Part.PartNum = Erp.PartWhse.PartNum
  INNER JOIN Erp.PartClass
    ON Erp.Part.Company = Erp.PartClass.Company AND Erp.Part.ClassID = Erp.PartClass.ClassID
  INNER JOIN Erp.PartPlant
    ON Erp.Part.Company = Erp.PartPlant.Company AND Erp.Part.PartNum = Erp.PartPlant.PartNum
  INNER JOIN Erp.Part_UD
    ON Erp.Part.SysRowID = Erp.Part_UD.ForeignSysRowID
  INNER JOIN Erp.JobHead
    ON Erp.Part.Company = Erp.JobHead.Company AND Erp.Part.PartNum = Erp.JobHead.PartNum
WHERE
  Erp.Part.InActive = N'False'
    AND Erp.JobHead.JobClosed = N'False'
  AND Erp.PartWhse.WarehouseCode IN (N'FG', N'Assy')

Any guidance would be appreciated.

Melissa

Reply


Terms & Conditions