SSRS 2012 issue with sql server 2000 data source

Question

Replications Steps

1. Set up a data source by connecting to a SQL Server 2000 data source via OLE DB. As SQL Server 2000 is not supported in SSRS 2012.

2. So then to set up data set. Using a dataset embedded in the report option. I select query type. I then put in query below.

   SELECT A.*
   FROM TableA AS A
   LEFT JOIN 
        (SELECT  
            TableB.RES_ID, Max(TableB.WeekCommencing) AS MaxOfWeekCommencing
         FROM TableB
         GROUP BY TableB.RES_ID
         HAVING ((
                 (Max(TableB.WeekCommencing)) >= @P1
                 Or 
                 (Max(TableB.WeekCommencing)) >= @P1 
                 Or 
                 (Max(TableB.WeekCommencing)) >= @P1))
        ) B ON A.RES_ID=B.RES_ID
   

When I click on Refresh fields I get Define Query parameter window that I press ok on then I get the error .

Could not create a list of fields for the query. Verify that you can connect to the data source and that your query syntax is correct.

In details, it says

Must declare variable in '@P1'

What am I doing wrong ? The parameter is already defined.

Answer 1

If this is a ole DB data source @param will not work. This works only with the Sql native client. So use ?? for paramenters in the query.

Answer 2

You can use local variables in your dataset code. This facilitates the mapping between the parameters of the report and those of the dataset.

This is especially useful if you have many parameters or if parameters are used several times.

Declare @P1 int
Declare @P2 int

Set @P1 = ?
Set @P2 = ?
;

SELECT A.*
FROM TableA AS A
LEFT JOIN 
     (SELECT  
         TableB.RES_ID, Max(TableB.WeekCommencing) AS MaxOfWeekCommencing
      FROM TableB
      GROUP BY TableB.RES_ID
      HAVING ((
              (MAX(TableB.WeekCommencing)) >= @P1
              OR
              (MAX(TableB.WeekCommencing)) >= @P1 
              OR 
              (MAX(TableB.WeekCommencing)) >= @P2))
     ) B ON A.RES_ID=B.RES_ID