Sometimes, converting a manual attendance sheet over to a Reporting Services report can be a bit harder than it would at first seem, and can result in some odd code to produce the desired results. Case in point: a client recently asked for a report to emulate their attendance sheet, but to update the sheet with the attendance information each time it is run. The report had to display members of a Tag as rows, a number of static data fields about each member, and then have a dynamic listing of columns for the Occurrences they may have attended (or future occurrences they might not yet have attended).
The kicker is that the Occurrences are tied to a specific set of Small Groups, and the selected Tag members may or may not be members of the set of Small Groups. So we needed a way to make sure that we had everyone in the selected Tag (even if they had never attended any of the occurrences, so a NULL for that person in the attendance tables) and all of the selected Occurrences (even if nobody had attended them, so Nothing in the attendance tables at all) without any existing, direct way to link the persons to the occurrences they hadn't attended.
I tried a few different approaches before I realized that the problem was easily solved by applying an unusual and often shunned equation: "1=1". This comparison is frequently used as a placeholder for an actual variable, but all too often it is forgotten and suddenly there is a hard-coded loop or action somewhere in the program that nobody can explain, so most programmers look down on its use. In this case, however, it was exactly what was needed.
What I ended up with was a join statement like this:
FROM
core_profile_member AS cpm
JOIN (
SELECT co2.occurrence_id, co2.occurrence_start_time
FROM
core_occurrence AS co2
JOIN smgp_group_occurrence AS sgo2 ON sgo2.occurrence_id=co2.occurrence_id
WHERE sgo2.group_id IN (<Group Listing>)
) AS co ON 1=1
LEFT OUTER JOIN core_occurrence_attendance AS coa ON coa.person_id=cpm.person_id
AND coa.occurrence_id=co.occurrence_id
...
WHERE cpm.profile_id IN (<Tag Listing>)
...
This subquery ends up joining every single occurrence linked to those Small Groups with every single person from the required Tags, so I end up with a row for every person:occurrence combination, whether they have attendance info or not.
I ended up having to do some date manipulation techniques to end up with just 1 report of attendance per day (in the odd chance there might be multiple occurrences on the same day), as well as a few other tweaks to match the client's specific criteria, but this ends up with exactly what I need: people regardless of attendance and attendance regardless of people, without a direct link between the two.
