在SSRS报表查询中的参数依赖变量

huangapple
go评论136阅读模式
英文:

Parameter-dependent variables in SSRS report query

问题

  1. 我的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?

  1. DECLARE	@myParm1	char(1)	-- input parameter
  2. DECLARE	@myParm2	char(1)	-- input parameter
  4. DECLARE	@myVar1	int	-- variable dependent upon @myParm2
  5. DECLARE	@myVar2	int	-- variable dependent upon @myParm2
  7. SET	@myParm1 = 'T'
  9. SET	@myParm2 = 'A'
  11. IF	@myParm2 = 'A'
  12. 	BEGIN
  13. 		SET @myVar1 = 1
  14. 		SET @myVar2 = NULL
  15. 	END
  16. ELSE IF	@myParm2 = 'B'
  17. 	BEGIN
  18. 		SET @myVar1 = 2
  19. 		SET @myVar2 = 9670
  20. 	END
  21. ELSE IF @myParm2 = 'C'
  22. 	BEGIN
  23. 		SET @myVar1 = 3
  24. 		SET @myVar2 = NULL
  25. 	END
  26. ELSE IF @myParm2 = 'D'
  27. 	BEGIN
  28. 		SET @myVar1 = 4
  29. 		SET @myVar2 = NULL
  30. 	END
  32. select		
  34. 	@myVar1	as 'FirstEventType',
  36. 	@myVar2	as 'SecondEventType',
  38. 	(
  39. 	select		t3.Description
  40. 	from		myTable2 t2
  41. 	inner join	myTable3 t3 on t2.keyColumn = t3.keyColumn
  42. 	where		t2.Type	= @myParm2
  43. 	and		t2.keyColumn = t1.keyColumn
  44. 	) as 'TypeDescription',
  46. 	(
  47. 		select	t2.DateColumn
  48. 		from	myTable2 t2
  49. 		where	t2.Type	= @myParm2
  50. 		and	t2.keyColumn = t1.keyColumn
  51. 	) as 'Date'
  52. 				
  53. from	myTable1 t1
  54. where	t1.PropertyType	= @myParm1```
  56. </details>
  59. # 答案1
  60. **得分**: 0
  62. 创建两个参数，并删除数据集内的@myParm1和@myParm2的任何DECLARE和SET（在我的示例中已注释掉）
  64. [![enter image description here][1]][1]
  66. 显示参数工作的查询的简化版本
  68. ```sql
  69. -- DECLARE @myParm1 CHAR(1) -- 输入参数
  70. -- DECLARE @myParm2 CHAR(1) -- 输入参数
  71. DECLARE @myVar1 INT -- 依赖于@myParm2的变量
  72. DECLARE @myVar2 INT -- 依赖于@myParm2的变量
  74. -- SET @myParm1 = 'T'
  75. -- SET @myParm2 = 'A'
  77. IF @myParm2 = 'A'
  78. BEGIN
  79.     SET @myVar1 = 1
  80.     SET @myVar2 = NULL
  81. END
  82. ELSE IF @myParm2 = 'B'
  83. BEGIN
  84.     SET @myVar1 = 2
  85.     SET @myVar2 = 9670
  86. END
  87. ELSE IF @myParm2 = 'C'
  88. BEGIN
  89.     SET @myVar1 = 3
  90.     SET @myVar2 = NULL
  91. END
  92. ELSE IF @myParm2 = 'D'
  93. BEGIN
  94.     SET @myVar1 = 4
  95.     SET @myVar2 = NULL
  96. END
  98. SELECT @myParm1 AS myParm1,
  99.     @myParm2 AS myParm2,
  100.     @myVar1 AS myVar1,
  101.     @myVar2 AS myVar2

要在值为null时显示NULL字符串，可以使用以下表达式：

  1. =Iif(Fields!myParm2.value IS Nothing, "NULL", Fields!myParm2.value)

在SSRS报表查询中的参数依赖变量

英文:

Create the two parameters and remove any DECLARE and SET of @myParm1 and @myParm2 inside the dataset (commented out in my example)

在SSRS报表查询中的参数依赖变量

