SSRS 2008R2 - Calculation in row group expression not working due to aggregation of varying data types - is this due to negative values?
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.
Thanks to this post: 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)
- SQL Server stored procedure with ROW_NUMBER
- Nested Subquery vs Derived table
- LAST_VALUE function not working as expected
- How to append 'OPTION (MAXDOP 1)' to SQLAlchemy select statement?
- Better techniques for trimming leading zeros in SQL Server?
- Aggregate bitwise-OR in a subquery
- SSIS Error: External Column for Source out of sync with Data Source columns; How do I remove External Columns?
- How can I group time by hour or by 10 minutes?
- TSQL Calculate week number of the month
- SQL Proc Modification Question
- TSQL Pseudo Random text generator
- T-SQL SELECT DISTINCT & ROW_NUMBER() OVER Ordering Problem
- Sort Month Name in SQL Query
- What does the new stored procedure default content mean?
- When should I use semicolons in SQL Server?
- SQL Server TRY...CATCH Is Not Catching An Error
- Truncate (not round) decimal places in SQL Server
- How to use SET XACT_ABORT ON the right way
- Find if any row parameter value was equal to 1 in period of time
- How to best write a TSQL script to create or alter a stored procedure that has a table-value type as parameter, preserving GRANTs
- How to get identity of another table record inside after insert trigger caused by a transaction for current scope and Session?
- There is already an object named '#columntable' in the database
- How to alter a function with dozens of dependencies?
- Getting a count dynamically for Customers first time orders and also orders lost after last order date + 4 months, per year and month
- Replacing RAISERROR with THROW
- Paging with TSQL, MVC3 and SubSonic?
- Problem with SQL Query Tracking
- How can I get TSQL from easy MS Access SQL with little to no handiwork?
- Each GROUP BY expression must contain at least one column that is not an outer reference
- How do I perform a GROUP BY on an aliased column in SQL Server?