Sub reports in Web application - using Crystal 8.5

[bduke]

How do you set up the connection to the sub report from the ASP page?

 

[FVTrainer]

Umm...that depends on what kind of connectivity you are using. Are you using an ODBC connection? OLEDB? ADO recordset? And, just as a point of reference, how are you setting up the connection for the main report?

 

[bduke]

This is the section that I was referring too. sorry for the confusion.


From Menu/Navigational Bar on Web page you will have this code:

<td colspan=&quot;21&quot; width=&quot;525&quot;>
<A class=&quot;menul1&quot; href=&quot;Reports.asp?go=PositionRequest&Name=<%=Name%>&status=<%=status%>&quot; target=&quot;_blank&quot;>Test Page</A>
</TD>



Reports.asp


<%@ LANGUAGE=&quot;VBSCRIPT&quot; %>
<!--#include file=&quot;../common/db.asp&quot;-->
<!--#include file=&quot;../common/format.asp&quot;-->

<%
response.expires = 0
Server.ScriptTimeout = 3600
IF request.querystring(&quot;go&quot <> &quot;&quot; Then
session(&quot;report&quot = request.querystring(&quot;go&quot
End If
'Open Connection to Database
reportname = &quot;reports\&quot; & session(&quot;report&quot & &quot;.rpt&quot;

' CREATE THE APPLICATION OBJECT
If Not IsObject (session(&quot;oApp&quot) Then
Set session(&quot;oApp&quot = Server.CreateObject(&quot;Crystalruntime.Application&quot
End If

Path = Request.ServerVariables(&quot;PATH_TRANSLATED&quot
While (Right(Path, 1) <> &quot;\&quot; And Len(Path) <> 0)
iLen = Len(Path) - 1
Path = Left(Path, iLen)
Wend
'response.write &quot;first thru &quot; & path & &quot; &quot;
path = left(path, (len(path) - 1))
While (Right(Path, 1) <> &quot;\&quot; And Len(Path) <> 0)
iLen = Len(Path) - 1
Path = Left(Path, iLen)
Wend
'OPEN THE REPORT (but destroy any previous one first)

If IsObject(session(&quot;oRpt&quot) then
Set session(&quot;oRpt&quot = nothing
End if

'Response.Write path & reportname
On error resume next

Set session(&quot;oRpt&quot = session(&quot;oApp&quot.OpenReport(path & reportname, 1)
'This line uses the &quot;PATH&quot; and &quot;reportname&quot; variables to reference the Crystal
'Report file, and open it up for processing.

If Err.Number <> 0 Then
response.write &quot;report path &quot; & path & reportname & &quot; &quot;
Response.Write &quot;Error Occurred creating Report Object: &quot; & Err.Description & err.number
Set Session(&quot;oRpt&quot = nothing
Set Session(&quot;oApp&quot = nothing
Session.Abandon
Response.End
End If


'This On error resume next block checks for any errors on creating the report object
'we are specifically trapping for an error if we try to surpass the maximum concurrent
'users defined by the license agreement. By destroying the application and report objects
'on failure we ensure that this client session will be able to run reports when a license is free
'
'Notice that we do not create the report object only once. This is because
'within an ASP session, you may want to process more than one report. The
'rptserver.asp component will only process a report object named session(&quot;oRpt&quot.
'Therefor, if you wish to process more than one report in an ASP session, you
'must open that report by creating a new session(&quot;oRpt&quot object.

session(&quot;oRpt&quot.MorePrintEngineErrorMessages = False
session(&quot;oRpt&quot.EnableParameterPrompting = False

'These lines disable the Error reporting mechanism included the built into the
'Crystal Report Design Component automation server (craxdrt.dll).
'This is done for two reasons:
'
'1. The print engine is executed on the Web Server, so any error messages
' will be displayed there. If an error is reported on the web server, the
' print engine will stop processing and you application will &quot;hang&quot;.
'
'2. This ASP page and rptserver.asp have some error handling logic desinged
' to trap any non-fatal errors (such as failed database connectivity) and
' display them to the client browser.
'
'**IMPORTANT** Even though we disable the extended error messaging of the engine
'fatal errors can cause an error dialog to be displayed on the Web Server machine.
'For this reason we reccomend that you set the &quot;Allow Service to Interact with Desktop&quot;
'option on the &quot;World Wide Web Publishing&quot; service (IIS service). That way if your ASP
'application freezes you will be able to view the error dialog (if one is displayed).

'======================================================================================
'======================================================================================

session(&quot;oRpt&quot.DiscardSavedData
set crtable = session(&quot;oRpt&quot.Database.Tables.Item(1)
crtable.SetLogonInfo &quot;dsn&quot;, &quot;server name&quot;, &quot;password&quot;, &quot;userid&quot;
'response.write crtable.testconnectivity
‘select case is for multiple reports… and the parameter fields if you don’t have any don’t worry about the ‘select section
select case session(&quot;report&quot
case &quot;xxx&quot;,&quot;xxx1&quot;

Session(&quot;oRpt&quot.ParameterFields.GetItemByName(&quot;@Name&quot.AddCurrentValue(cstr(request.querystring(&quot;Name&quot))

case &quot;xxx3&quot;
Session(&quot;oRpt&quot.ParameterFields.GetItemByName(&quot;@status&quot.AddCurrentValue(cstr(request.querystring(&quot;status1&quot))

end select


'====================================================================================
' Retrieve the Records and Create the &quot;Page on Demand&quot; Engine Object
'====================================================================================

On Error Resume Next
session(&quot;oRpt&quot.ReadRecords

If Err.Number <> 0 Then
response.write session(&quot;report&quot & &quot; Date &quot; & cdate(vdate) & &quot; &quot;
response.write &quot;connectivity &quot; & crtable.testconnectivity & &quot; &quot;
response.write crtable.SetLogonInfo
Response.Write &quot;Error Occurred Reading Records: &quot; & Err.Description & err.number
Set Session(&quot;oRpt&quot = nothing
Set Session(&quot;oApp&quot = nothing
Session.Abandon
Response.End
Else
If IsObject(session(&quot;oPageEngine&quot) Then
set session(&quot;oPageEngine&quot = nothing
End If
set session(&quot;oPageEngine&quot = session(&quot;oRpt&quot.PageEngine
End If
'There are other viewer that can be used but the activex allows us let the user print the report out.
%>
<!-- #include file=&quot;../common/crystal/SmartViewerActiveX.asp&quot; -->

<title>this is a example of the crystal setup.</title>

 

[FVTrainer]

Here is some sample code. It assumes a subreport called &quot;Titles&quot;.

Set subReportOne = session(&quot;oRpt&quot.OpenSubReport(&quot;Titles&quot

Set subReportOneTablesCollection = subReportOne.Database.Tables


For Each crTable in SubReportOneTablesCollection
crtable.SetLogonInfo &quot;dsn&quot;, &quot;server name&quot;, &quot;password&quot;, &quot;userid&quot;
Next


Place this code before the section starting with comment block:

'====================================================================================
' Retrieve the Records and Create the &quot;Page on Demand&quot; Engine Object
'====================================================================================

If you need a more generic approach (in the event you don't want to hardcode the subreport name), you can iterate through the report objects, looking for objects of the kind crSubreportObject.

The following is some vb code that shows how to do that. You should be able to adapt it for your asp application if necessary:

Dim subReport As SubreportObject
Dim sect As Section
Dim rptObject As Object
For Each sect In Report.Sections
For Each rptObject In sect.ReportObjects
If rptObject.Kind = crSubreportObject Then
Set subReport = rptObject
'Do your login code here

End If
Next
Next

 

[bduke]

The weird thing with this problem it works in Crystal but not on the ASP page and if I take out the checks for the parameters being passed it works - why does it do that.

 

[FVTrainer]

Well, as for why you don't get the same behavior when designing the report, the Crystal Designer automatically passes the logon info to the subreport if the logon info is the same as the main report. The RDC, which your asp pages are using, doesn't do that (it doesn't do it in vb, either).

As for the 2nd part, about the parameters, without seeing how you are using the parameters and what, if any, relationship they have to the subreport, I really can't answer. However, I really don't see how the subreports could work at all without using SetLogOnInfo, unless your database doesn't require authentication (or, if SQL Server, is using NT Authentication).