Solved

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

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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Microsoft Reports are based on a report definition, which is an XML file that describes data and layout for the report, with a different extension. You can create a client-side report definition language (*.rdlc) file with Visual Studio, and build g…
Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

757 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now