Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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…
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

721 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