Is there a way to modify the SQL Query at runtime using some code? I have a situation where I have an ASP.NET application and I want to pass data to a SSRS report without adding parameters. Thank you.
A:
You can pass a dataset from ASP.NET to a ReportViewer control.
DataSet ds = GetDataSet();
ReportViewer1.LocalReport.ReportPath = “ReportServer/myReport.rdlc”;
ReportViewer1.LocalReport.DataSources.Add(new ReportDataSource(”ds”, ds.Tables[0]));
dretzlaff17
2010-09-23 18:33:37
Thank you for answering my question. I am getting the following error message:
Raja
2010-09-27 17:52:52
I am getting an error for this line: DataSet ds = GetDataSet(); Error 1 The name 'GetDataSet' does not exist in the current context
Raja
2010-09-27 17:54:04
The GetDataSet method is just a fake filler. You need to create a method that is responsible for obtaining a DataSet that can be passed to the report viewer.
dretzlaff17
2010-09-27 19:18:33
I need to be able to change the sql query inside the ssrs report at runtime. What is the best way to do that? I was able to do that in Crystal Report but not SSRS report. It is very frustrating.
Raja
2010-09-28 12:58:23
My recommendation is not to change the query in the SSRS report. Instead, populate you query in code with a DataSet, and then send that DataSet to the SSRS report. This way no query is needed inside the SSRS report, it is just acting as a host. Another option is to use dynamic sql inside a stored procedure that is called by your SSRS report, in order to make it dynamic.
dretzlaff17
2010-09-29 04:16:26
dretzlaff17, can you send me an code example on how to populate by query in code with a dataset?
Raja
2010-09-30 20:31:57
Here is a link to reference on how to populate a DataSet. http://msdn.microsoft.com/en-us/library/bh8kx08z(VS.71).aspx
dretzlaff17
2010-10-01 16:55:03