hi ! The following condition works well in the SQL query surface, but when using it in the Reporting SErvices query designer area it make the designer to be closed . SUM(Case when (T1.[Posting Date] <= Convert(smalldatetime,'01/01/12'))  then T1.Amount Else 0 end) CValue, SUM(Case when (T1.[Posting Date] <= Convert(Smalldatetime,DATEADD(Year,-1,'01/01/12')))  then T1.Amount Else 0 end) PValue   To surprise, when the date value is '01/01/11' it works well in SSRS designer but not with the value '01/01/12' Also the table has values for that date fields of 2011 years and not yet for 2012.. might htis be the reason?? Kindly let me know.. --------------------------- Radhai Krish | Golden Age is no more far | --------------------------

Hi Radhai, From your scenario, I have tried to use your code with my table in Reporting Service query designer , but I found it works well and no error was raised, and my table has no data for 2012 also. Please refer to steps of my sample as below. 1. Create a new dataset in the Reporting Service Designer. 2. Type in the SQL script to the Reporting Service query designer using script A. Note: data parameter will generate automatically under the Parameters node when you use @date in your query designer . It will be better if you can supply more details about this issue, such as your table structure and how you use the code in your query designer and so on, and then we could help you better. Script A: Dataset for the report Select SUM(Case when (T1.[Date] <= Convert(smalldatetime,@Date))then T1.Amount Else 0 end) CValue, SUM(Case when (T1.[Date] <= Convert(Smalldatetime,DATEADD(Year,-1,@Date)))then T1.Amount Else 0 end) PValue from [NetIncurred] T1 If you have any question, please feel free to ask. Thanks, Eileen Zhao