Sub-reports not logging to same server as primary 2

[jolamb42]

Help!!!

I use Crystal 8.5 and VB 6.0

I have a report that uses a stored procedure for the main portion. I have about 17 sub-reports that are linked to data from this main stored procedure. Upon creating all of my reports, I follow the standard of doing a "Set Location" and removing any indications of a server name.

ie: From: SERVER_NAME.dbo.Proc(StoredProcedureName)
To: Proc(StoredProcedureName)

I have done this for both the main report AND all of my sub-reports. For those sub-reports that use more than one table, I have done this for EACH table.

My sub-reports are linked properly to the main report.

If I open my report in Crystal and run it, I can enter any parameter information I need to. Then, it has me log onto the server. (Which is a good indication that my "Set Locations" successfully removed any server names.) I log on once and it produces the report beautifully. It works 100% through VB.

My PROBLEM?!?! Now, I try to run my report to access data from a different server. If I do it thru VB, the main portion returns info fine. It's the sub-reports that do not appear correctly! NONE of them return the data they are supposed to.

So, I open the report in Crystal, run it, and enter my parameter values. When it asks me to log into the server, I do so. Then, it asks me to log on 17 more times for each sub-report!!! Why does this do that? When I log onto the server I used to originally create the report, it only asks me once???

Okay, if I do a Verify Database and select the new server and log on 18 times (once for main, 17 for each sub-report) and save my report and THEN use VB, it is FINE! I am completely baffled... Is there a setting I am missing that is supposed to tell the sub-reports to use the same server as the main report?

I have to roll these reports out to 20-30 client sites, each with different server names! It is not a production thing for me to have to do a "Verify Database" for each client each time we roll out new reports...

Please, I hope somebody can help me! Thank you in advance!!!

Jolynn

 

[chelseatech]

Are you using ODBC or a native Oracel or SQL server connection?

From my observation, if you use ODBC, CR makes one conenction to ODBC and then all the tables and subreport use the one connection, while if you sue native connections, a seperate conenction is made for each subreport.

Can have an effect on your Db licenses as well. IDBC seems a bit cleaner here. Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm

 

[jolamb42]

Chelseatech,

We are using OLEDB to connect to our SQL server. Is there some additional VB code I am inadvertantly omitting?? What do I need to do to pass each separate connection for the subreports??

Thanks!

Jolynn

 

[jpc]

Here's my solution to this problem. You have to query the report and determine if it has any subreports. If it does then you must point each subreport to the correct db and proc then logon. Hope this helps.
ps(the rpt reference in a vb class that we use to maintain various and sundry info about a certain report.)

jpc

Private Sub SetupSubReports()
Dim sRptName As String
Dim sRpt As New crpeauto.Report
Dim sRptObjs As crpeauto.ReportObjects
Dim sectsVar As Sections
Dim sectVar As Section
Dim objs As ReportObjects
Dim sRptObj As SubreportObject
Dim idx As Integer
' 'We had a problem with using subreports and passing the database INFO
' 'to each of the subreports. Solution below:
''*****
' 'this will detect if there are any subreports in the report that is assigned
' 'and will assign the database INFO to each of them.
Set sectsVar = mCrystalReport.Sections
For Each sectVar In sectsVar
Set objs = sectVar.ReportObjects
On Error Resume Next
For idx = 1 To objs.Count
If TypeOf objs.Item(idx) Is SubreportObject Then
Set sRptObj = objs.Item(idx)
sRptName = sRptObj.Name
Set sRpt = mCrystalReport.OpenSubreport(sRptName)
LogonReport sRpt
sRpt.ParameterPromptingEnabled = False
End If
Next idx
Next sectVar

End Sub

Private Sub LogonReport(Report As crpeauto.Report)
If Rpt.WinAuth = True Then
Report.Database.Tables(1).SetLogOnInfo Rpt.ServerName _
, Rpt.DatabaseName, &quot;<<Use Integrated Security>>&quot;, &quot;&quot;
'This sets the database location. Same as doing &quot;set location&quot; in crystal
Report.Database.Tables(1).Location = Rpt.DatabaseName & &quot;.dbo.Proc(usp_r_&quot; & Rpt.FileName & &quot;;1)&quot;
Else
Report.Database.Tables(1).SetLogOnInfo Rpt.ServerName _
, Rpt.DatabaseName, &quot;sa&quot;, &quot;&quot;
'This sets the database location. Same as doing &quot;set location&quot; in crystal
Report.Database.Tables(1).Location = Rpt.DatabaseName & &quot;.dbo.Proc(usp_r_&quot; & Rpt.FileName & &quot;;1)&quot;
End If
End Sub

 

[jolamb42]

jpc -

The code was helpful, thanks. I now am logging on any subreports in each section of my report.

BUT... I can't tell you if this works or not because I am getting a new error! A Crystal Reports: Database Error - Column number out of range. Clicking OK on this then brings up two more errors saying Error detected by database dll. Am I now needing an additional dll that I am not already loading??

Here are the dll's we are already installing with our program for Crystal:

crviewer.dll
craxdrt.dll
msvcp60.dll
p2ssql.dll
ntwdlblib.dll

I am really not good at this dll stuff, so any help is greatly appreciated! Thanks!!!

Jolynn

 

[jolamb42]

Okay, never mind the error...

I am back to my problem in my first post. Although I included the code jpc provided and am using SetLogOnInfo to logon the main and all the subreports, it is still not getting the correct data.

Any other ideas???

Jolynn

 

[jolamb42]

jpc -

Stepping through that code, it seems that my Set sRpt = mCrystalReport.OpenSubreport(sRptName) statement is not working. When I query ? sRpt Is Nothing, it returns true.

The following is what code I have adapted according to what you gave above. Did I miss something???

Thanks!
Jolynn

Dim objSections As Sections
Dim objSection As Section
Dim objReportObjs As ReportObjects
Dim objSubreportObj As SubreportObject
Dim lngCount As Long
Dim strSubreportObjName As String
Dim SubReport As CRAXDRT.Report

Set objSections = Report1.Sections
For Each objSection In objSections
Set objReportObjs = objSection.ReportObjects
If objReportObjs.Count <> 0 Then
For lngCount = 1 To objReportObjs.Count
If TypeOf objReportObjs.Item(lngCount) Is SubreportObject Then
Set objSubreportObj = objReportObjs.Item(lngCount)
strSubreportObjName = objSubreportObj.Name
Set SubReport = Report1.OpenSubreport(strSubreportObjName)
SubReport.Database.Tables(1).SetLogOnInfo objTempConn.DataSourceObject.ServerName, objTempConn.DataSourceObject.Database, objTempConn.DataSourceObject.UserId, objTempConn.DataSourceObject.PassWord
End If
Next 'lngCount
End If
Next 'objSection

 

[jpc]

Looking at the code, is &quot;Report1&quot; a valid object?

Then
Set objSubreportObj = objReportObjs.Item(lngCount)
strSubreportObjName = objSubreportObj.Name
setsubReport = Report1.OpenSubreport(strSubreportObjName)
SubReport.Database.Tables(1).SetLogOnInfo


jpc

 

[jolamb42]

jpc -

Yep, Report1 was my CRAXDRT.Report set at form level. (I use this form for all my reports, passing in specific info I need).

The problem I was getting was with:
strSubreportObjName = objSubreportObj.Name

objSubreportObj.Name held a generic name that crystal assigned. i.e. &quot;Subreport1&quot;, &quot;Subreport2&quot;, etc...

I changed the line to:
strSubreportObjName = objSubreportObj.SubreportName

That pulled the actual name I had assigned the subreport when I made it.

And... It works! I was so happy I didn't know whether to jump up and down or cry!

THANK YOU THANK YOU THANK YOU for all of your help!

Jolynn

 

[jpc]

Like my daughter taught me to say Whooo Hoooo!

jpc

 

[onegurl]

Did you ever resolve the &quot;Column number out of range&quot; error?

I am currently running into this error and am not finding any posted documentation on Crystal Decisions or anywhere.

thank you