To comapre a single date with multiple dates


  • Hi All,

    I am using fetch xml report in which there are two table Opportunity and activity

    One Opportunity with have multiple activity

    Opportunity has a field  called new_dateofapplication

    and Activity has a field called StartDateActivity which have multiple values

    i need to compare these dates somehow as below  but it didnt work


    for e.g

    Any help much appreciated



    Monday, April 28, 2014 2:07 PM


  • Hi Simran08,

    According to your description, you have two tables in your report. Now you want to compare the date in Opportunity table with the date in Activity table, sum all activity hours which their date are later than the start date. Right?

    In Reporting Service, if you want to compare two values in IIF() function without using Lookup/LookupSet, these two data fields need to be in same scope. Your date are from different tables. It seems you have different datasets for those tables. This may be the reason why you can’t make your expression working in your report. And for comparing data from different datasets, we only have Lookup/LookupSet function. However, this function is only working in conditions where the value of one field is equals to the value of the other field. So we can never use this function to judge if the value of one field is greater/less than the value of the other field. So in your scenario, it’s impossible to find a solution if your Activity table only has those two columns. Also if you get the total hours in that way (like you post in your picture), you can’t figure out how many hours in total hours are spent on each program. But I guess it supposed to have some fields in your Activity to show clients and program. If so, you just need to set groups in your table and get total in group footer. It will looks like below:

    If possible could you post the dataset and table structure or any other detail information about your report? That can help us tested your case in our local environment and do further analysis. Thank you.

    LookupSet Function (Report Builder and SSRS)

    Best Regards,
    Simon Hou

    Wednesday, April 30, 2014 1:15 PM