sql-server - 为什么我的总数没有正确显示，我该如何解决？

Question

我正在使用来自 SQL Server 的数据重新制作 SSRS 中的 Access 报告。在报告中，我有一个矩阵，其中一个值是 SumOfPieces。SumOfPieces 在我的查询中为sum(t1.pieces) as SumOfPieces. 在表格中，我只使用 [SumOfPieces] 就得到了正确的行值，但我的总数没有加在一起。例如，这就是我得到的：

Product | Facility | Shift/Line | Pieces
BFS     | BRWP     | A 1        | 65,000
BFS     | MHWP     | A 2        | 70,000
BFS     | MHWP     | B 2        | 80,000
________________________________________
Total   |          |            | 70,000

出于某种原因，它给了我中间值总计的表达式只是=Sum(fields!SumOfPieces.Value) 我尝试了类似此表达式的不同变体=Sum(avg(fields!SumPieces.Value,"Product1")

在 Access 中，这是通过嵌套 4-5 深的查询来完成的。对于这个领域，它看起来像

• 带有 t1.Pieces 的原始查询 t1
• t1 上的下一个查询，t1.Pieces 总和为 t1.SumOfPieces
• 下一个查询将 t1 与其他查询连接

Access 报表仅使用 SumOfPieces 作为行值，然后使用 sum(SumOfPieces) 作为总数。

我的数据集查询示例：

SELECT
   StaveHistorySummary.fk_Inspectors
  ,StaveHistorySummary.fk_InspectionSites
  ,StaveHistorySummary.fk_ProductionLines
  ,StaveHistorySummary.fk_ProductTypes
  ,StaveHistorySummary.DateMade
  ,StaveHistorySummary.[TimeStamp]
  ,StaveHistorySummary.StaveHistoryguid
  ,InspectionSites.SiteAbbr
  ,Inspectors.Name
  ,ProductTypes.Product
  ,ProductionLines.LineName
  ,CAST(sum(Millproduction.Pieces) as int) AS SumPieces
  ,CASE 
     WHEN SapEdgingInches IS NOT NULL THEN SapEdgingInches
     WHEN HeartEdgingInches IS NOT NULL THEN HeartEdgingInches
     WHEN BothEdgingInches IS NOT NULL THEN BothEdgingInches
     WHEN SawnIncorrInches IS NOT NULL THEN SawnIncorrInches
     WHEN EqualizedIncorrInches IS NOT NULL THEN EqualizedIncorrInches
     WHEN SawnOKInches IS NOT NULL THEN SawnOKInches
     END AS WIDTH
FROM
  StaveHistorySummary
  INNER JOIN ProductionLines
    ON StaveHistorySummary.fk_ProductionLines = ProductionLines.ProductionLines_NDX
  INNER JOIN InspectionSites
    ON StaveHistorySummary.fk_InspectionSites = InspectionSites.InspectionSites_NDX
  INNER JOIN ProductTypes
    ON StaveHistorySummary.fk_ProductTypes = ProductTypes.ProductTypes_NDX
  INNER JOIN Inspectors
    ON StaveHistorySummary.fk_Inspectors = Inspectors.Inspectors_NDX
  INNER JOIN MillProduction
    ON inspectionsites.inspectionsites_ndx = MillProduction.fk_inspectionsites
      AND productionlines.productionlines_ndx = MillProduction.fk_productionlines
      AND producttypes.producttypes_ndx = millproduction.fk_producttypes
WHERE (CAST(CAST(stavehistorysummary.DateMade as date) as datetime) BETWEEN '6/16/2019' AND '6/22/2019')
AND (CAST(CAST(MillProduction.DateMade as date) as datetime) BETWEEN '6/16/2019' AND '6/22/2019')
GROUP BY 
StaveHistorySummary.fk_Inspectors
  ,StaveHistorySummary.fk_InspectionSites
  ,StaveHistorySummary.fk_ProductionLines
  ,StaveHistorySummary.fk_ProductTypes
  ,StaveHistorySummary.DateMade
  ,StaveHistorySummary.[TimeStamp]
  ,StaveHistorySummary.StaveHistoryguid
  ,InspectionSites.SiteAbbr
  ,Inspectors.Name
  ,ProductTypes.Product
  ,ProductionLines.LineName
  ,CAST(sum(Millproduction.Pieces) as int) AS SumPieces
  ,CASE 
     WHEN SapEdgingInches IS NOT NULL THEN SapEdgingInches
     WHEN HeartEdgingInches IS NOT NULL THEN HeartEdgingInches
     WHEN BothEdgingInches IS NOT NULL THEN BothEdgingInches
     WHEN SawnIncorrInches IS NOT NULL THEN SawnIncorrInches
     WHEN EqualizedIncorrInches IS NOT NULL THEN EqualizedIncorrInches
     WHEN SawnOKInches IS NOT NULL THEN SawnOKInches
     END AS WIDTH

标签： sql-serverms-accessreporting-services