SSRS中能否结合LookupSet/Lookup与Running Value使用？

Hey there! Let's work through this problem step by step—you're on the right track with combining lookup functions and running values, so let's fix that doubled-value issue first.

为什么你的Lookup返回数值虚高？ #

The most likely culprit is that your Lookup is returning multiple matching values without you realizing it. If your dataset KPI004 has multiple rows with the same StartFortnightDate (which makes sense for things like daily sales rolled up to fortnights), Lookup will only grab the first match—but if you're using it inside a RunningValue that iterates over every row in your main table, you might be accidentally counting the same value multiple times.

正确结合LookupSet + RunningValue的方法 #

Instead of using Lookup, use LookupSet to get all matching values for a date, then aggregate them into a single number first. Then wrap that in RunningValue with the correct scope to avoid over-counting.

Here's the adjusted expression you can try:

=RunningValue(
  Sum(LookupSet(Fields!StartFortnightDate.Value, Fields!StartFortnightDate.Value, Fields!NumberOfFordCarsSold.Value, "KPI004")),
  Sum,
  "YourFortnightGroupName"
)

Let's break this down:

• LookupSet gets all matching NumberOfFordCarsSold values for the current date from your dataset
• Sum aggregates those into a single total for the fortnight (use First instead if each date only has one value)
• RunningValue then accumulates this total across your date group—make sure to replace "YourFortnightGroupName" with the actual name of your main table's date group (not Nothing, which would run the calculation across the entire dataset and cause duplicates).

其他优化建议 #

• Check date matching: Ensure your StartFortnightDate fields are the exact same data type (e.g., Date not String) in all datasets—even a hidden time component can break matches and lead to unexpected results.
• Pre-calculate totals in datasets: If possible, adjust your dataset queries to pre-calculate the fortnightly totals and cumulative values. This takes the load off the report and avoids lookup-related issues entirely. For example, in SQL you could use a window function like SUM(...) OVER (ORDER BY StartFortnightDate) to get cumulative values directly from the database.
• Avoid ReportItems: As you noticed, ReportItems only references the first instance of a control, so it's not useful for cross-dataset or cumulative calculations—stick with lookup functions and dataset-level calculations instead.

最后验证 #

If you're still seeing doubled values, try testing the lookup part alone first (without RunningValue) to confirm it returns the correct single value per date. For example:

=Sum(LookupSet(Fields!StartFortnightDate.Value, Fields!StartFortnightDate.Value, Fields!NumberOfFordCarsSold.Value, "KPI004"))

If this shows the correct fortnightly total, then adding RunningValue with the right group scope should fix the cumulative calculation.

内容的提问来源于stack exchange，提问作者Beck