如何在SSRS同表中实现日/月聚合数据展示（不分组）

Hey there, let's walk through how to build this table in SSRS without grouping, showing all your products with daily and monthly production sums:

Step 1: Set Up the Basic Table #

1. Open your SSRS report and add a new Table control to the design surface.
2. Rename the three columns to 产品, 当日生产数量总和, and 当月生产数量总和 respectively.

Step 2: Populate the Product Column #

Drag the product field from your dataset directly into the first column's detail row. This will display every product entry from your dataset as a separate row (no grouping required here).

Step 3: Add Expression for Daily Production Total #

In the detail row of the 当日生产数量总和 column, right-click and select Expression. Paste this formula (replace YourDatasetName with your actual dataset name):

=Sum(IIF(Fields!product.Value = First(Fields!product.Value, "Details") AND Fields!date.Value = First(Fields!date.Value, "Details"), Fields!producedUnit.Value, 0), "YourDatasetName")

What this does: It iterates over all rows in your dataset, summing the producedUnit values only if the product and date match the current detail row's product and date. The "Details" scope refers to the current row in the table.

Step 4: Add Expression for Monthly Production Total #

For the 当月生产数量总和 column's detail row, use this expression (swap YourDatasetName with your dataset's name):

=Sum(IIF(Fields!product.Value = First(Fields!product.Value, "Details") AND Year(Fields!date.Value) = Year(First(Fields!date.Value, "Details")) AND Month(Fields!date.Value) = Month(First(Fields!date.Value, "Details")), Fields!producedUnit.Value, 0), "YourDatasetName")

This calculates the total units produced by the current row's product within the same month and year as the current row's date, summing all matching entries in your dataset.

Step 5: Test and Adjust #

• Preview your report to confirm the totals are calculating correctly.
• If your dataset already has one row per product per date (meaning producedUnit is already the daily total), you can simplify the daily expression to just =Fields!producedUnit.Value—keep the monthly expression as-is to get the month-wide sum for that product.
• If you renamed the detail group (default is "Details"), replace "Details" in the expressions with your custom group name.

That's it! This setup will show every product entry from your dataset without grouping, alongside the daily and monthly totals you need.

内容的提问来源于stack exchange，提问作者s.demuro