August 8, 2011 at 10:24 am
Hi All,
I have report something like below
Report has Parameter : 2011
CANME Line Of Coverage Premiummonth PAmount
A Med 6/1/2011 200
A Med 7/1/2011 300
A Den 3/1/2011 400
A Den 1/1/2011 500
B Med 6/1/2011 200
B Med 7/1/2011 300
B Den 4/1/2011 400
B Den 1/1/2011 500
Needed like below:
CANME Line Of Coverage Premiummonth PAmount
A Den 1/1/2011 500
A Den 3/1/2011 400
AVERAGE for last three months for DEN: 0
A Med 6/1/2011 200
A Med 7/1/2011 300
Average on last three
months from current month MED: 166.66
B Den 4/1/2011 400
B Den 1/1/2011 500
AVERAGE for last three months DEN : 0
B Med 6/1/2011 200
B Med 7/1/2011 300
Average on last three
months from current month MED: 166.66
I added group by on CNAME , Line of coverage and I dont know how to achieve average on(PAmount) for only last three months(from current month) ,
but also display PAmount for all months for all Cnames across each Line of coverage
August 8, 2011 at 12:15 pm
I would add another column to your dataset
CASE WHEN PremiumMonth >= DATEADD(mm,-3,GetDate()) THEN PAmount ELSE 0 END AS P3Amount
Then simply use the P3Amount column for your average
August 8, 2011 at 2:46 pm
Getting errors while write the case statement...can we try same thing in switch??
August 8, 2011 at 2:52 pm
SWITCH is not available in T-SQL.
What is the error?
August 8, 2011 at 3:56 pm
Case is not working in SSRS...It is just showing #error
August 8, 2011 at 4:06 pm
My expression is :
= CASE WHEN Fields!PremuimMonth.Value >= DATEADD("mm",-3,NOW())THEN Fields!PremiumAmount.Value ELSE " " END As "AVERAGE AMOUNT"
August 9, 2011 at 7:21 am
I was suggesting that you put that in your dataset query, not in the report itself.
My experience is that pulling data with a "comb filter" style expression is a rather unpredictable issue within SSRS which is why I try to work that out in the dataset itself.
If you add some code similar to my example in your dataset, then all you have to do is do the math on the column with only the 3 month numbers.
August 9, 2011 at 9:43 am
I achieved this by adding an expression in report and also added group by Cname and Lofcoverage.I think it will be helpfull to others too.
My expression to achieve last three months Average:
=Round(SUM(IIf(Month(Fields!PremuimMonth.Value)>=Month(Now())-3,cDec((Fields!PremiumAmount.Value)),cDec(0)))/3,3)
Thanks for all the replies!!!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply