Develop a strong foundation and understanding of web design by learning HTML, CSS, and additional tools to help you develop your own website.
VB.Net & Crystal Reports with subreports table connections
I have been attempting to write a small VB.net (Community version) app to print Crystal report (XI version). The app populates a combo box based on user of available reports from a MySQL db. Almost all of the reports are using ODBC to connect to an AS400 db table(s) to extract data. If the report does not have a sub-report everything works well. If the report does have a sub-report it starts to load the crystal viewer then it pops a log on box asking for username and password. It does not matter what credentials are used it returns log on failed, please try again.
I have tried a lot of different solutions that I have found on the web, but none solve my issue. Here is the current code that is setting up to display the report:
Friend Function ViewReport(ByVal sReportName As String, Optional ByVal sSelectionFormula As String = "", Optional ByVal param As String = "") As Boolean
Dim intCounter As Integer
Dim objReport As New CrystalDecisions.CrystalRe
Dim ConInfo As New CrystalDecisions.Shared.Ta
Dim paraValue As New CrystalDecisions.Shared.Pa
Dim currValue As CrystalDecisions.Shared.Pa
Dim mySubReportObject As CrystalDecisions.CrystalRe
Dim mySubRepDoc As New CrystalDecisions.CrystalRe
Dim crtableLogoninfo As New TableLogOnInfo
Dim crTables As Tables
Dim crTable As Table
Dim crSubTables As Tables
Dim crSubTable As Table
Dim strParValPair() As String
Dim strVal() As String
Dim index As Integer
Try
'Load the report
objReport.Load(sReportName
ConInfo.ConnectionInfo.Use
ConInfo.ConnectionInfo.Pas
ConInfo.ConnectionInfo.Ser
ConInfo.ConnectionInfo.Dat
crTables = objReport.Database.Tables
For Each crTable In crTables
crtableLogoninfo = crTable.LogOnInfo
crtableLogoninfo.Connectio
crTable.ApplyLogOnInfo(crt
Next
intCounter = objReport.DataDefinition.P
If intCounter = 1 Then
If InStr(objReport.DataDefini
intCounter = 0
End If
End If
If intCounter > 0 And Trim(param) <> "" Then
strParValPair = param.Split("&")
For index = 0 To UBound(strParValPair)
If InStr(strParValPair(index)
strVal = strParValPair(index).Split
paraValue.Value = strVal(1)
currValue = _
objReport.DataDefinition.P
currValue.Add(paraValue)
objReport.DataDefinition.P
End If
Next
End If
For index = 0 To objReport.ReportDefinition
For intCounter = 0 To _
objReport.ReportDefinition
With objReport.ReportDefinition
If .ReportObjects(intCounter)
mySubReportObject = CType(.ReportObjects(intCo
mySubRepDoc = mySubReportObject.OpenSubr
crSubTables = mySubRepDoc.Database.Table
For Each crSubTable In crSubTables
crtableLogoninfo = crSubTable.LogOnInfo
crtableLogoninfo.Connectio
crSubTable.ApplyLogOnInfo(
Next
End If
End With
Next
Next
If sSelectionFormula.Length > 0 Then
objReport.RecordSelectionF
End If
CrystalReportViewer1.Repor
CrystalReportViewer1.Repor
CrystalReportViewer1.Show(
Return True
Catch ex As System.Exception
Dim ErrorInfo As String
ErrorInfo = "Exception: " & ex.Message & Environment.NewLine & Environment.NewLine
If ex.InnerException IsNot Nothing Then
ErrorInfo &= "InnerException: " & ex.InnerException.Message
End If
MessageBox.Show(ErrorInfo)
End Try
Return True
End Function
I have tried a lot of different solutions that I have found on the web, but none solve my issue. Here is the current code that is setting up to display the report:
Friend Function ViewReport(ByVal sReportName As String, Optional ByVal sSelectionFormula As String = "", Optional ByVal param As String = "") As Boolean
Dim intCounter As Integer
Dim objReport As New CrystalDecisions.CrystalRe
Dim ConInfo As New CrystalDecisions.Shared.Ta
Dim paraValue As New CrystalDecisions.Shared.Pa
Dim currValue As CrystalDecisions.Shared.Pa
Dim mySubReportObject As CrystalDecisions.CrystalRe
Dim mySubRepDoc As New CrystalDecisions.CrystalRe
Dim crtableLogoninfo As New TableLogOnInfo
Dim crTables As Tables
Dim crTable As Table
Dim crSubTables As Tables
Dim crSubTable As Table
Dim strParValPair() As String
Dim strVal() As String
Dim index As Integer
Try
'Load the report
objReport.Load(sReportName
ConInfo.ConnectionInfo.Use
ConInfo.ConnectionInfo.Pas
ConInfo.ConnectionInfo.Ser
ConInfo.ConnectionInfo.Dat
crTables = objReport.Database.Tables
For Each crTable In crTables
crtableLogoninfo = crTable.LogOnInfo
crtableLogoninfo.Connectio
crTable.ApplyLogOnInfo(crt
Next
intCounter = objReport.DataDefinition.P
If intCounter = 1 Then
If InStr(objReport.DataDefini
intCounter = 0
End If
End If
If intCounter > 0 And Trim(param) <> "" Then
strParValPair = param.Split("&")
For index = 0 To UBound(strParValPair)
If InStr(strParValPair(index)
strVal = strParValPair(index).Split
paraValue.Value = strVal(1)
currValue = _
objReport.DataDefinition.P
currValue.Add(paraValue)
objReport.DataDefinition.P
End If
Next
End If
For index = 0 To objReport.ReportDefinition
For intCounter = 0 To _
objReport.ReportDefinition
With objReport.ReportDefinition
If .ReportObjects(intCounter)
mySubReportObject = CType(.ReportObjects(intCo
mySubRepDoc = mySubReportObject.OpenSubr
crSubTables = mySubRepDoc.Database.Table
For Each crSubTable In crSubTables
crtableLogoninfo = crSubTable.LogOnInfo
crtableLogoninfo.Connectio
crSubTable.ApplyLogOnInfo(
Next
End If
End With
Next
Next
If sSelectionFormula.Length > 0 Then
objReport.RecordSelectionF
End If
CrystalReportViewer1.Repor
CrystalReportViewer1.Repor
CrystalReportViewer1.Show(
Return True
Catch ex As System.Exception
Dim ErrorInfo As String
ErrorInfo = "Exception: " & ex.Message & Environment.NewLine & Environment.NewLine
If ex.InnerException IsNot Nothing Then
ErrorInfo &= "InnerException: " & ex.InnerException.Message
End If
MessageBox.Show(ErrorInfo)
End Try
Return True
End Function
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Subreports always cause issues.
You need code that
finds the subreport
opens it as a report
applies the logon criteria
It is a mirror of your code but there is a loop around it like
For each reportObject in reportobjectset
if objecttype = subreport then
setup logon criteria
end if
end loop
I am not a .Net programmer so I don't have an example.
I'll see if I can find an example from SAP/Crystal.
mlmcc
You need code that
finds the subreport
opens it as a report
applies the logon criteria
It is a mirror of your code but there is a loop around it like
For each reportObject in reportobjectset
if objecttype = subreport then
setup logon criteria
end if
end loop
I am not a .Net programmer so I don't have an example.
I'll see if I can find an example from SAP/Crystal.
mlmcc
Thank you for your response, but I think that the loop around it is there. The top level for index loop cycles through all objects in the report and the for intCounter loop cycles through the sections, then if the object is a subreport then loop for each table in the subReport. But if you have some example that works I am more that happy to give it a shot. I have looked at numerous ways to do this and none have panned out for me.
I didn't see in my initial glance at the code. I see it now.
That is one reason why my recommended method of passing data to a report the push method where the application builds a dataset then passes it to the report.
That moves the database connections to the application where you can handle them.
Works well if you only have a few reports. Not necessarily so good if you have hundreds.
The other way I have avoided this problem was to setup my dev environment as a mirror of the production so even my dev db server was named the same. We used integrated security so the windows login also logged them into the db. COde that worked in dev worked just fine in prod since it didn't see a db change.
I'll see if I can find sample code for this
mlmcc
That is one reason why my recommended method of passing data to a report the push method where the application builds a dataset then passes it to the report.
That moves the database connections to the application where you can handle them.
Works well if you only have a few reports. Not necessarily so good if you have hundreds.
The other way I have avoided this problem was to setup my dev environment as a mirror of the production so even my dev db server was named the same. We used integrated security so the windows login also logged them into the db. COde that worked in dev worked just fine in prod since it didn't see a db change.
I'll see if I can find sample code for this
mlmcc
Sorry that I am not answering your question, but you have long way to go. Are you developing this for fun or to help somebody ? If the goal is to just to run few reports there are some commercial applications with free versions, which will cover you scenario. Here are links to 3 free apps:
http://www.r-tag.com/Pages/CommunityEdition.aspx
http://www.groffautomation.com/
http://www.logicitysuite.com/
They are not just viewers but schedulers too. I guess groffautomation and logicitysuite have some restrictions in the free version , but R-Tag is just a gift with practically all you need to run and schedule your reports for free. Just check them before to spend more time on your project.
http://www.r-tag.com/Pages/CommunityEdition.aspx
http://www.groffautomation.com/
http://www.logicitysuite.com/
They are not just viewers but schedulers too. I guess groffautomation and logicitysuite have some restrictions in the free version , but R-Tag is just a gift with practically all you need to run and schedule your reports for free. Just check them before to spend more time on your project.
Vasto - thanks for the links, and if I have to go down that path I will, but I would prefer to work through the issues to resolve them.
habbibb - I will give this a shot and let you know.
habbibb - I will give this a shot and let you know.
I have figured out what the real issue is. While it is true that any of the reports that have a sub-report failed, that led me down the wrong path (and I led you down the wrong path). I am not the author of the reports, so I was not looking at the reports that close. All of the reports with sub reports were written by someone who decided to use both MS access and DB2, so two different data sources. The thing that is interesting is that all of the data comes ultimately from db2, because access is just used to create views layering views. The report creator thought this was easier to implement. So what this meant is that I was passing the wrong log on info for the access database.
I do not have it working yet, but now that I understand what the real issue is, I am hopeful that I will be able to insert code to detect different data sources and pass the correct log on info.
I do not have it working yet, but now that I understand what the real issue is, I am hopeful that I will be able to insert code to detect different data sources and pass the correct log on info.
Good luck. It may work since you are using subreports for the 2nd data source. Crystal never has really embraced multiple data sources though it does work so long as you don't need to change them through code.
Your method should work so long as each report/subreport only has 1 data source
mlmcc
Your method should work so long as each report/subreport only has 1 data source
mlmcc
This is just an example of a specific scenario. You may also have problems with reports with commands, reports which are using ODBC or OLEDB connections or both , reports , which require to change the server ( not to provide just database name and password) etc. There are many scenarios and each one has specifics. That is why I said you have long way in front of you and it will be wise to try to find an existing working solution
I was able to solve my specific issue. There were a couple of things I did, one of which I was not wild about. First when filling out the TableLogOnInfo object I retrieved the server and database information from the report.
ConInfo.ConnectionInfo.Ser
ConInfo.ConnectionInfo.Dat
and then I also changed the UserID to Admin if it was an access database, but to determine if it was an access I parsed the Server name and checked if the last 4 characters were .mdb. This is the part I really don't like I would have preferred a data source type code or something, but I was not able to find it.
ConInfo.ConnectionInfo.Ser
ConInfo.ConnectionInfo.Dat
and then I also changed the UserID to Admin if it was an access database, but to determine if it was an access I parsed the Server name and checked if the last 4 characters were .mdb. This is the part I really don't like I would have preferred a data source type code or something, but I was not able to find it.
Premium Content
You need an Expert Office subscription to comment.Start Free Trial