如何通过动态筛选的分组 SSRS 和列进行合计

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

How to total by dynamic filtered group SSRS and Columns

问题

问题:
我想要根据筛选的“Campaign”来进行合计,但它包括了“result_type”代码列中所有与Campaign相关的内容,该值是“det_interaction_id”文本字段的计数。我得到了不正确的合计

设计视图:
设计视图中在组属性中通过Campaign筛选“MORTGAGE

输出:

日期 Campaign AM BD EX
7/10/2023 MORTGAGE_123 0 0 0
MORTGAGE_678 48 1 0
MORTGAGE_DEALER_321 16 0 0
MORTGAGE_DEALER_PURCHASE457 464 3 4
MORTGAGE_DOCS_213 16 2 0
总计 1,254 56 32

我应该得到的结果:

日期 Campaign AM BD EX
7/10/2023 MORTGAGE_123 0 0 0
MORTGAGE_678 48 1 0
MORTGAGE_DEALER_321 16 0 0
MORTGAGE_DEALER_PURCHASE457 464 3 4
MORTGAGE_DOCS_213 16 2 0
总计 544 6 4

我尝试过的方法:
这是普通的添加组合计算=Count(det_interaction_id.Value)

=IIf(Fields!Campaign.Value Like "*MORTGAGE*",Count(det_interaction_id.value),0)

英文:

Problem:
I would like to Total by the filtered “Campaign" but it includes all within the Campaign for the "result_type” code column and the value is a count of "det_interaction_id" text fields. I am getting the incorrect Totals
Design View:
The design view has a filter in group properties by Campaign “MORTGAGE

Output:

Date Campaign AM BD EX
7/10/2023 MORTGAGE_123 0 0 0
MORTGAGE_678 48 1 0
MORTGAGE_DEALER_321 16 0 0
MORTGAGE_DEALER_PURCHASE457 464 3 4
MORTGAGE_DOCS_213 16 2 0
Total 1,254 56 32

What I should get:

Date Campaign AM BD EX
7/10/2023 MORTGAGE_123 0 0 0
MORTGAGE_678 48 1 0
MORTGAGE_DEALER_321 16 0 0
MORTGAGE_DEALER_PURCHASE457 464 3 4
MORTGAGE_DOCS_213 16 2 0
Total 544 6 4

What I tried:
This normal Add Group Total =Count(det_interaction_id.Value)

=IIf(Fields!Campaign.Value Like "*MORTGAGE*",Count(det_interaction_id.value),0)

答案1

得分: 1

=SUM(IIf(Fields!Campaign.Value Like "抵押",1,0))

英文:

I dont know why this is but this worked for me to total dynamic column and filter by group

=SUM(IIf(Fields!Campaign.Value Like "MORTGAGE",1,0))

答案2

得分: 0

我认为问题在于IIF语句只检查第一个Campaign值而不是每个值。聚合函数应该位于IIF的外部。

=COUNT(IIF(Fields!Campaign.Value Like "MORTGAGE", det_interaction_id.value, NULL))

虽然表格正在过滤MORTGAGE,但我不确定总数为什么会更高。

英文:

I believe the issue is that the IIF statement is only checking the FIRST Campaign value and not EACH value. The aggregate function should be on the outside of the IIF.

=COUNT(IIF(Fields!Campaign.Value Like "*MORTGAGE*", det_interaction_id.value, NULL))

I'm not sure how the total would be higher if the table is filtering for MORTGAGE, though.

huangapple
  • 本文由 发表于 2023年6月30日 01:13:50
  • 转载请务必保留本文链接:https://go.coder-hub.com/76583254.html
匿名

发表评论

匿名网友

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

确定