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.

What are your rights in the database vs the person who can see everything? Sounds like you need a stored procedure with EXECUTE AS.

But don't take my word...

Couldn't you model it like this?

CREATE TABLE CallsFact (
     SurrogateCallKey INT IDENTITY
     , CallerID INT
     , CallType VARCHAR(8)
   ...

Did you read Jeff Moden's article on DelimitedSplit8K?

I used to work at a company that did healthcare data reporting.  I was stupefied when I found full names, birthdates and SSNs in the database. Went straight to the...