SSRS Could not update a list of fields for the query

Question

I am trying to create a report, but my query seems to be causing problems even though it runs fine on Rapid SQL 8.5. The specific error message is below:

Could not update a list of fields for the query. Verify that you can connect to the data source and that your query syntax is correct. Invalid column name 'MMTEST%'.

To continue without updating the fields list, click OK

My query is here below:

select count(*), No.mm_territorynumber as NO,usr.domainname, usr.systemuserid, usr.fullname, usr.mm_PrimaryTeamIdName as PrimaryTeamName
from systemuser usr WITH (nolock) 
inner join dbo.teammembership mem with (nolock) on mem.systemuserid = usr.systemuserid
inner join dbo.team team with (nolock) on team.teamid = mem.teamid and mm_teamcategorycode is not null
inner join BusinessUnit  bu WITH (nolock)  on usr.BusinessUnitId = bu.BusinessUnitId
inner join BusinessUnit NO  WITH (nolock)  on NO.BusinessUnitId = bu.mm_GUID
where usr.systemuserid in (
select usr.systemuserid from systemuser usr WITH (nolock) 
inner join dbo.teammembership mem with (nolock) on mem.systemuserid = usr.systemuserid
inner join dbo.team team with (nolock) on team.teamid = mem.teamid
and mm_teamcategorycode = '930750004'
where usr.isdisabled = 0 
intersect 
select usr.systemuserid from systemuser usr WITH (nolock) 
inner join dbo.teammembership mem with (nolock) on mem.systemuserid =      usr.systemuserid
inner join dbo.team team with (nolock) on team.teamid = mem.teamid
and mm_teamcategorycode = '930750000'
where usr.isdisabled = 0
) and
"MMTEST%" not in (select usr.domainname from systemuser usr) and
--usr.domainname not like "MMTEST%" and
usr.systemuserid = team.AdministratorId  -- Only team adminstrators
group by No.mm_territorynumber, usr.domainname, usr.systemuserid, usr.fullname, usr.mm_PrimaryTeamIdName
having count(*) = 2
order by NO.mm_territorynumber, usr.fullname;

I have already done some research to ensure that my specific question had already not been answered. Here are some of the links that I've visited:

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/db1d1298-e5a9-4d4f-949e-aa570ccc6db3/ssrs-could-not-update-list-of-fields-for-the-query

https://social.msdn.microsoft.com/forums/sqlserver/en-US/96bca97a-e658-4f03-a246-5c8ae5e71c7b/could-not-create-a-list-of-fields-for-the-query

could not create a list of fields for the query

http://www.sqlchick.com/entries/2012/2/6/workaround-for-could-not-update-a-list-of-fields-for-the-que.html

Is there somewhere that I accidentally selected a column multiple times? After reviewing my code it doesn't seem so, but I am also very new to SQL so there may be some control flow issues that I don't understand. Any help would be appreciated.

EDIT: It seems that MMTEST% has been created as a column, but I have no idea why that happens.

Answer 1

Use single quotes instead of double quotes to filter out specific words in a value.