Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
dtoddp
Starting Member
12 Posts |
Posted - 2012-10-03 : 10:39:02
|
Using SSRS 2008 R2, I've got a Tablix report that summarizes totals for a given group item. Where I want to change, is the Hours Column (there are 2 cost, columns that are SUM'd, and one Hours column that is SUM'd). I'd like to only print one specific Hours value (basically where the index = 2) and NOT SUM the HOURS field like the Cost fields are SUM'd. Was trying to put IIF logic in there so the SUM operation only did so if the Index = 2, but it keeps giving me errors.How do you not group/SUM a field in a Tablix when the other fields are being SUM'd?Thanks,DTP |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-10-03 : 10:46:08
|
how did you try? like this?SUM(IIF(Fields!Index.value=2,Fields!Hours.value,0))------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-10-03 : 10:46:43
|
What is the expression you are using? I would think something like this:=SUM(IIF(Fields!Index.Value=2,Fields!Hours.Value,0)) |
|
|
dtoddp
Starting Member
12 Posts |
Posted - 2012-10-03 : 10:53:27
|
This is the SUM statement with no modifications :=SUM(Fields!Est_Task_Hours.Value)The Index Value is basically "Fields!TaskIndex.Value"So the new Statement is =SUM(IIF(Fields!TaskIndex.value=2,Fields!Est_Task_Hours.value,0))But it gives me errors - reading the log below gives the following:[rsAggregateOfMixedDataTypes] The Value expression for the textrun ‘Est_Task_Hours.Paragraphs[0].TextRuns[0]’ uses an aggregate function on data of varying data types. Aggregate functions other than First, Last, Previous, Count, and CountDistinct can only aggregate data of a single data type.Preview complete -- 0 errors, 1 warningsThoughts?D |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-10-03 : 11:20:11
|
Convert both to the same data type - for example:=SUM(IIF(Fields!TaskIndex.value=2,CDbl(Fields!Est_Task_Hours.value),CDbl(0))) |
|
|
dtoddp
Starting Member
12 Posts |
Posted - 2012-10-03 : 11:25:18
|
Ok - Excellent, it worked! THANK YOU. Only I'm still a bit confused as to why 2 "different fields" had to have the same data type? |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-10-03 : 11:54:05
|
Glad to help!SSRS wants both Fields!Est_Task_Hours.value which would be picked up for some rows and the zero that would be picked up for the rest of the rows to have the same data type so it can add all of that up. Hence the need for conversion. |
|
|
|
|
|
|
|