reporting-services - SSRS 使用现有数据集计算人口和问责制

Question

我在报告中有 2 个数据集，1 个包含总人口，第二个包含评论。我需要在一个字段中做的是显示完成的百分比。例如 dataset1.[评论] where Status = "incomplete" and Status = "high risk" / dataset2.[Population] where type = "high risk"

人口数据示例：

• 名称，风险等级
• 客户 123，高风险
• Client124，中等风险
• 客户 125，高风险
• 客户 126，高风险
• 客户 127，高风险
• 客户 128，高风险

查看数据示例

• 客户名称，审核状态
• 客户端 123，未完成
• Client124，完成
• 客户端 125，完成
• 客户端 126，未完成
• 客户端 127，未完成
• 客户端 128，未完成

所以 5 高风险人群，4 条评论不完整 = 80% 拖欠

我需要尝试用一种表达方式来做到这一点，而对于我的生活，我正在挣扎！也许是星期五的大脑！？

任何帮助表示赞赏。

标签： reporting-servicesexpression

Answer 1

检查下面的屏幕截图和步骤以获得所需的输出

示例输出

表达式详细信息

行组详细信息 - 1

行组详细信息 - 2

数据集1

select 'Client123' as [NAME],'HIGH RISK' as [RISK LEVEL]
union all
select 'Client124' as [NAME],'MEDIUM RISK' as [RISK LEVEL]
union all
select 'Client125' as [NAME],'HIGH RISK' as [RISK LEVEL]
union all
select 'Client126' as [NAME],'HIGH RISK' as [RISK LEVEL]
union all
select 'Client127' as [NAME],'HIGH RISK' as [RISK LEVEL]
union all
select 'Client128' as [NAME],'HIGH RISK' as [RISK LEVEL]

数据集2

select 'Client123' as [CLIENT NAME] , 'INCOMPLETE' as [REVIEW STATUS]
union all
select 'Client124' as [CLIENT NAME] , 'COMPLETE' as [REVIEW STATUS]
union all
select 'Client125' as [CLIENT NAME] , 'COMPLETE' as [REVIEW STATUS]
union all
select 'Client126' as [CLIENT NAME] , 'INCOMPLETE' as [REVIEW STATUS]
union all
select 'Client127' as [CLIENT NAME] , 'INCOMPLETE' as [REVIEW STATUS]
union all
select 'Client128' as [CLIENT NAME] , 'INCOMPLETE' as [REVIEW STATUS]

本报告中使用的表达式

1. Lookup(Fields!NAME.Value, Fields!CLIENT_NAME.Value, Fields!REVIEW_STATUS.Value, "DataSet2")

2. count(Lookup(Fields!NAME.Value, Fields!CLIENT_NAME.Value, Fields!REVIEW_STATUS.Value, "DataSet2"))

3. count(Lookup(Fields!NAME.Value, Fields!CLIENT_NAME.Value, Fields!REVIEW_STATUS.Value, "DataSet2"))/Count(Fields!NAME.Value,"RISK_LEVEL")

4. Count(Fields!NAME.Value, "RISK_LEVEL2")

我创建了两个示例报告。我提到了sample report1. 对于sample report2像第一个一样的组属性。