Fareed Abdul Hameed Fareed Abdul Hameed - 3 months ago 20
Vb.net Question

Crystal Report prompts for Login when using Sub-Reports through VS 2013

Microsoft SQL Database
VB.NET in Visual Studio 2013

I am currently using a form for launching the crystal reports and it works perfectly fine for reports that has no sub-reports. The code I have used to build it is an adaptation from https://apps.support.sap.com/sap/support/knowledge/public/en/1676673.

Imports CrystalDecisions.Shared
Imports CrystalDecisions.CrystalReports.Engine


Public Class Crystal
Public RPTSRC As String
Public RPTNAME As String


Private Sub configureCRYSTALREPORT()
Dim myConnectionInfo As New ConnectionInfo()
myConnectionInfo.DatabaseName = "DBNAME"
myConnectionInfo.UserID = "CR"
myConnectionInfo.Password = "1234"
setDBLOGONforREPORT(myConnectionInfo)
End Sub


Private Sub setDBLOGONforREPORT(ByVal myconnectioninfo As ConnectionInfo)
Dim mytableloginfos As New TableLogOnInfos()
mytableloginfos = CrystalReportViewer1.LogOnInfo
For Each myTableLogOnInfo As TableLogOnInfo In mytableloginfos
myTableLogOnInfo.ConnectionInfo = myconnectioninfo
Next
End Sub


Private Sub MGM_PT_MNT_Load(sender As Object, e As EventArgs) Handles MyBase.Load
Me.Size = New Size((Screen.PrimaryScreen.Bounds.Width) - 10, (Screen.PrimaryScreen.Bounds.Height) - 35)
Me.Location = New Point((Screen.PrimaryScreen.WorkingArea.Width - Me.Width) / 2, (Screen.PrimaryScreen.WorkingArea.Height - Me.Height) / 2)
Me.Text = RPTNAME
CrystalReportViewer1.ReportSource = RPTSRC
configureCRYSTALREPORT()
Application.DoEvents()
End Sub
End Class


As per my understanding, this only manages to pass the values only to the main report and hence the sub report requests for the credentials.Unfortunately, the KPI report I have developed requires 2 subreports to be a part of the main report and the Management cannot be expected to type the username and password to view this. Furthermore, we are using SQL Authentication only.

Can someone please help me to pass the login details to the SubReports for VB.NET? Any help appreciated .

EDIT:

Thanks to Jonathan, I got the answer to my question and would like to make it easier for others too who are also in search of a similar requirement.

Imports CrystalDecisions.Shared
Imports CrystalDecisions.CrystalReports.Engine

Public Class Crystal
'This is only needed if you would like another form to send the Report Name (RPTNAME) and the Location (RPTSRC). Location can be a Shared UNC Path.
Public RPTSRC As String
Public RPTNAME As String


Private Sub configureCRYSTALREPORT(report As ReportDocument)
Dim myConnectionInfo As New ConnectionInfo()
myConnectionInfo.DatabaseName = "DBNAME"
myConnectionInfo.UserID = "Username"
myConnectionInfo.Password = "Password"
setDBLOGONforREPORT(myConnectionInfo)
configureSubREPORT(report, myConnectionInfo)

End Sub

Private Sub setDBLOGONforREPORT(ByVal myconnectioninfo As ConnectionInfo)
Dim mytableloginfos As New TableLogOnInfos()
mytableloginfos = CrystalReportViewer1.LogOnInfo
For Each myTableLogOnInfo As TableLogOnInfo In mytableloginfos
myTableLogOnInfo.ConnectionInfo = myconnectioninfo
Next
End Sub

Private Sub configureSubREPORT(report As ReportDocument, info As ConnectionInfo)
Dim rD As ReportDocument
Dim table As Table

For Each rD In report.Subreports
For Each table In rD.Database.Tables
table.LogOnInfo.ConnectionInfo = info
table.ApplyLogOnInfo(table.LogOnInfo)
Next
Next
End Sub

Private Sub MGM_PT_MNT_Load(sender As Object, e As EventArgs) Handles MyBase.Load
Me.Size = New Size((Screen.PrimaryScreen.Bounds.Width) - 8, (Screen.PrimaryScreen.Bounds.Height) - 44)
Me.Location = New Point((Screen.PrimaryScreen.WorkingArea.Width - Me.Width) / 2, (Screen.PrimaryScreen.WorkingArea.Height - Me.Height) / 2)
Me.Text = RPTNAME
Dim report = New ReportDocument
report.Load(RPTSRC)
CrystalReportViewer1.ReportSource = report
configureCRYSTALREPORT(report)
'EnableDatabaseLogonPrompt = "false"
Application.DoEvents()
End Sub
End Class

Answer

It is a very long time since I used VB, so please forgive if the syntax is not right, but I do have CR Sub-Reports in c#.

What you need to do, is pass the logoninfo down to all the sub-reports. Something like this:

Change your load sub:

Instead of:

    CrystalReportViewer1.ReportSource = RPTSRC 
    configureCRYSTALREPORT()

Do

    Dim report = New ReportDocument
    report.Load(RPTSRC)
    CrystalReportViewer1.ReportSource = report 
    configureCRYSTALREPORT(report)

Then make sub routine

Private Sub configureSubREPORT(report as ReportDocument, info as ConnectionInfo)
    Dim rD As ReportDocument
    Dim table As Table

    For Each rD In report.Subreports
        For Each table In rD.Database.Tables
            table.LogOnInfo.ConnectionInfo = info
            table.ApplyLogOnInfo(table.LogOnInfo)
        Next
    Next
End Sub

Finally change existing sub as follows:

Private Sub configureCRYSTALREPORT(report AS ReportDocument)
    Dim myConnectionInfo As New ConnectionInfo()
    myConnectionInfo.DatabaseName = "DBNAME"
    myConnectionInfo.UserID = "CR"
    myConnectionInfo.Password = "1234"
    setDBLOGONforREPORT(myConnectionInfo)
    configureSubREPORT(report, myConnectionInfo)

End Sub

That should do it. But as I said before I am very rusty on VB!