A tag is associated with a child, and only indirectly with the parent.  If a tag were associated with a parent, then ALL children of that parent would have that tag....

You would need to use dynamic SQL, and you should never "simply plop" anything that will be executed into a query.  You should always take steps to prevent SQL injection.

The issue that you're seeing is that the results you get depend on what user interface you are using.  Specifically, the grid view in SSMS returns different results than the...

;
WITH CTE AS
(
    SELECT *, DENSE_RANK() OVER(ORDER BY n) AS dr
    FROM @Message m
    CROSS APPLY (
        SELECT 1
        FROM @vendor v
        WHERE v.MessageId = m.MessageId

What do you want to happen if there is information in the @vendor table, but it doesn't match any of the records in the @Message table?  No rows? Records that...

Re

As Thom has already said, this approach violates First Normal Form, and you've already run into problems because of this.  You should reconsider your approach.

Reported as spam.

Reported as spam.

Without sample data and expected results, it's very difficult to troubleshoot your query.  You can find how to post those in the first link in my signature.

My first...

Sorry, didn't realize this was in SSRS.  You don't want five separate datasets.  You should either be creating two datasets (one for the main report and one for the grant...