Crystal subreport with asp.net

Hello,

I am using 2 subreports in a main report. For both the subreports, I am using different tables(one for each). I have to filter the records selected by both the sub reports with some record selection conditions. If I have only one subreport, the first set of following pasted code is working fine. And if I don't have selection criteria , the second section of the pasted code works fine. But, I want the two subreports should work with a selection condition. I am using crystal 9 for reports and asp.net 1 for crystal viewer. How could I do this, please advice.

Thanks
Ceema


---------------Single Sub report with query string-----------------------

 Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
              Dim Da As New SqlDataAdapter
        Dim Ds As New DataSet
        Dim SchoolID As String
        Dim ClassID As String
        SchoolID = "Ab1"
             
        Dim myConnection As SqlConnection
        myConnection = New SqlConnection("server=IB; uid=sa; pwd=1; database=FS")
        Da = New SqlDataAdapter("Select * From TmpForm1A where SchoolID='" & SchoolID & "'", myConnection)
        Da.Fill(Ds)
        repDoc.SetDataSource(Ds.Tables(0))
        CrystalReportViewer1.ReportSource = repDoc

    End Sub





....Code for subreport without query.......................

Dim Da As New SqlDataAdapter
        Dim Ds As New DataSet
        Dim Dai As New SqlDataAdapter
        Dim Dsi As New DataSet
        Dim SchoolID As String
        Dim ClassID As String
        SchoolID = "Ab1"
        ClassID = "KG1-A"
        crDatabase = repDoc.Database
        crTables = crDatabase.Tables

        'Loop through each table and set the connection info
        'Pass the connection info to the logoninfo object then apply the
        'logoninfo to the main report
        For Each crTable In crTables
            With crConnInfo
                .ServerName = "IB"
                .DatabaseName = "FS"
                .UserID = "sa"
                .Password = "1"
            End With
            crLogOnInfo = crTable.LogOnInfo
            crLogOnInfo.ConnectionInfo = crConnInfo
            crTable.ApplyLogOnInfo(crLogOnInfo)

            ' check wheter the connectivity is right or wrong
            If (crTable.TestConnectivity()) Then
                If (crTable.Location.IndexOf(".") > 0) Then
                    crTable.Location = crTable.Location.Substring(crTable.Location.LastIndexOf(".") + 1)
                Else
                   
                    crTable.Location = crTable.Location
                End If

                Response.Write(crTable.Location)
            Else
                Response.Write("Connection failed")
            End If

        Next



        'Set the sections collection with report sections
        crSections = repDoc.ReportDefinition.Sections

        'Loop through each section and find all the report objects
        'Loop through all the report objects to find all subreport objects, then set the
        'logoninfo to the subreport

        For Each crSection In crSections
            crReportObjects = crSection.ReportObjects
            For Each crReportObject In crReportObjects
                If crReportObject.Kind = ReportObjectKind.SubreportObject Then

                    '            'If you find a subreport, typecast the reportobject to a subreport object
                    crSubreportObject = CType(crReportObject, SubreportObject)

                    '            'Open the subreport
                    subRepDoc = crSubreportObject.OpenSubreport(crSubreportObject.SubreportName)
                    Response.Write(crSubreportObject.SubreportName)
                    crDatabase = subRepDoc.Database
                    crTables = crDatabase.Tables

                    '            'Loop through each table and set the connection info
                    '            'Pass the connection info to the logoninfo object then apply the
                    '            'logoninfo to the subreport

                    For Each crTable In crTables
                        With crConnInfo
                            .ServerName = "IB"
                            .DatabaseName = "FS"
                            .UserID = "sa"
                            .Password = "1"
                        End With
                        crLogOnInfo = crTable.LogOnInfo
                        crLogOnInfo.ConnectionInfo = crConnInfo
                        crTable.ApplyLogOnInfo(crLogOnInfo)
                    Next


                    If (crTable.TestConnectivity()) Then
                        If (crTable.Location.IndexOf(".") > 0) Then
                            crTable.Location = crTable.Location.Substring(crTable.Location.LastIndexOf(".") + 1)
                        Else
                            crTable.Location = crTable.Location
                        End If
                       
                        Response.Write(crTable.Location)
                    Else
                        Response.Write("Second Connection failed")
                    End If

                End If
            Next
            Dai.Fill(Dsi)
        Next

       
        CrystalReportViewer1.ReportSource = repDoc

ceemaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dlarlickCommented:

I am not looking at documentation and only an observation based on your code above.

I believe that in example 1 you are explicitly setting the datasource based on the returned dataset.
In the second you are only assigning login information for the subreport to the appropriate object and crystal is performing the login and retrieval of the data set.

Each subreport is it's own report document and int turn has it's own report source.  Your second example you are assigning the login info for each subreport, if you change it to assign the report source, similar to example 1 I believe this would solve your problem.  

Essentially you need to combine the two, retrieve your datasets (plural intended) similar to example1 for each subreport,  loop through like example 2 and "SetDataSource" for each report document/subreport found.  Remove the logininfo.

Good luck

0
ceemaAuthor Commented:
Hello,

   For me, My problem is , If I have to set two different datasets, I have to create two seperate dataadapters, since i have to use different conditions for data retrievel. So, how could I do this, if I can combine these two exmples? Otherwise I think I have to use recordselection formula, but it's giving me error when I am using in my program. Somebody was telling, it's because of the version problem, but I am forced to use some existing reports in my web applications, so I can't change the version and all.

Thanks you,
Ceema
0
dlarlickCommented:

Yes you will need a data table for each subreport,  much the same as you are setting connection info for each report document in your second example.  I do not know of the record selection error you are seeing, but do not doubt that the error may exist.  

I have roughed out a combined example, NOT in working order but should give you an idea of what I would attempt , given the same circumstance.

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
              Dim Da As New SqlDataAdapter
        Dim Ds As New DataSet
        Dim SchoolID As String
        Dim ClassID As String
        SchoolID = "Ab1"
             
        --Get DataTable One
        Dim myConnection As SqlConnection
        myConnection = New SqlConnection("server=IB; uid=sa; pwd=1; database=FS")
        Da = New SqlDataAdapter("Select * From TmpForm1A where SchoolID='" & SchoolID & "'", myConnection)
        Da.Fill(Ds)

        --Get DataTable Two
        {
           Get second data table, your comment on a second data adapter
        }

       --Assign data source for main report document
        repDoc.SetDataSource(Ds.Tables(0))

       --Assign
        For Each crSection In crSections
            crReportObjects = crSection.ReportObjects
            For Each crReportObject In crReportObjects
                If crReportObject.Kind = ReportObjectKind.SubreportObject Then

                    ' 'If you find a subreport, typecast the reportobject to a subreport object
                    crSubreportObject = CType(crReportObject, SubreportObject)

                   --Determine appropriate subreport,  Assign related data table
                   crSubreportObject.SetDataSource(Ds.Tables(1)) -- or something similar


0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

ceemaAuthor Commented:
Hello dlarlick ,

  I will try this logic and let you know.

Thank you
Ceema
0
ceemaAuthor Commented:
Hello,
   
   I have tried the logic and now my code looks as follows. But now it's not checking the condition ,which I have specified with sql query, simply it's retrieving all the datas available from the table. Since I don't have any table in my main report, I haven't created

'--Assign data source for main report document
        repDoc.SetDataSource(Ds.Tables(0))'

And Ds.Tables(0) is ging the error, invalida table number, so I tried 'repDoc.SetDataSource(Ds)'


Do you have any suggestion?

Thanks you so much for the reply.

