locked
programmically setting report viewer datasources RRS feed

  • Question

  • User746043490 posted

    Greetings!

    I have created a web application will will eventually be the holding place for several reports. Rather than creating a new webform for each report, I want to use the same report viewer for all of the reports. I have created an AccountSummary dataset (AccountSummaryDS.xsd) that has two tables, one for the header information and one for the details. I have two webforms in the app, a Default.aspx that captures the necessary employee information for the query and the ReportForm.aspx that has the reportviewer control.

    I came up with the code below for a Windows form application but am having issues translating it to be used in a webform. Any help would be greatly appreciated :)

    Sub DisplayReport()
            Dim sText As String = ""
            Dim paramPlanYear As String = "2010 - 2011"
            'Dim sReport As String = "FlexSpendReport_Test.YearToDate.rdlc"
            Dim sReport As String = "HREmployeeReports.YearToDate.rdlc"
            Dim dr As SqlDataReader
            'Dim cn As New SqlConnection(FlexSpendingConn)
            Dim cn As New SqlConnection(ConfigurationManager.ConnectionStrings("FlexSpendingConnectionString").ConnectionString)
            Dim cmd As New SqlCommand("RptFSAccountSummary", cn)
            cmd.CommandType = CommandType.StoredProcedure
            With cmd.Parameters
                .Add("@EmployeeID", SqlDbType.VarChar, 10).Value = EmployeeID
                .Add("@PlanYearStart", SqlDbType.DateTime).Value = #7/1/2010#
                .Add("@PlanYearEnd", SqlDbType.DateTime).Value = #6/30/2011#
            End With
            '------------------------------------------------
            '-------- Dimension First Dataset ---------------
            '------------------------------------------------
            Dim ds As New AccountSummaryDS  <-------Says its not defined
            Dim table As New DataTable
            Dim datarow As DataRow
            table = ds.AccountSummaryHeader
            '------------------------------------------------
            '--------- Fill First Dataset -------------------
            '------------------------------------------------
            Try
                cn.Open()
                dr = cmd.ExecuteReader()
                Do While dr.Read
                    datarow = table.NewRow
                    With datarow
                        .Item("EmployeeID") = dr("EmployeeID")
                        .Item("EmployeeName") = dr("EmployeeName")
                        .Item("EffectiveDate") = dr("EffectiveDate")
                        .Item("FileNumber") = dr("FileNumber")
                        .Item("PlanDescription") = dr("PlanDescription")
                        .Item("AnnualPledge") = dr("AnnualPledge")
                        .Item("RewardsAmount") = dr("RewardsAmount")
                        .Item("RewardsChoice") = dr("RewardsChoice")
                        .Item("EmployeeStatusDescription") = dr("EmployeeStatusDescription")
                        .Item("PlanYearBeginDate") = dr("PlanYearBeginDate")
                        .Item("PlanYearEndDate") = dr("PlanYearEndDate")
                    End With
                    table.Rows.Add(datarow)
                Loop
    
            Catch ex As Exception
                'Me.Cursor = Cursors.Default
                'MessageBox.Show(ex.Message, "Error Retrieving Search Results", _
                '                MessageBoxButtons.OK)
                lblError.Text = ex.Message
            Finally
                'Me.Cursor = Cursors.Default
                cn.Close()
                cn.Dispose()
            End Try
    
            '------------------------------------------------
            '-------- Dimension Second Dataset --------------
            '------------------------------------------------
            Dim dr1 As SqlDataReader
            'Dim cn1 As New SqlConnection(FlexSpendingConn)
            Dim cn1 As New SqlConnection(ConfigurationManager.ConnectionStrings("FlexSpendingConnectionString").ConnectionString)
            Dim cmd1 As New SqlCommand("RptFSAccountSummaryDetail", cn1)
            cmd1.CommandType = CommandType.StoredProcedure
            With cmd1.Parameters
                .Add("@EmployeeID", SqlDbType.VarChar, 10).Value = EmployeeID
                .Add("@PlanYearStart", SqlDbType.DateTime).Value = #7/1/2010#
                .Add("@PlanYearEnd", SqlDbType.DateTime).Value = #6/30/2011#
            End With
            Dim table1 As New DataTable
            Dim datarow1 As DataRow
            table1 = ds.AccountSummaryDetail
            '------------------------------------------------
            '--------- Fill Second Dataset ------------------
            '------------------------------------------------
            Try
                cn1.Open()
                dr1 = cmd1.ExecuteReader(CommandBehavior.CloseConnection)
                Do While dr1.Read
                    datarow1 = table1.NewRow
                    With datarow1
                        .Item("EmployeeID") = dr1("EmployeeID")
                        .Item("ServiceDate") = dr1("ServiceDate")
                        .Item("ExpenseTypeDescription") = dr1("ExpenseTypeDescription")
                        .Item("ExpenseAmount") = dr1("ExpenseAmount")
                        .Item("ExpenseStatusDescription") = dr1("ExpenseStatusDescription")
                        .Item("ProcessDate") = dr1("ProcessDate")
                        .Item("PersonCoveredDescription") = dr1("PersonCoveredDescription")
                        .Item("Note") = dr1("Note")
                    End With
                    table1.Rows.Add(datarow1)
                Loop
            Catch ex As Exception
                'Me.Cursor = Cursors.Default
                'MessageBox.Show(ex.Message, "Error Retrieving Search Results", _
                '                MessageBoxButtons.OK)
                lblError.Text = ex.Message
            Finally
                'Me.Cursor = Cursors.Default
                cn.Close()
                cn.Dispose()
            End Try
            '------------------------------------------
            '------- Open Form, bind report -----------             <---Im assuming I need to strip this out and place it in the ReportForm.aspx that has the reportviewer??
            '-----------------------------------------
            'Dim rds As New ReportDataSource
            Dim rds As New Microsoft.Reporting.WebForms.ReportDataSource
            'Dim rds1 As New ReportDataSource
            Dim rds1 As New Microsoft.Reporting.WebForms.ReportDataSource
            Dim frm1 As New ReportForm
            'frm1.StartPosition = FormStartPosition.CenterScreen
            frm1.ReportViewer.Reset()
            frm1.ReportViewer.ProcessingMode = ProcessingMode.Local
            frm1.ReportViewer1.LocalReport.DataSources.Clear()
            frm1.ReportViewer1.LocalReport.ReportEmbeddedResource = sReport
            'Dim param(0) As Microsoft.Reporting.WinForms.ReportParameter
            Dim param(0) As Microsoft.Reporting.WebForms.ReportParameter
            'param(0) = New Microsoft.Reporting.WinForms.ReportParameter("PlanYear", paramPlanYear)
            param(0) = New Microsoft.Reporting.WebForms.ReportParameter("PlanYear", paramPlanYear)
            frm1.ReportViewer.LocalReport.SetParameters(param)
            rds.Name = "AccountSummaryDS_AccountSummaryHeader"
            rds.Value = table
            frm1.ReportViewer.LocalReport.DataSources.Add(rds)
            rds1.Name = "AccountSummaryDS_AccountSummaryDetail"
            rds1.Value = table1
            frm1.ReportViewer.LocalReport.DataSources.Add(rds1)
            frm1.ReportViewer.RefreshReport()
            'frm1.ShowDialog()
    
        End Sub

    Happy Holidays!!!!!!!!!

     

    Wednesday, December 22, 2010 5:39 PM

Answers