I have searched this forum and others and have not found a solution. I want to use the reports module to show projects associates with the users's custom profile field. The backend is SQL Server 2005. The query works if I use an actual UserId, but not when I use @UserId.

 The following results in the error message: There is an error in your SQL at line 3: Must declare the scalar variable "@UserID". Must declare the scalar variable "@UserID".

Select projects.projectid as [Project ID],onlninappid as [Online ID], participants.company as [Company],projects.projecttype as [Type], projects.QPStatus as [Status],Convert(varchar,preapprovaldate,101) as [Pre-Approved On], '$' + Convert(varchar,Convert(money,PreApprovedIncentiveAmount)) as [Pre-Approved For] FROM effrt.dbo.projects inner join effrt.dbo.participants on effrt.dbo.projects.participantid= effrt.dbo.participants.participantid WHERE tradeallyid in (Select PropertyValue From gdsrts.dbo.par_UserProfile Where PropertyDefinitionID = 58 AND UserId=@UserID) And datereceived > '10/01/2009' OR tradeallyid2 in (Select PropertyValue From gdsrts.dbo.par_UserProfile Where PropertyDefinitionID = 58 AND UserId=@UserID) And datereceived > '10/01/2009' ORDER BY DATERECEIVED DESC

This SQL works fine:

Select projects.projectid as [Project ID],onlninappid as [Online ID], participants.company as [Company],projects.projecttype as [Type], projects.QPStatus as [Status],Convert(varchar,preapprovaldate,101) as [Pre-Approved On], '$' + Convert(varchar,Convert(money,PreApprovedIncentiveAmount)) as [Pre-Approved For] FROM effrt.dbo.projects inner join effrt.dbo.participants on effrt.dbo.projects.participantid= effrt.dbo.participants.participantid WHERE tradeallyid in (Select PropertyValue From gdsrts.dbo.par_UserProfile Where PropertyDefinitionID = 58 AND UserId=10) And datereceived > '10/01/2009' OR tradeallyid2 in (Select PropertyValue From gdsrts.dbo.par_UserProfile Where PropertyDefinitionID = 58 AND UserId=10) And datereceived > '10/01/2009' ORDER BY DATERECEIVED DESC

Test successful, 22 records returned. 

Can someone please tell me what I am missing here? It seems like it is something simple...

Sebastian,

 Thanks for your reply. I have done this, but still get the error:

There was an error executing the data source: There is an error in your SQL at line 1: Must declare the scalar variable "@UserID". Must declare the scalar variable "@UserID".