Ceema



 Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim Da As New SqlDataAdapter
        Dim Ds As New DataSet
        Dim Dai As New SqlDataAdapter
        Dim Dsi As New DataSet
        Dim SchoolID As String
        Dim ClassID As String
        SchoolID = "Ab1"
        crDatabase = repDoc.Database
        crTables = crDatabase.Tables
        Dim da1 As New SqlDataAdapter
        Dim Ds1 As New DataSet
        Dim my1 As SqlConnection
        my1 = FunMyCon()
        da1 = New SqlDataAdapter("Select * From TmpForm7 where TmpForm7.SchoolID='" & SchoolID & "'", my1)
        da1.Fill(Ds1)





        Dim my As SqlConnection
        my = New SqlConnection("server=IBRA; uid=sa; pwd=123; database=FSchool")
        my = FunMyCon()
        Da = New SqlDataAdapter("Select * From TmpForm7A where TmpForm7A.SchoolID='" & SchoolID & "'", my)
        Da.Fill(Ds)


        Dim myConnection As SqlConnection
        For Each crTable In crTables
            With crConnInfo
                .ServerName = "IBRA"
                .DatabaseName = "FSchool"
                .UserID = "sa"
                .Password = "123"
            End With
            crLogOnInfo = crTable.LogOnInfo
            crLogOnInfo.ConnectionInfo = crConnInfo
            crTable.ApplyLogOnInfo(crLogOnInfo)


            If (crTable.TestConnectivity()) Then
                If (crTable.Location.IndexOf(".") > 0) Then
                    crTable.Location = crTable.Location.Substring(crTable.Location.LastIndexOf(".") + 1)
                Else

                    crTable.Location = crTable.Location
                End If


            Else

            End If

        Next

        crSections = repDoc.ReportDefinition.Sections

        For Each crSection In crSections
            crReportObjects = crSection.ReportObjects
            For Each crReportObject In crReportObjects
                If crReportObject.Kind = ReportObjectKind.SubreportObject Then


                    crSubreportObject = CType(crReportObject, SubreportObject)


                    subRepDoc = crSubreportObject.OpenSubreport(crSubreportObject.SubreportName)
                    ' Response.Write(crSubreportObject.SubreportName)
                    'Response.Write("<br>")
                    crDatabase = subRepDoc.Database
                    crTables = crDatabase.Tables
                    For Each crTable In crTables
                        With crConnInfo
                            .ServerName = "IBRA"
                            .DatabaseName = "FSchool"
                            .UserID = "sa"
                            .Password = "123"
                        End With
                        crLogOnInfo = crTable.LogOnInfo
                        crLogOnInfo.ConnectionInfo = crConnInfo
                        crTable.ApplyLogOnInfo(crLogOnInfo)
                        Select Case crSubreportObject.SubreportName
                            Case "Form7DetailsB.rpt"
                                repDoc.SetDataSource(Ds)
                            Case "Form7DetailsA.rpt"
                                repDoc.SetDataSource(Ds)
                        End Select

                    Next
                    If (crTable.TestConnectivity()) Then
                        If (crTable.Location.IndexOf(".") > 0) Then
                            crTable.Location = crTable.Location.Substring(crTable.Location.LastIndexOf(".") + 1)
                        Else
                            crTable.Location = crTable.Location
                        End If
                    Else

                    End If

                End If
            Next
        Next


        CrystalReportViewer1.ReportSource = repDoc



    End Sub
