This will probably sound really, really lame and simple...
I am in a new job, and took over a project from the guy that used to be here. Anyway, I've never used Crystal Reports before, and have studied it for a day or so.
I have an ASP page that is calling a report. The report is hitting a database and pulling all recods that match the query. I designed the report and see it working, but hard-coded the search perameters. If I redirect to the ASP page calling the report, and pass a variable in the querystring, how do I get that perameter in the report's sql?
Many thanks for any help given.
I try to avoid crystal if possible, but I do have to use it in my current project. However my approach is different.
Set the report up to use an "active file" define the field/report contects such as
you'll need to redefine your report source and fields
Then in one asp, select your results and store them in a session variable
set session("oRs") = Recordset
'===================================================================================
'Create the Crystal Reports Objects
'===================================================================================
' CREATE THE APPLICATION OBJECT
If Not IsObject (session("oApp")) Then
Set session("oApp") = Server.CreateObject("CrystalRuntime.Application")
End If
' CREATE THE REPORT OBJECT
'
'The Report object is created by calling the Application object's OpenReport method.
'This "While/Wend" loop is used to determine the physical path (eg: C:\) to the
'Crystal Report file by translating the URL virtual path (eg: http://Domain/Dir)
'OPEN THE REPORT (but destroy any previous one first)
If IsObject(session("oRpt")) then
Set session("oRpt") = nothing
End if
Set session("oRpt") = session("oApp").OpenReport(path & reportname, 1)
'Now we must tell the report to report off of the data in the ADO recordset:
'To base a report on data from a dynamically generated ADO recordset, we must
'build the report based on the data structure of the recordset we will create.
'Then at runtime, we tell the report to report off of the data in the ADO Record set.
'The report is currently created against a database structure file (ADORecordset.ttx)
'This ttx file contains the structure of the recordset, and not the actual data.
'A Crystal Report is completely dependant on the structure of the dataset the report
'will use. Therefor it is important that your database structure (ttx file) or DSN
'reflects EXACTLY the data that is contained in the ADO recordset at runtime.
session("oRpt").DiscardSavedData
set Database = session("oRpt").Database
'Instantiates a database collection which references the database(s) used in the report.
set Tables = Database.Tables
'Instantiates a Tables collection which references the Tables of the Database object.
set Table1 = Tables.Item(1)
'Instantiates a table object which references the first table used in the report.
'In this case this table object currently refers to the ADORecordset.ttx file.
Table1.SetPrivateData 3, session("oRs")
'The "SetPrivateData" line tells the report that it datasource is now the recordset
'Now the report will display the data contained in the session("oRs") record set.
'If your report contained a subreport that was based off this or a different reordset
'you must follow the same steps above only referencing the subreport object.
'
'
'====================================================================================
' Retrieve the Records and Create the "Page on Demand" Engine Object
'====================================================================================
'On Error Resume Next
session("oRpt").ReadRecords
If Err.Number <> 0 Then
Response.Write "An Error has occured on the server in attempting to access the data source"
Else
If IsObject(session("oPageEngine")) Then
set session("oPageEngine") = nothing
End If
set session("oPageEngine") = session("oRpt").PageEngine
End If
' INSTANTIATE THE CRYSTAL REPORTS SMART VIEWER
'
'When using the Crystal Reports automation server in an ASP environment, we use
'the same page on demand "Smart Viewers" used with the Crystal Web Report Server.
'The are four Crystal Reports Smart Viewers:
'
'1. ActiveX Smart Viewer
'2. Java Smart Viewer
'3. HTML Frame Smart Viewer
'4. HTML Page Smart Viewer
'
'The Smart Viewer that you use will based on the browser's display capablities.
'For Example, you would not want to instantiate the Java viewer if the browser
'did not support Java applets. For purposes on this demo, we have chosen to
'define a viewer. You can through code determine the support capabilities of
'the requesting browser. However that functionality is inherent in the Crystal
'Reports automation server and is beyond the scope of this demonstration app.
'
'We have chosen to leverage the server side include functionality of ASP
'for simplicity sake. So you can use the SmartViewer*.asp files to instantiate
'the smart viewer that you wish to send to the browser. Simply replace the line
'below with the Smart Viewer asp file you wish to use.
'
'The choices are SmartViewerActiveX.asp, SmartViewerJave.asp,
'SmartViewerHTMLFrame.asp, and SmartViewerHTMLPAge.asp.
'Note that to use this include you must have the appropriate .asp file in the
'same virtual directory as the main ASP page.
'
'*NOTE* For SmartViewerHTMLFrame and SmartViewerHTMLPage, you must also have
'the files framepage.asp and toolbar.asp in your virtual directory.
'Pass in dynamic values (titles etc)
'Title
'session("oRpt").formulafields.item(1).text = "'All Years for Contract " & cover & " Report for " & usertext & "'"
<script LANGUAGE="VBScript">
<!--
Sub Page_Initialize
On Error Resume Next
Dim webBroker
Set webBroker = CreateObject("WebReportBroker.WebReportBroker")
if ScriptEngineMajorVersion < 2 then
window.alert "IE 3.02 users on NT4 need to get the latest version of VBScript or install IE 4.01 SP1. IE 3.02 users on Win95 need DCOM95 and latest version of VBScript, or install IE 4.01 SP1. These files are available at Microsoft's web site."
CRViewer.ReportName = Location.Protocol + "//" + Location.Host + "/scrsamples/Web Component Server/rptserver.asp"
else
Dim webSource
Set webSource = CreateObject("WebReportSource.WebReportSource")
webSource.ReportSource = webBroker
webSource.URL = "rptserver.asp" 'Location.Protocol + "//" + location.Host + "/scrsamples/Web Component Server/rptserver.asp"
webSource.PromptOnRefresh = True
CRViewer.ReportSource = webSource
end if
CRViewer.EnableExportButton = true
CRViewer.EnableRefreshButton = false
CRViewer.ViewReport
<%if Session("ReportDetail") = "Total" then
Response.write("CRViewer.Zoom(90)" & chr(13))
Response.write("CRViewer.DisplayGroupTree = false")
else
Response.write("CRViewer.Zoom(75)" & chr(13))
Response.write("CRViewer.DisplayGroupTree = True")
end if%>
End Sub
-->
</script>
This was mostly taken from the samples folder and adapted quite quickly, once it work, we left it :)
Thanks for the reply. I actually considered using a .ttx file, but ended up defining my querystring in the asp page before I launched the report. Worked like a charm.
I do have one more question, though, if you have any idea how to do it:
I need to be able to display some graphics on the report prior to printing it, but then not print them....only print the values of the fields. I'm using it to print data onto a pre-printed letterhead, so I don't need to display the header.
--- or ---
As an alternative, is there a way to automatically start the activexviewer printing as soon as it launches? Or at least to launch the print dialog box?
Many thanks for any help given.
hmm, can't say I've ever had a reason to look :)
if it is possible there should be a print method of some description on the view object.
If I get a chance i'll look into it.
TO dc_OnTheEdge:
Hi, I tried to use your example to show a report in my ASP page.
But it just show me a white page, and the activeX is empty (I can see only the border).
What could be the reason?
Thanks in advance
aletheia2, make sure you don't have a response.end anywhere tucked into the code.
I'd be guessing, though, that you're encountering the same issue I used to have with the ActiveX viewer. I think the computer viewing the report thinks that it already has the viewer installed, so doesn't get prompted to download a new one.
Try changing the line that defines the viewer, to use the SmartViewerJava. That seemed to work the best for me, and it gets prompted to download the viewer.
Hi, Keithb.
I don't have any Response.End in my files.
On the server I have the ActiveXViewer.cab file, the Report file and the ASP files. Do I need something else?
If I want to use SmartViewerJava, what do I need? Another .cab file?
Thanks for your help
Just a thought...I personally have made the mistake of having an errant response.end buried in the code....
Anyway, all I did was make sure that the smartviewerjava.asp file was in the same directory as my report, and then I changed the include statement to the java viewer.
Of course, the developer I replaced had already installed Crystal on the web server, and had done some development with it, but I don't think he did anything special to install the java stuff.
Hi, I've tried the JavaViewer and now the applet appears on the ASP page...
... but still the report is not shown!
This is the code of my ASP page, can you find some mistake?
<%@ LANGUAGE="VBSCRIPT" %>
<%
StrConnection = "DSN=miao;UID=miao;PWD=miao;"
Set dbConnection = Server.CreateObject("ADODB.Connection")
dbConnection.Open StrConnection
strSQL = "SELECT * FROM rtf_faxinv WHERE mrm='s'"
Set rs = dbConnection.Execute(strSQL)
Set Session("oRs") = rs
'CREATE THE APPLICATION OBJECT
If Not IsObject (Session("oApp")) Then
Set Session("oApp") = Server.CreateObject("CrystalRuntime.Application")
End If
'CREATE THE REPORT OBJECT
Path = Request.ServerVariables("PATH_TRANSLATED")
While (Right(Path, 1) <> "\" And Len(Path) <> 0)
iLen = Len(Path) - 1
Path = Left(Path, iLen)
Wend
'OPEN THE REPORT
If IsObject(Session("oRpt")) Then
Set Session("oRpt") = Nothing
End if
Set Session("oRpt") = Session("oApp").OpenReport(Path & "rptFaxInviati.rpt", 1)
Set Database = Session("oRpt").Database
Set Tables = Database.Tables
Set Table1 = Tables.Item(1)
Table1.SetPrivateData 3, session("oRs")
On Error Resume Next
Session("oRpt").ReadRecords
If Err.Number <> 0 Then
Response.Write "An Error has occured on the server in attempting to access the data source"
Else
If IsObject(Session("oPageEngine")) Then
Set Session("oPageEngine") = Nothing
End If
Set Session("oPageEngine") = Session("oRpt").PageEngine
End If
Let me look at it. That looks like the type of thing that would happen when it is not pulling anything from the database. No recordset is returned, so it just gives a blank page. No error exists, so no warning message--but no recordset either.
Give me some time and I"ll look thru it closer.
Thank you very much!
Please notice that the static text doesn't appear, neither!
Take a look at the attach file.
Thanks
even i was getting the same kind of problem,
u can try out the link given below, this link leads to files which u can use and compare your code to see if everything is alright