February 1, 2006 at 12:14 am
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).
Thanks,
Vivs
February 1, 2006 at 11:19 pm
Hi there,
Can you please tell in precise what is not working the SP or the Reporting Serv part.
Regards,
a_k93
February 1, 2006 at 11:24 pm
Problem was that fields of dataset were not coming in reporting services when we use Dynamic Queries in SP. I was tracking the problem and finally i got the solution. I am concluding the solution in following steps:
1. Create Dynamic Query based on parameter’s value
2. Insert result set cursor into a Temporary table ( Create Temporary table in the beginning of SP and use insert into #T Select field1, field1 from table )
3. Select data from Temporary table in the end of proc (Select * from #T)
4. Bind this SP with Report’s Dataset.
5. Now all the fields are coming in report.
Cheers,
Vivs
February 2, 2006 at 12:09 pm
I like having Reporting Services do as much work for me as it can. While creating my stored procedure, I get a final query or a table definition and use that SQL in RS. That lets RS create the field names. When I am happy with everything else, THEN I change the dataset to use the call to the SP. The field definitions remain.
Good luck.
"Laziness is a good trait in a programmer." - Larry Wall
Larry
February 2, 2006 at 12:21 pm
You need to "Refresh Fields" after you finish entering the EXEC statement. It's a little icon next to the dataset dropdown. If done properly, you will get a pop up asking you for the field values. Enter them and it will be done. It won't actually run the query though.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply