Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2009-04-06
5
Medium Priority
?
1,189 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
  • 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 1500 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

I recently went through the process of creating a Calendar Control of events with the basis of using a database to keep track of the dates that are selectable, one requirement was to have the selected date pop-up in a simple lightbox.  At first this…
Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

971 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