Use an IF statement to determine if the value is 'Choice_Added', and overwrite the RowNumber value.  Then filter on the very outside to eliminate the values you don't want.

Do you have a database diagram for this part of the database?  It's hard to tell from the temporary tables how things are related, because there are so many foreign...

Creating datasets is trivial. You have a connection (DataSource), and then you build a DataSet by executing a stored procedure against that DataSource and it returns your dataset. Then you...

This appears to work for the data provided:
SELECT *
FROM test_table2 t2
WHERE t2.ID NOT IN (SELECT ID FROM test_table1)
AND t2.condition = 'Used';

As I see it, you have two options with the (CalendarID, StudentCounts) duplicates.  If you don't need grand totals, you can leave them in your underlying dataset, and then just...

I'm just going to snip off this piece... (this thing is mind-boggling!)

Bascially the counts are repeated a lot for the same calendared.
Could you show me how...

Are you adding the data to a tablix? I can't tell from your report design. If you're doing that, you can just add a totals line outside the group(s).

is there a way to check for page numbers that are even or odd?
MOD() ?
=IIF(Globals!PageNumber MOD 2 = 0, "Even", "Odd")

Oh, so you're a big fan of implicit conversion? Try it with a multi-million (or more) row table. Why not save yourself the trouble and make the parameter type match...

You won't need the DISTINCT if you use a windowing function to remove the duplicates too.

Use the standard ROW_NUMBER() OVER (PARTITION BY <grouping columns> ORDER BY <columns>) AS rn
to assign each duplicate a row_number and then filter for  rn = 1.

Yep, sounds like a permissions issue.
Okay, found an article here that's even better than the one I originally posted...
Execute As article.