英文:
SSRS 2008R2 - Calculation in row group expression not working due to aggregation of varying data types - is this due to negative values?
问题
以下是翻译好的内容：
第二个表格中“Net Effect” 行的表达式返回 #Error 作为结果（该表达式在不同数据类型的数据上使用聚合函数）。相同的表达式在其他表格中工作，但在这个表格中，当矩阵中的计数类型为“Renewed Member”时，存在负值。这是否是导致问题的原因？负值会被视为不同的数据类型吗？
在我的 SQL 查询中，我已经将 NULL 值转换为 0.00，如下所示：
SELECT COUNT(x.ccx_membershipid) as CountAll
, x.FinancialYear
, x.Month3Char
, x.MonthSort
, SUM(ISNULL(x.ccx_membershipfee,0.00)) AS FeeTotal
, x.CountType
FROM #Merged X
我的表达式是：
=SUM(IIf(Fields!CountType.Value="New Member",Fields!FeeTotal.Value,0.00))
+
Sum(IIF(Fields!CountType.Value="Win-back",Fields!FeeTotal.Value,0.00))
+
Sum(IIF(Fields!CountType.Value="Renewed Member",Fields!FeeTotal.Value,0.00))
-
Sum(IIF(Fields!CountType.Value="Inactive Companies",Fields!FeeTotal.Value,0.00))
-
Sum(IIF(Fields!CountType.Value="Lapsed Member",Fields!FeeTotal.Value,0.00))
-
Sum(IIF(Fields!CountType.Value="Resigned/Cancelled Member",Fields!FeeTotal.Value,0.00))
没有文本值，我能看到的。如果的确是负值导致了问题，我该如何解决？
提前感谢您能给予的任何建议。
英文:
The expression in the 'Net Effect' row in the second table is giving #Error as the result(the expression is using aggregate function on data of varying data types). The same expression works in the other tables but this table does have negative values where the count type = "Renewed Member" in the matrix. Is that what is causing the issue? Would negative values be classed as a different data type?
In my sql query I have converted the NULL values to 0.00 using this:
SELECT COUNT(x.ccx_membershipid) as CountAll
, x.FinancialYear
, x.Month3Char
, x.MonthSort
, SUM(ISNULL(x.ccx_membershipfee,0.00)) AS FeeTotal
, x.CountType
FROM #Merged X
My Expression is:
=SUM(IIf(Fields!CountType.Value="New Member",Fields!FeeTotal.Value,0.00))
+
Sum(IIF(Fields!CountType.Value="Win-back",Fields!FeeTotal.Value,0.00))
+
Sum(IIF(Fields!CountType.Value="Renewed Member",Fields!FeeTotal.Value,0.00))
-
Sum(IIF(Fields!CountType.Value="Inactive Companies",Fields!FeeTotal.Value,0.00))
-
Sum(IIF(Fields!CountType.Value="Lapsed Member",Fields!FeeTotal.Value,0.00))
-
Sum(IIF(Fields!CountType.Value="Resigned/Cancelled Member",Fields!FeeTotal.Value,0.00))
There are no text values that I can see. If it is indeed the negative values that are causing the issue how do I get around that?
Thank you in advance for any advice that you can give.
答案1
得分: 0
Thanks to this post: https://stackoverflow.com/questions/29126101/ssrs-2008r2-using-aggregate-on-field-with-an-expression
Adding Cdec around my expression AFTER the SUM has made it work.
=SUM(Cdec(IIf(Fields!CountType.Value="New Member",Fields!FeeTotal.Value,0.00)))
+
Sum(Cdec(IIF(Fields!CountType.Value="Win-back",Fields!FeeTotal.Value,0.00)))
+
Sum(Cdec(IIF(Fields!CountType.Value="Renewed Member",Fields!FeeTotal.Value,0.00)))
Sum(Cdec(IIF(Fields!CountType.Value="Inactive Companies",Fields!FeeTotal.Value,0.00)))
Sum(Cdec(IIF(Fields!CountType.Value="Lapsed Member",Fields!FeeTotal.Value,0.00)))
Sum(Cdec(IIF(Fields!CountType.Value="Resigned/Cancelled Member",Fields!FeeTotal.Value,0.00)))
英文:
Thanks to this post: https://stackoverflow.com/questions/29126101/ssrs-2008r2-using-aggregate-on-field-with-an-expression
Adding Cdec around my expression AFTER the SUM has made it work.
=SUM(Cdec(IIf(Fields!CountType.Value="New Member",Fields!FeeTotal.Value,0.00)))
+
Sum(Cdec(IIF(Fields!CountType.Value="Win-back",Fields!FeeTotal.Value,0.00)))
+
Sum(Cdec(IIF(Fields!CountType.Value="Renewed Member",Fields!FeeTotal.Value,0.00)))
-
Sum(Cdec(IIF(Fields!CountType.Value="Inactive Companies",Fields!FeeTotal.Value,0.00)))
-
Sum(Cdec(IIF(Fields!CountType.Value="Lapsed Member",Fields!FeeTotal.Value,0.00)))
-
Sum(Cdec(IIF(Fields!CountType.Value="Resigned/Cancelled Member",Fields!FeeTotal.Value,0.00)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论