英文:
Parameter-dependent variables in SSRS report query
问题
我的SSRS报表有2个输入参数@myParm1和@myParm2。报表的主查询需要2个变量@myVar1和@myVar2，它们的值都由@myParm2确定。下面显示的简化代码通过SSMS可以正确执行。当我通过SSRS运行它（没有SET语句，并启用了适当的基于查询的数据集以允许用户选择@myParm1和@myParm2时），我得到一个空白报表，没有任何错误信息。SSRS允许设置通过报表属性对话框设置的变量，但我无法配置在我的主查询中起作用的SSRS变量。我应该使用SSRS变量还是需要一种不同的方法？
英文:
My SSRS report has 2 input parameters @myParm1 and @myParm2. The report's main query requires 2 variables @myVar1 and @myVar2, both of which have values determined by @myParm2. The simplified code shown below executes properly via SSMS. When I run it via SSRS (without the SET statements and with appropriate query-driven datasets enabling user selection of @myParm1 and @myParm2) I get a blank report without any error messages. SSRS allows variables which can be set up via the Report Properties dialog, but I haven't been able to configure SSRS variables that work in my main query. Should I be using SSRS variables or is a different approach needed?
DECLARE @myParm1 char(1) -- input parameter
DECLARE @myParm2 char(1) -- input parameter
DECLARE @myVar1 int -- variable dependent upon @myParm2
DECLARE @myVar2 int -- variable dependent upon @myParm2
SET @myParm1 = 'T'
SET @myParm2 = 'A'
IF @myParm2 = 'A'
BEGIN
SET @myVar1 = 1
SET @myVar2 = NULL
END
ELSE IF @myParm2 = 'B'
BEGIN
SET @myVar1 = 2
SET @myVar2 = 9670
END
ELSE IF @myParm2 = 'C'
BEGIN
SET @myVar1 = 3
SET @myVar2 = NULL
END
ELSE IF @myParm2 = 'D'
BEGIN
SET @myVar1 = 4
SET @myVar2 = NULL
END
select
@myVar1 as 'FirstEventType',
@myVar2 as 'SecondEventType',
(
select t3.Description
from myTable2 t2
inner join myTable3 t3 on t2.keyColumn = t3.keyColumn
where t2.Type = @myParm2
and t2.keyColumn = t1.keyColumn
) as 'TypeDescription',
(
select t2.DateColumn
from myTable2 t2
where t2.Type = @myParm2
and t2.keyColumn = t1.keyColumn
) as 'Date'
from myTable1 t1
where t1.PropertyType = @myParm1```
</details>
# 答案1
**得分**: 0
创建两个参数，并删除数据集内的@myParm1和@myParm2的任何DECLARE和SET（在我的示例中已注释掉）
[![enter image description here][1]][1]
显示参数工作的查询的简化版本
```sql
-- DECLARE @myParm1 CHAR(1) -- 输入参数
-- DECLARE @myParm2 CHAR(1) -- 输入参数
DECLARE @myVar1 INT -- 依赖于@myParm2的变量
DECLARE @myVar2 INT -- 依赖于@myParm2的变量
-- SET @myParm1 = 'T'
-- SET @myParm2 = 'A'
IF @myParm2 = 'A'
BEGIN
SET @myVar1 = 1
SET @myVar2 = NULL
END
ELSE IF @myParm2 = 'B'
BEGIN
SET @myVar1 = 2
SET @myVar2 = 9670
END
ELSE IF @myParm2 = 'C'
BEGIN
SET @myVar1 = 3
SET @myVar2 = NULL
END
ELSE IF @myParm2 = 'D'
BEGIN
SET @myVar1 = 4
SET @myVar2 = NULL
END
SELECT @myParm1 AS myParm1,
@myParm2 AS myParm2,
@myVar1 AS myVar1,
@myVar2 AS myVar2
要在值为null时显示NULL字符串，可以使用以下表达式：
=Iif(Fields!myParm2.value IS Nothing, "NULL", Fields!myParm2.value)
英文:
Create the two parameters and remove any DECLARE and SET of @myParm1 and @myParm2 inside the dataset (commented out in my example)
A simplified version of your query showing that parameters work
-- DECLARE @myParm1 CHAR(1) -- input parameter
-- DECLARE @myParm2 CHAR(1) -- input parameter
DECLARE @myVar1 INT -- variable dependent upon @myParm2
DECLARE @myVar2 INT -- variable dependent upon @myParm2
-- SET @myParm1 = 'T'
-- SET @myParm2 = 'A'
IF @myParm2 = 'A'
BEGIN
SET @myVar1 = 1
SET @myVar2 = NULL
END
ELSE IF @myParm2 = 'B'
BEGIN
SET @myVar1 = 2
SET @myVar2 = 9670
END
ELSE IF @myParm2 = 'C'
BEGIN
SET @myVar1 = 3
SET @myVar2 = NULL
END
ELSE IF @myParm2 = 'D'
BEGIN
SET @myVar1 = 4
SET @myVar2 = NULL
END
SELECT @myParm1 AS myParm1,
@myParm2 AS myParm2,
@myVar1 AS myVar1,
@myVar2 AS myVar2
To display NULL string when value is null you can use an expression like
=Iif(Fields!myParm2.value IS Nothing, "NULL",Fields!myParm2.value)
答案2
得分: 0
感谢 niktrs。
我已将您标记为答案。另外，我找到了另一种方法：不使用“依赖参数的变量”，而是使用隐藏的“依赖参数的参数”，其默认值由 @myParm2 确定。
使用我的原始示例，@myVar1 变成了 @myParm3，
和
@myVar2 变成了 @myParm4
然后，在 @myParm3 的默认值/指定值部分中使用以下表达式：
=Switch
(
Parameters!myParm2.Value = "A", 1,
Parameters!myParm2.Value = "B", 2,
Parameters!myParm2.Value = "C", 3,
Parameters!myParm2.Value = "D", 4
)
在 @myParm4 的默认值/指定值部分中使用以下表达式：
=Switch
(
Parameters!myParm2.Value = "A", Nothing,
Parameters!myParm2.Value = "B", 9670,
Parameters!myParm2.Value = "C", Nothing,
Parameters!myParm2.Value = "D", Nothing
)
并将主查询更改为：
select
@myParm3 as 'FirstEventType',
@myParm4 as 'SecondEventType',
(
select t3.Description
from myTable2 t2
inner join myTable3 t3 on t2.keyColumn = t3.keyColumn
where t2.Type = @myParm2
and t2.keyColumn = t1.keyColumn
) as 'TypeDescription',
(
select t2.DateColumn
from myTable2 t2
where t2.Type = @myParm2
and t2.keyColumn = t1.keyColumn
) as 'Date'
from myTable1 t1
where t1.PropertyType = @myParm1
英文:
Thanks niktrs.
I've marked yours as the answer. Also, I figured out another way: instead of "parameter-dependent variables", use hidden "parameter-dependent parameters" having default values determined by @myParm2
Using my original example, @myVar1 becomes @myParm3
and
@myVar2 becomes @myParm4
Then, use this expression in the @myParm3 Default Values / Specify Values section:
=Switch
(
Parameters!myParm2.Value = "A", 1,
Parameters!myParm2.Value = "B", 2,
Parameters!myParm2.Value = "C", 3,
Parameters!myParm2.Value = "D", 4
)
use this expression in the @myParm4 Default Values / Specify Values section:
=Switch
(
Parameters!myParm2.Value = "A", Nothing,
Parameters!myParm2.Value = "B", 9670,
Parameters!myParm2.Value = "C", Nothing,
Parameters!myParm2.Value = "D", Nothing
)
and change the main query to:
select
@myParm3 as 'FirstEventType',
@myParm4 as 'SecondEventType',
(
select t3.Description
from myTable2 t2
inner join myTable3 t3 on t2.keyColumn = t3.keyColumn
where t2.Type = @myParm2
and t2.keyColumn = t1.keyColumn
) as 'TypeDescription',
(
select t2.DateColumn
from myTable2 t2
where t2.Type = @myParm2
and t2.keyColumn = t1.keyColumn
) as 'Date'
from myTable1 t1
where t1.PropertyType = @myParm1
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论