0
dlarlickCommented:

   an example of something like this,  I think you are thinking to hard on this.  In your example one you are setting the data source of the report document.  Essentially all you have to do is find the subreport, which is a reportdocument and then set that data source.  Your attempt is still setting the logininfo.  really not needed if you are explicity obtaining the data tables like example one.

  Maybe something more like the following.    

        Dim Da As New SqlDataAdapter
        Dim Ds As New DataSet
        Dim SchoolID As String
        Dim ClassID As String
        Dim crReportObject As CrystalDecisions.CrystalReports.Engine.ReportObject
        Dim crSubreportObject As CrystalDecisions.CrystalReports.Engine.ReportDocument
        SchoolID = "Ab1"

        Dim myConnection As SqlConnection
        myConnection = New SqlConnection("server=IB; uid=sa; pwd=1; database=FS")
        Da = New SqlDataAdapter("Select * From TmpForm1A where SchoolID='" & SchoolID & "'", myConnection)
        Da.Fill(Ds)

       'Same thing here for second data set

        Dim repDoc As CrystalReports.Engine.ReportDocument
        Dim repDoc2 As TestReport
        Dim subrepDoc As CrystalReports.Engine.ReportDocument
        repDoc = New CrystalReports.Engine.ReportDocument

        repDoc.SetDataSource(Ds.Tables(0))
        CrystalReportViewer1.ReportSource = repDoc

        'Find the sub report for which you want to add
        For Each crReportObject In repDoc.ReportDefinition.ReportObjects
            If crReportObject.Kind = ReportObjectKind.SubreportObject Then

                'If you find a subreport, typecast the reportobject to a subreport object
                crSubreportObject = CType(crReportObject, SubreportObject)

                If crSubreportObject.Name = "test" Then
                    crSubreportObject.SetDataSource(Ds.Tables(1))
                End If

            End If
        Next
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ceemaAuthor Commented:
Hello,

   When I tried with the above logic I am getting logon failure. I have only two subreports in my report and for each of the subreports I am using one table(Example for Subreport 'S1' table T1 and for Subreport 'S2' Table 'T2' and I don't have any table connected to the main report). I have tried to  print the two subreport names and it's showing the subreport names correctly and tables using inside the subreports also getting exacltly. Do you have any idea why it's showing this logon failure?

Thank you
Ceema
0
ceemaAuthor Commented:
Hello,

  I am fed up with this, so may I ask something? If I will pass the value with a record selection formula then that will be good,no? So , I tried it like the following, after defining the formula field like {TmpForm7A.SchoolID}={@SchoolID}. But it's showing no record selection, eventhough there is value for the particular school id I passed into the program. When I tried to print 'FormulaName ', it was giving {@SchoolID}, but the folowing is not functing I think. Any suggection?

subRepDoc.DataDefinition.FormulaFields("SchoolID").Text = "'" & SchoolID & "'"

Thanks
Ceema
My code is as follows...



  Dim Da As New SqlDataAdapter
        Dim Ds As New DataSet
        Dim Dai As New SqlDataAdapter
        Dim Dsi As New DataSet
        Dim SchoolID As String
        Dim ClassID As String
        SchoolID = "Ab1"
        crDatabase = repDoc.Database
        crTables = crDatabase.Tables

       
        Dim myConnection As SqlConnection
        For Each crTable In crTables
            With crConnInfo
                .ServerName = "IBRA"
                .DatabaseName = "FSchool"
                .UserID = "sa"
                .Password = "123"
            End With
            crLogOnInfo = crTable.LogOnInfo
            crLogOnInfo.ConnectionInfo = crConnInfo
            crTable.ApplyLogOnInfo(crLogOnInfo)


            If (crTable.TestConnectivity()) Then
                If (crTable.Location.IndexOf(".") > 0) Then
                    crTable.Location = crTable.Location.Substring(crTable.Location.LastIndexOf(".") + 1)
                Else

                    crTable.Location = crTable.Location
                End If


            Else

            End If

        Next

        crSections = repDoc.ReportDefinition.Sections

        For Each crSection In crSections
            crReportObjects = crSection.ReportObjects
            For Each crReportObject In crReportObjects
                If crReportObject.Kind = ReportObjectKind.SubreportObject Then


                    crSubreportObject = CType(crReportObject, SubreportObject)


                    subRepDoc = crSubreportObject.OpenSubreport(crSubreportObject.SubreportName)
                    crDatabase = subRepDoc.Database
                    crTables = crDatabase.Tables
                    For Each crTable In crTables
                        With crConnInfo
                            .ServerName = "IBRA"
                            .DatabaseName = "FSchool"
                            .UserID = "sa"
                            .Password = "123"
                        End With
                        crLogOnInfo = crTable.LogOnInfo
                        crLogOnInfo.ConnectionInfo = crConnInfo
                        crTable.ApplyLogOnInfo(crLogOnInfo)
                        Dim thisFormulaField As CrystalDecisions.CrystalReports.Engine.FormulaFieldDefinition
                            Select Case crSubreportObject.SubreportName
                                Case "Form7DetailsB.rpt"

                                '********Tried to pass parameter value like this
                                'subRepDoc.DataDefinition.FormulaFields("SchoolID").Text = "'" & SchoolID & "'"
'tried to print number of formulas
                                'Response.Write(repDoc.DataDefinition.FormulaFields.Count)


                                For Each thisFormulaField In repDoc.DataDefinition.FormulaFields
                                    ' Response.Write(thisFormulaField.FormulaName)

                                    If thisFormulaField.FormulaName = "{@SchoolID}" Then
                                        subRepDoc.DataDefinition.FormulaFields("SchoolID").Text = "'" & SchoolID & "'"
                                        Response.Write(repDoc.DataDefinition.FormulaFields.Count)
                                    End If
                                Next
                                Response.Write("<br>")

                                Case "Form7DetailsA.rpt"
                                 

                                For Each thisFormulaField In repDoc.DataDefinition.FormulaFields
                                   
                                    If thisFormulaField.FormulaName =  "{@SchoolID}" Then
                                        subRepDoc.DataDefinition.FormulaFields("SchoolID").Text = "'" & SchoolID & "'"
                                        Response.Write(repDoc.DataDefinition.FormulaFields.Count)
                                    End If
                                Next

                            End Select
                        Next
                        If (crTable.TestConnectivity()) Then
                            If (crTable.Location.IndexOf(".") > 0) Then
                                crTable.Location = crTable.Location.Substring(crTable.Location.LastIndexOf(".") + 1)
                            Else
                                crTable.Location = crTable.Location
                            End If
                        Else

                        End If

                End If
            Next
        Next

       

        CrystalReportViewer1.ReportSource = repDoc



    End Sub
    Function FunMyCon() As SqlConnection
        Dim _ConStr As String
        Dim _Conn As SqlConnection
        _ConStr = ConfigurationSettings.AppSettings("MyDB")
        _Conn = New SqlConnection(_ConStr)
        _Conn.Open()
        Return _Conn
    End Function
0
ceemaAuthor Commented:
Hello,

  I have got the solution by passing the record selection formmula, I used it like this

subRepDoc.DataDefinition.FormulaFields.Item("SchoolID").Text = "'ab0'"


Entire code I am pasing, some one may find it useful later... And dlarlick
 helped me a lot, so I am giving the points to him/her......

Thanks
Ceema


Imports System.Data
Imports CrystalDecisions.CrystalReports.Engine
Imports CrystalDecisions.Shared
Imports System.Data.SqlClient
Imports CrystalDecisions.Web.Design
Public Class testreport
    Inherits System.Web.UI.Page

#Region " Web Form Designer Generated Code "

    'This call is required by the Web Form Designer.
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()

    End Sub
    Protected WithEvents CrystalReportViewer1 As CrystalDecisions.Web.CrystalReportViewer
    Dim crSections As Sections
    Dim crSection As Section
    Dim crReportObjects As ReportObjects
    Dim crReportObject As ReportObject
    Dim crSubreportObject As SubreportObject
    Dim crDatabase As Database
    Dim crTables As Tables
    Dim crTable As Table
    Dim crLogOnInfo As TableLogOnInfo
    Dim crConnInfo As New ConnectionInfo

    Dim repDoc As New Form7
    Dim subRepDoc As New ReportDocument
    'NOTE: The following placeholder declaration is required by the Web Form Designer.
    'Do not delete or move it.
    Private designerPlaceholderDeclaration As System.Object

    Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init
        'CODEGEN: This method call is required by the Web Form Designer
        'Do not modify it using the code editor.
        InitializeComponent()
    End Sub

#End Region

    Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim Da As New SqlDataAdapter
        Dim Ds As New DataSet
        Dim Dai As New SqlDataAdapter
        Dim Dsi As New DataSet
        Dim SchoolID As String
        ' SchoolID = Session("SchoolID")
        Dim ClassID As String
        ' ClassID = Session("ClassID")
        SchoolID = "'Ab0'"
        crDatabase = repDoc.Database
        crTables = crDatabase.Tables

       
        Dim myConnection As SqlConnection
        For Each crTable In crTables
            With crConnInfo
                .ServerName = "IBRA"
                .DatabaseName = "FSchool"
                .UserID = "sa"
                .Password = "123"
            End With
            crLogOnInfo = crTable.LogOnInfo
            crLogOnInfo.ConnectionInfo = crConnInfo
            crTable.ApplyLogOnInfo(crLogOnInfo)


            If (crTable.TestConnectivity()) Then
                If (crTable.Location.IndexOf(".") > 0) Then
                    crTable.Location = crTable.Location.Substring(crTable.Location.LastIndexOf(".") + 1)
                Else

                    crTable.Location = crTable.Location
                End If


            Else

            End If

        Next

        crSections = repDoc.ReportDefinition.Sections

        For Each crSection In crSections
            crReportObjects = crSection.ReportObjects
            For Each crReportObject In crReportObjects
                If crReportObject.Kind = ReportObjectKind.SubreportObject Then


                    crSubreportObject = CType(crReportObject, SubreportObject)


                    subRepDoc = crSubreportObject.OpenSubreport(crSubreportObject.SubreportName)
                    ' Response.Write(crSubreportObject.SubreportName)
                    'Response.Write("<br>")
                    crDatabase = subRepDoc.Database
                    crTables = crDatabase.Tables
                    For Each crTable In crTables
                        With crConnInfo
                            .ServerName = "IBRA"
                            .DatabaseName = "FSchool"
                            .UserID = "sa"
                            .Password = "123"
                        End With
                        crLogOnInfo = crTable.LogOnInfo
                        crLogOnInfo.ConnectionInfo = crConnInfo
                        crTable.ApplyLogOnInfo(crLogOnInfo)
                    Next
                    If (crTable.TestConnectivity()) Then
                        If (crTable.Location.IndexOf(".") > 0) Then
                            crTable.Location = crTable.Location.Substring(crTable.Location.LastIndexOf(".") + 1)
                        Else
                            crTable.Location = crTable.Location
                        End If
                    Else

                    End If

                     subRepDoc.DataDefinition.FormulaFields.Item("SchoolID").Text = "'ab0'"
                   ' subRepDoc.DataDefinition.FormulaFields.Item("SchoolID").Text = """ & SchoolID & """
                End If
            Next
        Next

       

        CrystalReportViewer1.ReportSource = repDoc



    End Sub
    Function FunMyCon() As SqlConnection
        Dim _ConStr As String
        Dim _Conn As SqlConnection
        _ConStr = ConfigurationSettings.AppSettings("MyDB")
        _Conn = New SqlConnection(_ConStr)
        _Conn.Open()
        Return _Conn
    End Function


End Class

0
ceemaAuthor Commented:
Hello,

   After finishing all these cases, I have one more problem, if I will use the code

 subRepDoc.DataDefinition.FormulaFields.Item("SchoolID").Text = "'ab0'" for passing the record formula, it's working fine.

But if I will assing like

 subRepDoc.DataDefinition.FormulaFields.Item("SchoolID").Text = "'" & session("SchoolID") & "'" it's not giving the result. Do you have any suggestion?

Thanks
Ceema
0
ceemaAuthor Commented:
Hi,

  I have got the reason, it was because if was searching for case sensitive data. I was passing Ab0 and database was having ab0 as data.

Thank you
Ceema
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.