英文:
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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论