Reporting Services SP using Dynamic Queries

Hey i am using Stored Procedures in Sql Reporting Services.
This SP has some parameters and based on these parameter's value Final query is being created at run time. But when i use this SP in VS.Net Reporting Services as dataset then it doesnt show its fields.

Here I am writing some part of my SP to show how things are working at my end. @.Country , @.StartDate , @.EndDate are SP parameters and based on these parameter’s value, some part of query is being added to main query. So in this case again I am not able to get fields in report.

IF LTRIM(RTRIM(@.Country)) IS NOT NULL

BEGIN
SET @.CountryQuery = " AND ID3.id_value LIKE '" + LTRIM(RTRIM(@.Country)) +"' "
END

IF LTRIM(RTRIM(@.StartDate)) IS NOT NULL AND LTRIM(RTRIM(@.EndDate)) IS NOT NULL
BEGIN
SET @.DateQuery = " AND ( "+@.FieldName+" >= '" + LTRIM(RTRIM(@.StartDate)) + "' AND "+@.FieldName+" <= '" + LTRIM(RTRIM(@.EndDate)) + "' ) "
END

SET @.Query = " SELECT DISTINCT field1, field2, field3 FROM Table1 T1 INNER JOIN Table2 T2 ON T1.id_fk = T2.id_code " + @.CityQuery + " WHERE tr_status =1" + @.DateQuery

EXEC (@.Query)

Even I have used temp table in SP and stored values in it but could not able to get desired result (get fields in report).

PLease help me
Thanks

Hi,

Try these steps:

1. In your DataSet put the query with fixed values (i.e. without parameters):

SELECT DISTINCT field1, field2, field3 FROM Table1 ...

2. Execute the Query

3. Go to the "DataSets Window" and select "refresh"

4. Now you should replace the query above with the Stored Procedure command

I think this solves your problem.

--

Telmo Moreira