Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1006
  • Last Modified:

Attach a dataset to a reportview in VB.NET

I am new to reportviewer in visual studio 2008.

I want to create a dataset using code and then display that in a report.

Basically I need to pass a variable to the reportviewer sql statement so it only display's data based on that variable.

Something like "select WO_TYPE, WO_TYPE_ID from WO_TABLE where WO_TYPE = 'variable.value' and then the report will only display the report where that value = the variable.

I have tried to create a report and bind it to the dataset using the qui then modify the binding to use my code generated dataset. But I haven't been successful.  

If any body can tell me how to create a report based on a variable it would be great.

Thanks
Fixitben
0
fixitben
Asked:
fixitben
4 Solutions
 
yatin_81Commented:
See the code ::
ReportViewer1.ServerReport.ReportServerCredentials.NetworkCredentials = New System.Net.NetworkCredential("report", "report")
      ReportViewer1.Height = Me.Height - 50
      ReportViewer1.Width = Me.Width - 20
      Dim strEntityAdd, strEntityAdd2, strEntityTel, strEntityFax, strEntityWeb As String
      Dim cmd As New SqlCommand("select entityadd,entityadd2,entitytel,entityfax,entityweb from entitymaster where entitycode='" & gbl_entitycode & "'", _Globalobj.globalconn)
      'cmd.CommandType = CommandType.StoredProcedure
      Dim ds As New DataSet
      Dim da As New SqlDataAdapter
      da.SelectCommand = cmd
      da.Fill(ds)

      Me.ReportViewer1.RefreshReport()
      Me.ReportViewer1.ShowExportButton = False
      Me.ReportViewer1.ShowPrintButton = False

      ReportViewer1.ServerReport.ReportServerUrl = New Uri("http://" & rsServerName & ":" & rsPort & "/" & rsSite)
      If strBuySell = "S" Then
        Me.ReportViewer1.ServerReport.ReportPath = "/" & curdb & "/RDLName"
      Else
        Me.ReportViewer1.ServerReport.ReportPath = "/" & curdb & "/RDLName"
      End If
      'ReportViewer1.ServerReport.ReportServerCredentials.ImpersonationUser
      Dim p(3) As Microsoft.Reporting.WinForms.ReportParameter
      p(0) = New Microsoft.Reporting.WinForms.ReportParameter("FromDate", strFromDate)
      p(1) = New Microsoft.Reporting.WinForms.ReportParameter("ToDate", strToDate)
      p(2) = New Microsoft.Reporting.WinForms.ReportParameter("FromCode", strFromCode)
      p(3) = New Microsoft.Reporting.WinForms.ReportParameter("ToCode", strToCode)

      ReportViewer1.ServerReport.SetParameters(CType(p, Global.System.Collections.Generic.IEnumerable(Of Global.Microsoft.Reporting.WinForms.ReportParameter)))
      ReportViewer1.ShowParameterPrompts = False
      ReportViewer1.ProcessingMode = ProcessingMode.Remote
      ReportViewer1.RefreshReport()

Open in new window

0
 
CodeCruiserCommented:
Here is complete tutorial about parameters in reporting services

http://msdn.microsoft.com/en-us/library/aa337432%28v=SQL.90%29.aspx
0
 
RADONE242Commented:
Could you put the sql results into a dataset and add the dataset to the reports datasources?

        Me.ctlReportViewer.LocalReport.DataSources.Clear()
       Me.ctlReportViewer.LocalReport.DataSources.Add(New ReportDataSource("DataSourceName", Dataset))
       
        'Parameters
        Me.ctlReportViewer.LocalReport.SetParameters(parItems)
        'Refresh report
        Me.ctlReportViewer.RefreshReport()

Once the datasource has been established you can sort and filter in any table that has the datasource selected.
0
 
fixitbenAuthor Commented:
I got it working using a generated report using the wizard based on a dataset.

Then using this code I can create the same dataset with the changes to the where statement based on a variable.

Now I can control the report and have VS create the report for me.

Thanks For your help.
Fixitben


Imports System.Data.OleDb
Imports System.Data
Imports System.Data.SqlClient
Imports System.Windows.Forms
Imports Microsoft.Reporting.WinForms

Public Class Form2
    Private strConnectionString As String = "Provider=SQLOLEDB;Data Source=xxxxxx;Initial Catalog=xxxxxx;Integrated Security=SSPI;"
    Private objConnection As OleDbConnection
    Private objCommand As OleDbCommand
    Private objDataAdapter As OleDbDataAdapter
    Private objDataTable As DataTable
    Public connectionstring As String
    Private Value As Integer = 1
   

    Private Sub Form2_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'TODO: This line of code loads data into the 'DataSet2.DataTable1' table. You can move, or remove it, as needed.




        ReportLoad()
        Me.ReportViewer1.RefreshReport()





        'TODO: This line of code loads data into the 'DataSet2.DataTable1' table. You can move, or remove it, as needed.
       
    End Sub



    Private Sub ReportLoad()
        objConnection = New OleDbConnection(strConnectionString)
        objCommand = New OleDbCommand("SELECT	   STAFF_FNAME, STAFF_LNAME, SKILL_NAME, SKILL_DESC, SKILL_LEVEL, ACTIVE FROM STAFF, SKILL_LEVEL, SKILL, SKILL_ACQ WHERE STAFF.STAFF_ID = SKILL_ACQ.STAFF_ID AND SKILL_LEVEL.SKILL_LEVEL_ID = SKILL_ACQ.SKILL_LEVEL_ID AND	   SKILL.SKILL_ID = SKILL_ACQ.SKILL_ID AND STAFF.STAFF_FNAME = '"+texbox.text.tostring+"' ORDER BY STAFF_FNAME", objConnection)

        'Initialize the DataAdapter object and set the SelectCommand property
        objDataAdapter = New OleDbDataAdapter
        objDataAdapter.SelectCommand = objCommand

        'Initialize the DataTable object
        objDataTable = New DataTable
        Dim DataSet2 As DataSet2.DataTable1DataTable = New DataSet2.DataTable1DataTable
        'Populate the DataTable
        objDataAdapter.Fill(DataSet2)


        Dim ReportDataSource As New ReportDataSource("DataSet2_DataTable1", DataSet2)
        ReportViewer1.LocalReport.DataSources.Clear()
        ReportViewer1.LocalReport.DataSources.Add(ReportDataSource)
        Me.ReportViewer1.RefreshReport()



        ' Me.DataTable1TableAdapter.Fill(Me.DataSet2.DataTable1)

        '  Me.ReportViewer1.RefreshReport()



    End Sub
End Class

Open in new window

0
 
fixitbenAuthor Commented:
Thanks guys. You got me pointed in the right direction.
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.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now