Solved

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

Posted on 2009-04-06
5
1,167 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 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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

Suggested Solutions

A quick way to get a menu to work on our website, is using the Menu control and assign it to a web.sitemap using SiteMapDataSource. Example of web.sitemap file: (CODE) Sample code to add to the page menu: (CODE) Running the application, we wi…
User art_snob (http://www.experts-exchange.com/M_6114203.html) encountered strange behavior of Android Web browser on his Mobile Web site. It took a while to find the true cause. It happens so, that the Android Web browser (at least up to OS ver. 2.…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

810 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