100% 条形图 SQL - 是/否答案

Question

Sql Server 2016/Report Designer：

不确定这是否是放置此问题的正确标题，因此如果我错了，请纠正我。

我有以下几点：

SELECT [aField6] AS [CleanAndTidy],
[aField10] AS [HandWash],
[aField8] AS [PPEStorage],
[aField14] AS [PipesAndFittings],
[aField12] AS [COSHH]
FROM vAdvF_265
GROUP BY [aField6], [aField10], [aField8], [aField14], [aField12]

答案是：

CleanAndTidy    HandWash    PPEStorage  PipesAndFittings    COSHH
No                 No            No           No            No
No                 Yes           Yes          Yes           Yes
Yes                No            No           Yes           Yes
Yes                No            Yes          Yes           Yes
Yes                Yes           No           No            Yes
Yes                Yes           No           Yes           No
Yes                Yes           Yes          No            No
Yes                Yes           Yes          Yes           No
Yes                Yes           Yes          Yes           Yes

我希望将其放入每列 100% 堆叠条形图中(是/否为 50/50(，但是在 SSRS 中使用它时，我似乎找不到向图表提供数据的正确方法。

我也尝试过：

SELECT [Type1], [Count] -- YES Count
FROM (
SELECT 
SUM(CASE WHEN [aField6]='yes' THEN 1 ELSE 0 END) AS [CleanAndTidyYes],
SUM(CASE WHEN [aField10]='yes' THEN 1 ELSE 0 END) AS [HandWashYes],
SUM(CASE WHEN [aField8]='yes' THEN 1 ELSE 0 END) AS [PPEStorageYes],
SUM(CASE WHEN [aField14]='yes' THEN 1 ELSE 0 END) AS [PipesAndFittingsYes],
SUM(CASE WHEN [aField12]='yes' THEN 1 ELSE 0 END) AS [COSHHYes]
FROM vAdvF_265
) sums
UNPIVOT
([Count] FOR [Type1] IN
([CleanAndTidyYes], [HandWashYes], [PPEStorageYes], [PipesAndFittingsYes], [COSHHYes])
) AS unpivoted1
UNION
SELECT [Type], [Count] --NO Count
FROM (
SELECT 
SUM(CASE WHEN [aField6]='no' THEN 1 ELSE 0 END) AS [CleanAndTidyNo],
SUM(CASE WHEN [aField10]='no' THEN 1 ELSE 0 END) AS [HandWashNo],
SUM(CASE WHEN [aField8]='no' THEN 1 ELSE 0 END) AS [PPEStorageNo],
SUM(CASE WHEN [aField14]='no' THEN 1 ELSE 0 END) AS [PipesAndFittingsNo],
SUM(CASE WHEN [aField12]='no' THEN 1 ELSE 0 END) AS [COSHHNo]
FROM vAdvF_265
) sums
UNPIVOT
([Count] FOR [Type] IN
([CleanAndTidyNo], [HandWashNo], [PPEStorageNo], [PipesAndFittingsNo], [COSHHNo])
) AS unpivoted1;

这给了我：

Type1                                                                                                                            Count
-------------------------------------------------------------------------------------------------------------------------------- -----------
CleanAndTidyNo                                                                                                                   2
CleanAndTidyYes                                                                                                                  18
COSHHNo                                                                                                                          3
COSHHYes                                                                                                                         17
HandWashNo                                                                                                                       7
HandWashYes                                                                                                                      13
PipesAndFittingsNo                                                                                                               2
PipesAndFittingsYes                                                                                                              18
PPEStorageNo                                                                                                                     4
PPEStorageYes                                                                                                                    16

尽管第二个给了我所需的计数，但我仍在努力将其放入 100% 图表中，而不是每列的"是/否"答案。

有人可以帮助我指出正确的方向/提供任何建议或其他方法吗？

Answer 1

为条形图值添加一个表达式。在表达式中添加如下代码：

=SUM(IIF(Fields!CleanAndTidy.Value = "Yes", 1, 0))

sum 函数将添加数据集中每一行的总值。然后，您可以在其中添加 IIF 表达式以计算每列并根据表达式返回一个值。在上面的示例中，如果 CleanAndTidy 为"是"，则返回 1。否则，则返回 0。最终结果将是"是"答案的计数。

然后，您可以冲洗并重复洗手等。 冲洗并重复？明白了吗？