Solved

how can you rebind a report viewer control with custom queried data

Posted on 2009-04-06
5
1,181 Views
Last Modified: 2012-05-06
I am using Visual Web Developer 2008 Express, SQL Server 2005 and .NET 3.5 SP1.  I have put a report viewer control on my page and it initially comes up with the data for the report.  I have added a Drop Down List which contains items I would like to use to filter the data in the report.  I added a button on click event to refresh the report viewer control data.  I can not get it to work - anyone have any info on how to rebind the data to the report viewer control.  I am querying the same fields I used in the original report xsd/rdlc.
FORM CODE:
 
<asp:DropDownList ID="ddl_school" runat="server">
            <asp:ListItem Value="10000">Test School 1</asp:ListItem>
            <asp:ListItem Value="10005">Test School 2</asp:ListItem>
            <asp:ListItem Value="10006">Test School 3</asp:ListItem>
        </asp:DropDownList>
        <asp:Button ID="Button1" runat="server" Text="Refresh Report" />
        <br /><br /><br />
        <rsweb:ReportViewer ID="ReportViewer1" runat="server" Font-Names="Verdana" 
            Font-Size="8pt" Height="400px" Width="400px">
            <LocalReport ReportPath="Report1.rdlc">
                <DataSources>
                    <rsweb:ReportDataSource DataSourceId="ObjectDataSource1" 
                        Name="parenttutorDataSet_lp_intVen_sch_v" />
                </DataSources>
            </LocalReport>
        </rsweb:ReportViewer>
    
        <asp:ObjectDataSource ID="ObjectDataSource1" runat="server" 
            SelectMethod="GetData" 
            TypeName="parenttutorDataSetTableAdapters.lp_intVen_sch_vTableAdapter">
        </asp:ObjectDataSource>
 
CODE BEHIND
 
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
Imports System.Collections
Imports System.Web
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports System.Web.UI.WebControls.WebParts
Imports System.Web.UI.HtmlControls
Imports Microsoft.Reporting.WebForms
Partial Class _Default
    Inherits System.Web.UI.Page
 
    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim rep As LocalReport = ReportViewer1.LocalReport
        rep.ReportPath = "report1.rdlc"
 
        Dim DBConn As SqlConnection
        Dim DBCommand As SqlDataAdapter
        DBConn = New SqlConnection(ConfigurationManager.ConnectionStrings("parenttutorConnectionString").ToString)
        Dim ds As New DataSet
 
        Dim sql As String = "SELECT sc_id, Student, datemade, lo, Teacher, ss, School, ts_datemade FROM dbo.lp_intVen_sch_v  where sc_id=" & Me.ddl_school.SelectedItem.Value
 
        DBCommand = New SqlDataAdapter(sql, DBConn)
        DBCommand.Fill(ds, "rs")
        Dim dsData As New ReportDataSource()
        rep.DataSources.Add(dsData)
 
    End Sub
 
End Class

Open in new window

0
Comment
Question by:john
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 29

Expert Comment

by:David H.H.Lee
ID: 24084191
Hi johnsails,
Have you try ObjectDataSource.FilterExpression Property?
eg:
vb
======
Protected Sub ObjectDataSource1_Filtering(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.ObjectDataSourceFilteringEventArgs)
        If Me.ddl_school.SelectedItem.Value <> "" Then
            e.ParameterValues.Clear()
            e.ParameterValues.Add("sc_id", Me.ddl_school.SelectedItem.Value)
        End If
    End Sub

aspx
====
<asp:ObjectDataSource ID="ObjectDataSource1" runat="server"
            SelectMethod="GetData"
            TypeName="parenttutorDataSetTableAdapters.lp_intVen_sch_vTableAdapter"  filterexpression="sc_id={0}" OnFiltering="ObjectDataSource1_Filtering">
<filterparameters>
              <asp:formparameter name="sc_id" formfield="ddl_school" defaultvalue="0" />
            </filterparameters>
        </asp:ObjectDataSource>

More details:
http://msdn.microsoft.com/en-us/library/system.web.ui.webcontrols.objectdatasource.filterexpression.aspx
0
 

Author Comment

by:john
ID: 24102263
Thank you!! Yes this works great for my example.  

However, I also have a date field. Can it be done for a date range?  Between sdate and edate for example??
0
 

Author Comment

by:john
ID: 24102507
After some more thought I'm not sure this is going to solve my problem entirely.  What I really wanted to do was rebind a new dataset to the report viewer control.  I am able to do this with a Gridview control and was hoping I could do the same thing with the report viewer control.  I have researched this heavily and have not found any solutions.  

The object filtering will work in most circumstances; however, I have complicated query statements.  For example in the query -  if School 1 is selected then the value for field lo='a', if school 2 is selected then the value for field lo='b'.   I need to be able to build a custom query, requery the DB, and then rebind the report viewer control with the result set.
0
 
LVL 29

Accepted Solution

by:
David H.H.Lee earned 500 total points
ID: 24104880
Hi johnsails,
>>...I have complicated query statements.  For example in the query -  if School 1 is selected then the value for field lo='a', if school 2 is selected then the value for field lo='b'.
You can always write custom and completed query on your side and filter the criteria based on filterexpression as suggested. Those logic can be filtered at OnFiltering event based on formparameter as defined under filterparameters group. This need to depend your query arrangement and how you control the "where clause" criteria as mentioned in your stored procedure. Remember, you also can write dynamic sql inside the store procedure and execute it accordingly. But, this is up to you to determine the method that preferred.



0
 

Author Comment

by:john
ID: 24283727
I do not want to bring down the entire dataset and then filter it.  The dataset is huge.  Can the report viewer control be rebound?
0

Featured Post

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Just a quick little trick I learned recently.  Now that I'm using jQuery with abandon in my asp.net applications, I have grown tired of the following syntax:      (CODE) I suppose it just offends my sense of decency to put inline VBScript on a…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
Suggested Courses

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question