May 10, 2011 at 12:58 pm
Howdy
I'm have a tablix where I'm getting an average value based on two dates passed in by the user (@firstdate, @seconddate). The dates passed in become the values in the 'coveragedate' field.
So, I have a column group "coveragedate" which returns an average value for each date. Then, I want to subtract those averages from each other to get the difference in averages for the two dates. I found some code that allows me to do that by searching various forums, but something is funky because it is not subtracting the two average values correctly. I suspect it is because the AVG IIF statement is not ignoring nulls. Here is my expression. Does anyone have any thoughts of how to correct it to ignore null values in the coveragedate field when subtracting the averages from each other? The original statment was putting in a zero if the statement proved false, which I switched to using 'nothing'. But, that still didn't work.
=AVG(CDec(IIF(Fields!CoverageDate.Value=Parameters!seconddate.Value,Fields!elect_ee_prem.Value,nothing))) - AVG(CDec(IIF(Fields!CoverageDate.Value=Parameters!firstdate.Value,Fields!elect_ee_prem.Value,nothing)))
OR, if someone knows of an easier way to subtract column group values from each other in a tablix then by using the above expression, I'm happy to do that instead.
May 10, 2011 at 2:34 pm
subtract column group values from each other in a tablix
=ReportItems!textBoxName1.Value - ReportItems!textBoxName2.Value
This should work if you are subtracting within the same scope (Group or detail)
May 10, 2011 at 2:49 pm
Have you tried using ISNOTHING() ?
May 10, 2011 at 3:03 pm
Bro, there is one more solution to it.
Create two 'Calculated Fields' in your Dataset. One each for the Dates you mentioned, lets call the, Calc1 and Calc2.
Now in your tablix( u r using tablix?), add a column which simply has the expression =Calc1-Calc2.
I am sure this would work, just make sure the calculations for Calc1 and Calc2 are correct and in all refer to same rowgroup.
something like this
Calc1=(Fields!Field_Name.Value,"SUM",Dataset or rowgroupname)
ur table should look like this
Calc1 Calc2 CalculatedCol
=Exp1 =Exp2 =Calc1-Calc2
If this helps great, if not, kindly mention the correct solution that you get.
It helps immensely.
Regards,
Athar Iqbal
May 10, 2011 at 3:41 pm
Perhaps I'm missing something obvious, Daniel, but when you have a column group, you only have one text box representing the sum, average, or whatever you're doing on the grouping. You don't actually have two text boxes, so you can't subtract them from each other. It's only once the report renders that it show the column group as two columns split out by the dates the user enters.
May 11, 2011 at 9:19 am
Athar, I like your idea. I haven't worked with calculated fields in SSRS datasets before, and I'm a bit confused about how to write the expression, particularly considering that the coveragedate grouping in based on parameter values entered by the user.
So, if my column grouping is based on the field coveragedate (but I don't know what the value is--parameter names are firstdate and seconddate), and I'm averaging the field elect_ee_prem based on coveragedate, what would the expression look like that I need for the caculated field? If I have to use an AVG IIF statement like I was using originally to differentiate which paramter value to average the elect_ee_prem on, than I feel I'm right back to where I started with there being issues with the IIF statement not ignoring null values.
Does that make sense? Sorry if I'm being obtuse.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply