I’m trying to create a SSRS report in BIDS using SharePoint list as data source. I’m trying to create a Matrix report and I need help to do that. The list has columns department type and scheduled dates for each user like “User1, dept 1, 5/8/2011", "user1, dept 2, 5/6/2010,” etc. I have to create a report that shows like this 2011 2010 User Dept1 Dept2 Dept1 Dept2 1 5/8 5/6 The report I have already created shows 2 rows for User 1 with date for respective years. But I want a single row for each user which shows dates for both years like the above example. Any help is appreciated. Thanks

I changed the expression to IIF(Fields!DEPT_VALUE<>"Dept and Year(Fields!Scheduled_date.Value)=2011, Fields!Scheduled_date,"") and this worked. Thanks

Kumar, I'm not able to resolve second issue in my second post. I grouped my report by user name and this report shows only the first rows date for each user. if the user has multiple scheduled dates, it shows only the first rows and ignores all other rows for that user. I'm not sure where I'm going wrong. I'm using the same expression as above. Thanks again.

I'm able to create the matrix report. The problem is with If expressions when a user has more than 1 department. The If expression is. IIf(Fields!DEPT_TYPE.Value ="Dept1" or Fields!DEPT_TYPE.Value ="Dept2" or Fields! DEPT_TYPE.Value ="Dept3" And Year(Fields!SCHEDULED_DATE.Value) = 2011, Fields!SCHEDULED_DATE.Value,""). This expression does not show any results if the user is logged in all 3 departments. It works fine for only 1 department type. And the second issue is my report only shows one value for each User. If the User1 has date values in dept1 in 2011 and dept1 in 2010, the report only displays date for 2011 or the first value for the user in the SharePoint list. How do I solve this issue? Is there any other approach for getting this done? Thanks

Thanks for the reply Kumar, It displays results for the year 2010 but if the user has all 3 department types then the query does not work. For example: User Dept_type Scheduled_date user1 dept1,dept2,dept3. 8/2/2011. for the above scenario the result is null for the year 2011. i hope I made it clear. I have 10 Dept type in total, I'm not sure how to write the expression for combination's. Thanks again for the help.