如何在SSRS中基于同一维度的两个参数创建报表

1. 配置独立参数组 #

• 创建两组独立参数：
  • 当月参数组：@TargetYear（目标年份）、@TargetMonth（目标月份），用于筛选第一列的单月成本数据
  • 累计参数组：@AccumYear（累计年份）、@AccumMonth（截止月份），用于筛选第二列的年初至该月累计数据
• 预算数据与实际数据时间维度一致时，可共享参数选项，减少重复配置

2. 调整SQL数据集逻辑 #

在现有数据集基础上，新增数据类别标识，区分单月/累计/预算单月/预算累计数据，示例SQL片段：

SELECT 
  DataCategory = CASE 
    WHEN IsActual = 1 AND Year = @TargetYear AND Month = @TargetMonth THEN '当月实际'
    WHEN IsActual = 1 AND Year = @AccumYear AND Month <= @AccumMonth THEN '累计实际'
    WHEN IsBudget = 1 AND Year = @TargetYear AND Month = @TargetMonth THEN '当月预算'
    WHEN IsBudget = 1 AND Year = @AccumYear AND Month <= @AccumMonth THEN '累计预算'
  END,
  CostAmount = SUM(Cost)
FROM YourCostTable
WHERE 
  (IsActual = 1 AND ((Year = @TargetYear AND Month = @TargetMonth) OR (Year = @AccumYear AND Month <= @AccumMonth)))
  OR (IsBudget = 1 AND ((Year = @TargetYear AND Month = @TargetMonth) OR (Year = @AccumYear AND Month <= @AccumMonth)))
GROUP BY 
  CASE 
    WHEN IsActual = 1 AND Year = @TargetYear AND Month = @TargetMonth THEN '当月实际'
    WHEN IsActual = 1 AND Year = @AccumYear AND Month <= @AccumMonth THEN '累计实际'
    WHEN IsBudget = 1 AND Year = @TargetYear AND Month = @TargetMonth THEN '当月预算'
    WHEN IsBudget = 1 AND Year = @AccumYear AND Month <= @AccumMonth THEN '累计预算'
  END

3. 矩阵报表列配置 #

• 矩阵列组绑定DataCategory字段，调整列顺序为「当月实际、当月预算、累计实际、累计预算」（通过列组排序功能实现）
• 数据单元格绑定CostAmount字段，对应展示各类别成本值

4. 新增act/budget-1计算列 #

在矩阵末尾添加计算列，针对单月和累计数据分别计算差异率：

• 当月差异率表达式：=IIF(Fields!当月预算.Value=0, 0, Fields!当月实际.Value / Fields!当月预算.Value - 1)
• 累计差异率表达式：=IIF(Fields!累计预算.Value=0, 0, Fields!累计实际.Value / Fields!累计预算.Value - 1)
• 加入空值判断避免除数为0报错

5. 参数联动优化（可选） #

• 设置@TargetMonth的可用值依赖@TargetYear，@AccumMonth依赖@AccumYear，确保月份选项匹配对应年份的有效月份
• 若需固定「累计月份与当月月份一致」的逻辑，可设置参数默认值，比如@AccumMonth默认等于@TargetMonth，@AccumYear默认等于@TargetYear

内容的提问来源于stack exchange，提问作者Robert Wójtowicz