SSRS报表生成器级联参数过滤透视表时遇语法错误求助

Let's break down why your first query is throwing that frustrating syntax error, while the second one works perfectly—plus step-by-step fixes to resolve the issue.

The Root Cause: Mismatched Operator for Multi-Value Parameters #

The error An expression of non-boolean type specified in a context where a condition is expected, near ',' almost always stems from a SQL syntax conflict when handling multi-value parameters. Here's the critical difference between your two queries:

Problematic First Query #

In both your main dataset and the final parameter dataset, you use:

ACC_PERIOD = @ACC_PERIOD

If @ACC_PERIOD is set to Allow multiple values (standard for cascading period parameters), SSRS replaces the parameter with a comma-separated string of selected values (e.g., '202301','202302'). This turns your condition into invalid SQL:

ACC_PERIOD = '202301','202302'

SQL Server can’t parse this—the = operator expects a single value, not a comma-separated list, which triggers the syntax error.

Working Second Query #

You correctly use:

[acctyymm] IN (@ACC_PERIOD)

When @ACC_PERIOD is multi-value, SSRS translates this into valid SQL that checks against all selected values, avoiding the syntax conflict entirely.

Step-by-Step Fixes #

1. Update the Main Dataset Query
   Change the ACC_PERIOD condition from = to IN to support multi-value selections:

   WHERE ACC_PERIOD IN (@ACC_PERIOD) 
     AND REINSURER IN (@REINSURER) 
     AND INSURER IN (@INSURER) 
     AND PLN IN (@PLN) 
     AND SVC_AGY IN (@SVC_AGY)
   

2. Fix the Final Parameter Dataset Query
   Apply the same fix to the ACC_PERIOD condition in your SVC_AGY parameter's dataset:

   SELECT DISTINCT [fasren_servicingagency] 
   FROM [FAS_ReinsuranceNumber]
   WHERE ACC_PERIOD IN (@ACC_PERIOD) 
     AND REINSURER IN (@REINSURER) 
     AND INSURER IN (@INSURER) 
     AND PLN IN (@PLN)
   ORDER BY SVC_AGY
   

3. Verify Parameter Settings

   • Double-check that every cascading parameter has Allow multiple values enabled (if you intend to let users select multiple options).
   • Confirm each parameter’s data type matches the corresponding database field (you noted all are character types, so just ensure consistency here).

Additional Checks If Issues Persist #

• If a parameter is meant to be single-value, disable Allow multiple values and keep using = for that parameter’s condition.
• For optional parameters that might be left blank, add logic to handle empty selections (e.g., (@REINSURER IS NULL OR REINSURER IN (@REINSURER))), though this isn’t likely the issue here since your error is strictly syntax-related.

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