I am building a report which must meet the following conditions on two datasets, extracted from two different data sources:
DATASET 1: From a SharePoint List DataSource
If Status = “Eligible to Move” OR “Pending QA/QC Review” OR “Re-Exam Completed”
AND IF:
DATASET 2: From a SQL Server Database DataSource
Schedule 1st Briefing Date is blank OR (1st Briefing Attendance = No Show AND Schedule 2nd Briefing Date is blank) OR (2nd Briefing Attendance = No Show) OR (Schedule 1st Briefing Date is a date prior to today’s date AND 1st Briefing Attendance is blank) OR (Schedule 2nd Briefing Date is a date prior to today’s date AND 2nd Briefing Attendance is blank)
I did this by adding filters to both data sources using expressions that model the logic above, and then doing a lookup in a table which uses DataSet1 Customer ID to match DataSet2 Customer ID and returns an issue status (peculiar to DataSet2). I could not add DataSet 2 fields to the DataSet 1 table, which is why I did the lookup. However this is not working. I have added my code for DataSet2 filtration below, in case something might be wrong with it.
I should also add I had the datasources and datasets set up for me in the report as I don't have credentials, but I was able to add filters to the datasets.
=IIF(IsNothing(Fields!FirstBriefingDate.Value)
or (Fields!FirstBriefingAttendance.Value = "No Show" and
IsNothing(Fields!SecondBriefingDate.Value)) or
Fields!SecondBriefingAttendance.Value = "No Show" or
(Fields!FirstBriefingDate.Value<Today() and
IsNothing(Fields!FirstBriefingAttendance.Value)) or
(Fields!SecondBriefingDate.Value<Today() and
IsNothing(Fields!SecondBriefingAttendance.Value)), 1, 0)