A simplified version of your query showing that parameters work

  1. -- DECLARE @myParm1 CHAR(1) -- input parameter
  2. -- DECLARE @myParm2 CHAR(1) -- input parameter
  3. DECLARE @myVar1 INT -- variable dependent upon @myParm2
  4. DECLARE @myVar2 INT -- variable dependent upon @myParm2
  6. -- SET @myParm1 = &#39;T&#39;
  7. -- SET @myParm2 = &#39;A&#39;
  9. IF @myParm2 = &#39;A&#39;
  10. BEGIN
  11. 	SET @myVar1 = 1
  12. 	SET @myVar2 = NULL
  13. END
  14. ELSE IF @myParm2 = &#39;B&#39;
  15. BEGIN
  16. 	SET @myVar1 = 2
  17. 	SET @myVar2 = 9670
  18. END
  19. ELSE IF @myParm2 = &#39;C&#39;
  20. BEGIN
  21. 	SET @myVar1 = 3
  22. 	SET @myVar2 = NULL
  23. END
  24. ELSE IF @myParm2 = &#39;D&#39;
  25. BEGIN
  26. 	SET @myVar1 = 4
  27. 	SET @myVar2 = NULL
  28. END
  30. SELECT @myParm1 AS myParm1,
  31. 	@myParm2 AS myParm2,
  32. 	@myVar1 AS myVar1,
  33. 	@myVar2 AS myVar2

To display NULL string when value is null you can use an expression like

  1. =Iif(Fields!myParm2.value  IS Nothing, &quot;NULL&quot;,Fields!myParm2.value)

在SSRS报表查询中的参数依赖变量

答案2

得分: 0

感谢 niktrs。

我已将您标记为答案。另外，我找到了另一种方法：不使用“依赖参数的变量”，而是使用隐藏的“依赖参数的参数”，其默认值由 @myParm2 确定。

使用我的原始示例，@myVar1 变成了 @myParm3，
和
@myVar2 变成了 @myParm4

然后，在 @myParm3 的默认值/指定值部分中使用以下表达式：

  1. =Switch
  2. (
  3. Parameters!myParm2.Value = "A", 1,
  4. Parameters!myParm2.Value = "B", 2,
  5. Parameters!myParm2.Value = "C", 3,
  6. Parameters!myParm2.Value = "D", 4
  7. )

在 @myParm4 的默认值/指定值部分中使用以下表达式：

  1. =Switch
  2. (
  3. Parameters!myParm2.Value = "A", Nothing,
  4. Parameters!myParm2.Value = "B", 9670,
  5. Parameters!myParm2.Value = "C", Nothing,
  6. Parameters!myParm2.Value = "D", Nothing
  7. )

并将主查询更改为：

  1. select      
  3.     @myParm3 as 'FirstEventType',
  5.     @myParm4 as 'SecondEventType',
  7.     (
  8.     select      t3.Description
  9.     from        myTable2 t2
  10.     inner join  myTable3 t3 on t2.keyColumn = t3.keyColumn
  11.     where       t2.Type = @myParm2
  12.     and     t2.keyColumn = t1.keyColumn
  13.     ) as 'TypeDescription',
  15.     (
  16.         select  t2.DateColumn
  17.         from    myTable2 t2
  18.         where   t2.Type = @myParm2
  19.         and t2.keyColumn = t1.keyColumn
  20.     ) as 'Date'
  21.                 
  22. from    myTable1 t1
  23. 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:

  1. =Switch
  2. (
  3. Parameters!myParm2.Value = &quot;A&quot;, 1,
  4. Parameters!myParm2.Value = &quot;B&quot;, 2,
  5. Parameters!myParm2.Value = &quot;C&quot;, 3,
  6. Parameters!myParm2.Value = &quot;D&quot;, 4
  7. )

use this expression in the @myParm4 Default Values / Specify Values section:

  1. =Switch
  2. (
  3. Parameters!myParm2.Value = &quot;A&quot;, Nothing,
  4. Parameters!myParm2.Value = &quot;B&quot;, 9670,
  5. Parameters!myParm2.Value = &quot;C&quot;, Nothing,
  6. Parameters!myParm2.Value = &quot;D&quot;, Nothing
  7. )

and change the main query to:

  1. select      
  3.     @myParm3 as &#39;FirstEventType&#39;,
  5.     @myParm4 as &#39;SecondEventType&#39;,
  7.     (
  8.     select      t3.Description
  9.     from        myTable2 t2
  10.     inner join  myTable3 t3 on t2.keyColumn = t3.keyColumn
  11.     where       t2.Type = @myParm2
  12.     and     t2.keyColumn = t1.keyColumn
  13.     ) as &#39;TypeDescription&#39;,
  15.     (
  16.         select  t2.DateColumn
  17.         from    myTable2 t2
  18.         where   t2.Type = @myParm2
  19.         and t2.keyColumn = t1.keyColumn
  20.     ) as &#39;Date&#39;
  21.                 
  22. from    myTable1 t1
  23. where   t1.PropertyType = @myParm1

huangapple
  • 本文由 发表于 2023年7月18日 08:32:13
  • 转载请务必保留本文链接：https://go.coder-hub.com/76708852.html
  • parameters
  • reporting-services
  • variables
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定