How to pass a parameter to a report SqlDataAdapter

[AkAlan]

I am trying to find the best way to pass a parameter to a report in my web app. Here is what I have done so far:

I have built a seperate class that holds my reports called "MyTelerikLib".
I built a report called "DepartmentReport.vb"
I added a SqlDataAdapter to the report called "SqlDataAdapter1"
    I configured the adapter to use an existing stored procedure that has a parameter called "@DepartmentID"
    If I hard code the SqlDataAdapter parameter value it works fine in preview mode
 
I have a webform in my main project with a ReportViewer on it and I assigned  "DepartmentReport.vb" as it's report.
I would like to be able to dynamically add the value to SqlDataAdapter1 during the reports OnLoad event.

I really want to get the parameter from a session variable but Hard Coded it to 7 here to test it.
Here is what I have been trying on the reports webform Page_Load event:

Protected



Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

    Dim rv As Telerik.Reporting.Report = New DepartmentReport()
    rv.ReportParameters("DepartmentID").Value = 7
    Me.ReportViewer1.Report = rv
 End Sub

   

[AkAlan]

Well I have a solution and it looks to be solid.

On my report I have a SqlDataAdapter which is configured to get it's data from a stored procedure with a parameter. I added a Public Property to the code of the report like this

 

	Public Property setDepartmentID() As Integer
	Get
	Return DirectCast(SqlDataAdapter1.SelectCommand.Parameters(0).Value, Integer)
	End Get
	Set(ByVal departmentID As Integer)
	SqlDataAdapter1.SelectCommand.Parameters(0).Value = departmentID
	End Set
	End Property

On the Page_Load event of the form that holds the report viewer I added this code

	Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
	Session("DepartmentID") = 10
	Dim rv = New DepartmentReport()
	rv.setDepartmentID = Session("DepartmentID")
	Me.ReportViewer1.Report = rv
	End Sub

So I guess my original problem was that I was trying to add a parameter to the report itself and not to the SqlDataAdapter. I will now try and figure out the best way to pass multiple parameters. Any suggestions or improvements on this solution would be great. I have spent a lot of time figuring this out, sure hope it is valid.

[Hrisi]

Hello Alan,

Your solution is perfect. Of course you can use your session variable directly (without setDepartmentID property) , but this should be made in the NeedDataSource event. In the event handler you should retrieve data and set the processing report's DataSource property.

Kind regards,
Hrisi
the Telerik team

Instantly find answers to your questions on the new Telerik Support Portal.
Check out the tips for optimizing your support resource searches.

[AkAlan]

Hi Hrisi, Thanks for the post back. I would like to explore your suggestion, I just don't know how to access a session variable from the reports NeedDataSource event. I assume there is a namespace I need to import to be able to access it but I don't know which one.

[Hrisi]

Hi Alan,

You can use Session in the similar way as in your Page_Load event handler because processing of the report is in the same application (Session is Application level object). Only in design-time Session is not valid and you can not Preview the report in the Visual Studio designer.

Best wishes,
Hrisi
the Telerik team

Instantly find answers to your questions on the new Telerik Support Portal.
Watch a video on how to optimize your support resource searches and check out more tips on the blogs.

[AkAlan]

Hi Hrisi,  I get around that by hardcoding a parameter in the SqlDataAdapter properties. I get the data I want in design time and it gets reset during run time.  It works for me until I find a better way.