Passing parameters to a crystal report
Passing parameters to a crystal report
(OP)
This might be an easy one but I am not very familiar with Crystal. I have copied a report to from one server to another and am trying to open from a web page using asp. The report accesses a MS Access database to pull out information. It passes an order number from the system to the report which pulls up information for that particular order.
The current system uses VB to pass the Order Num onto the report. So basically I know that the report accepts a parameter. But if I run the report directly from the design view it does not explicitly ask me for the order num, just opens up a blank report with the design. The 'Show SQL Query' under database in the report is disabled so I cannot view it. My question is how do I find out where the parameter has been set inside of the report? I have checked 'Selection Expert' and there is nothing there. Any help here will be really appreciated. THANKS in advance!!
The current system uses VB to pass the Order Num onto the report. So basically I know that the report accepts a parameter. But if I run the report directly from the design view it does not explicitly ask me for the order num, just opens up a blank report with the design. The 'Show SQL Query' under database in the report is disabled so I cannot view it. My question is how do I find out where the parameter has been set inside of the report? I have checked 'Selection Expert' and there is nothing there. Any help here will be really appreciated. THANKS in advance!!
RE: Passing parameters to a crystal report
Check to see if the parameter exists (Insert->Field Object->Check Parameters), and don't worry over the Show SQL, that doesn't guarantee anything regarding parameters.
The Selection Expert is a toy, to check the record selection formula, use Report->Edit Selection Formula, and check the Record and the Group options.
Now you know how to tell IF a parameter is set in the report, and WHERE it would be.
Do you have a task, or was this just for informational purposes?
-k
RE: Passing parameters to a crystal report
Dim cnn
Dim rs
Dim strSQL
Dim Soeno
Set cnn = Server.CreateObject("ADODB.Connection")
sDataDir = Server.MapPath ("oms\oms32\reports.mdb")
cnn.Open "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=" & sDataDir & ";"
Set rs = Server.CreateObject("ADODB.Recordset")
strSQL = "select * from soa_header where soeno='S12345'"
rs.Open strSQL, cnn
Do while not rs.EOF
Soeno = rs("soeno")
'Response.Write(Soeno)
rs.MoveNext
Loop
rs.Close
set rs = Nothing
Set cnn = Nothing
This line actaully passes the Order Number over to the runreport.asp page.....soa.rpt is the report in question. How do I pass the Soeno from this page to the runrepot.asp page?. Is it like this....
menu[2][1] = new Item('Print Order', 'RunReport.asp?ReportName=soa.rpt?Soeno<%=rs("soeno")%>', '', defLength, 0, 0);
But the above is Javascript code...how do I embed asp in that? I am not sure you know asp too well, but if u do it would be great if you can help!
The runreport.asp code is as follows:
<%@ LANGUAGE="VBSCRIPT" %>
<!-- #include file="SmartViewerActiveX.asp" -->
<%
reportname = Request.QueryString("ReportName")
BeginDate = Request.QueryString("BeginDate")
EndDate = Request.QueryString("EndDate")
Soeno = Request.QueryString("Soeno")
'reportname = "DailyDealerReport.rpt"
' 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 (but destroy any previous one first)
If IsObject(session("oRpt")) then
Set session("oRpt") = nothing
End if
' rpttoview = "D:\Webdata\Release\oms2003\soa.rpt"
' Set session("oRpt") = session("oApp").OpenReport(rpttoview,1)
set session("oRpt") = session("oApp").OpenReport(path & reportname,1)
session("oRpt").MorePrintEngineErrorMessages = False
session("oRpt").EnableParameterPrompting = False
session("oRpt").DiscardSavedData
'check for parameters
select case reportname
case "DailyDealerReport.rpt"
set session("ParamCollection") = Session("oRpt").Parameterfields
set Param1 = session("ParamCollection").Item(1)
Call Param1.SetCurrentValue (cstr(BeginDate),12)
case "DailyDealerReportControl.rpt"
set session("ParamCollection") = Session("oRpt").Parameterfields
set Param1 = session("ParamCollection").Item(1)
Call Param1.SetCurrentValue (cstr(BeginDate),12)
case "DailyDealerLabels.rpt"
set session("ParamCollection") = Session("oRpt").Parameterfields
set Param1 = session("ParamCollection").Item(1)
Call Param1.SetCurrentValue (cstr(BeginDate),12)
case "WeeklyManufacturerReportSpa.rpt"
set session("ParamCollection") = Session("oRpt").Parameterfields
set Param1 = session("ParamCollection").Item(1)
Call Param1.SetCurrentValue (cstr(BeginDate),12)
set Param2 = session("ParamCollection").Item(2)
Call Param2.SetCurrentValue (cstr(EndDate),12)
set Param3 = session("ParamCollection").Item(3)
Call Param3.SetCurrentValue (cstr("S"), 12)
set Param4 = session("ParamCollection").Item(4)
Call Param4.SetCurrentValue (cstr("%"), 12)
case "WeeklyManufacturerReportSpaControl.rpt"
set session("ParamCollection") = Session("oRpt").Parameterfields
set Param1 = session("ParamCollection").Item(1)
Call Param1.SetCurrentValue (cstr(BeginDate),12)
set Param2 = session("ParamCollection").Item(2)
Call Param2.SetCurrentValue (cstr(EndDate),12)
set Param3 = session("ParamCollection").Item(3)
Call Param3.SetCurrentValue (cstr("S"), 12)
set Param4 = session("ParamCollection").Item(4)
Call Param4.SetCurrentValue (cstr("%"), 12)
case "WeeklyManufacturerReportBath.rpt"
set session("ParamCollection") = Session("oRpt").Parameterfields
set Param1 = session("ParamCollection").Item(1)
Call Param1.SetCurrentValue (cstr(BeginDate),12)
set Param2 = session("ParamCollection").Item(2)
Call Param2.SetCurrentValue (cstr(EndDate),12)
set Param3 = session("ParamCollection").Item(3)
Call Param3.SetCurrentValue (cstr("B"), 12)
set Param4 = session("ParamCollection").Item(4)
Call Param4.SetCurrentValue (cstr("%"), 12)
case "WeeklyManufacturerReportCanada.rpt"
set session("ParamCollection") = Session("oRpt").Parameterfields
set Param1 = session("ParamCollection").Item(1)
Call Param1.SetCurrentValue (cstr(BeginDate),12)
set Param2 = session("ParamCollection").Item(2)
Call Param2.SetCurrentValue (cstr(EndDate),12)
set Param3 = session("ParamCollection").Item(3)
Call Param3.SetCurrentValue (cstr("S"), 12)
set Param4 = session("ParamCollection").Item(4)
Call Param4.SetCurrentValue (cstr("C"), 12)
case "WeeklyManufacturerReportCanadaBath.rpt"
set session("ParamCollection") = Session("oRpt").Parameterfields
set Param1 = session("ParamCollection").Item(1)
Call Param1.SetCurrentValue (cstr(BeginDate),12)
set Param2 = session("ParamCollection").Item(2)
Call Param2.SetCurrentValue (cstr(EndDate),12)
set Param3 = session("ParamCollection").Item(3)
Call Param3.SetCurrentValue (cstr("B"), 12)
set Param4 = session("ParamCollection").Item(4)
Call Param4.SetCurrentValue (cstr("C"), 12)
case "CallOutcomes.rpt"
set session("ParamCollection") = Session("oRpt").Parameterfields
set Param1 = session("ParamCollection").Item(1)
Call Param1.SetCurrentValue (cstr(BeginDate),12)
set Param2 = session("ParamCollection").Item(2)
Call Param2.SetCurrentValue (cstr(EndDate),12)
case "RequestSourcesBath.rpt"
set session("ParamCollection") = Session("oRpt").Parameterfields
set Param1 = session("ParamCollection").Item(1)
Call Param1.SetCurrentValue (cstr(BeginDate),12)
set Param2 = session("ParamCollection").Item(2)
Call Param2.SetCurrentValue (cstr(EndDate),12)
set Param3 = session("ParamCollection").Item(3)
Call Param3.SetCurrentValue (cstr("B"), 12)
case "RequestSourcesSpa.rpt"
set session("ParamCollection") = Session("oRpt").Parameterfields
set Param1 = session("ParamCollection").Item(1)
Call Param1.SetCurrentValue (cstr(BeginDate),12)
set Param2 = session("ParamCollection").Item(2)
Call Param2.SetCurrentValue (cstr(EndDate),12)
set Param3 = session("ParamCollection").Item(3)
Call Param3.SetCurrentValue (cstr("S"), 12)
case "LeadsByChannel.rpt"
set session("ParamCollection") = Session("oRpt").Parameterfields
set Param1 = session("ParamCollection").Item(1)
Call Param1.SetCurrentValue (cstr(BeginDate),12)
set Param2 = session("ParamCollection").Item(2)
Call Param2.SetCurrentValue (cstr(EndDate),12)
set Param3 = session("ParamCollection").Item(3)
Call Param3.SetCurrentValue (cstr("%"), 12)
case "ManufacturerReferral.rpt", "DealerReferral.rpt", "CallPrompts.rpt"
set session("ParamCollection") = Session("oRpt").Parameterfields
set Param1 = session("ParamCollection").Item(1)
Call Param1.SetCurrentValue (cstr(BeginDate),12)
set Param2 = session("ParamCollection").Item(2)
Call Param2.SetCurrentValue (cstr(EndDate),12)
case "soa.rpt"
set session("ParamCollection") = Session("oRpt").Parameterfields
set Param1 = session("ParamCollection").Item(1)
Call Param1.SetCurrentValue (cstr(Soeno),12)
case "ManufacturerList.rpt"
case "ActiveDealerList.rpt"
case "ActiveDealerLabels.rpt"
case "ActiveMFGLabels.rpt"
case "DealersZeroLatLong.rpt"
case "DealersNoProds.rpt"
'case "soa.rpt"
case else
set session("ParamCollection") = Session("oRpt").Parameterfields
set Param1 = session("ParamCollection").Item(1)
Call Param1.SetCurrentValue (cstr(BeginDate),12)
set Param2 = session("ParamCollection").Item(2)
Call Param2.SetCurrentValue (cstr(EndDate),12)
end select
If IsObject(session("oPageEngine")) Then
set session("oPageEngine") = nothing
End If
set session("oPageEngine") = session("oRpt").PageEngine
This is the smartviewerActiveX.asp page code....
<%
'This file contains the HTML code to instantiate the Smart Viewer ActiveX.
'
'You will notice that the Report Name parameter references the rptserver.asp file.
'This is because the report pages are actually created by rptserver.asp.
'Rptserver.asp accesses session("oApp"), session("oRpt") and session("oPageEngine")
'to create the report pages that will be rendered by the ActiveX Smart Viewer.
'
%>
<HTML>
<HEAD>
<TITLE>Ineos Bath and Spa Report System v1.0</TITLE>
</HEAD>
<BODY BGCOLOR=1E457B LANGUAGE=VBScript ONLOAD="Page_Initialize">
<OBJECT ID="CRViewer"
CLASSID="CLSID:C4847596-972C-11D0-9567-00A0C9273C2A"
WIDTH=100% HEIGHT=95%
CODEBASE="/viewer/activeXViewer/activexviewer.cab#Version=8,0,0,224">
<PARAM NAME="EnableRefreshButton" VALUE=1>
<PARAM NAME="EnableGroupTree" VALUE=1>
<PARAM NAME="DisplayGroupTree" VALUE=1>
<PARAM NAME="EnablePrintButton" VALUE=1>
<PARAM NAME="EnableExportButton" VALUE=1>
<PARAM NAME="EnableDrillDown" VALUE=1>
<PARAM NAME="EnableSearchControl" VALUE=1>
<PARAM NAME="EnableAnimationControl" VALUE=1>
<PARAM NAME="EnableZoomControl" VALUE=1>
</OBJECT>
<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 = "rptserver.asp"
else
Dim webSource
Set webSource = CreateObject("WebReportSource.WebReportSource")
webSource.ReportSource = webBroker
webSource.URL = "rptserver.asp"
webSource.PromptOnRefresh = True
CRViewer.ReportSource = webSource
end if
CRViewer.ViewReport
End Sub
-->
</SCRIPT>
</BODY>
</HTML>
So basically using these pages I need to open the soa.rpt file passing it the soeno. Now does this Soeno automatically get passed to the report in the runreport.as page and if so how does Crystal accept it and use it to retireve just that information from the database? Could you tell me how to do this? Thanks so much for any hep offered.
RE: Passing parameters to a crystal report
http://support.crystaldecisions.com/communityCS/FilesAndUpdates/aspxmps85.exe.asp
And check out this thread on the topic:
http://www.experts-exchange.com/Databases/Crystal_Reports/Q_20650992.html
-k
RE: Passing parameters to a crystal report