Passing parameters to a crystal report

SmileyFace · Jul 30, 2003

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!!

synapsevampire · Jul 30, 2003

The parameter may not exist within the report at all, rather a record selection is passed from VB.

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

SmileyFace · Jul 30, 2003

Hey thanks! I guess you are probably right. The selection criteria is set inside of VB. I couldn't find any parameters set in the report. Then maybe you can help me out here.....I am helping convert a Vb application over to ASP to make it web based. With the little Crystal knowledge I have I was able to use what we have set up for our other websystems to open the Crystal Reports Viewer. I have a page which is basically an Order Review page where I should be able to print the order from the menu. When I click on print order, I would like it to pass the order number over to the report so that it can pull information for that order from an Access database. SO what I did was connect to the database using ASP and retrieve the order number.....called Soeno. Right now since it is still in developmental stages I am just trying to pass over an existant order number directly. Here is the code I use for that....

Dim cnn
Dim rs
Dim strSQL
Dim Soeno

Set cnn = Server.CreateObject("ADODB.Connection&quot

sDataDir = Server.MapPath ("oms\oms32\reports.mdb&quot

cnn.Open "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=" & sDataDir & ";"

Set rs = Server.CreateObject("ADODB.Recordset&quot

strSQL = "select * from soa_header where soeno='S12345'"
rs.Open strSQL, cnn
Do while not rs.EOF
Soeno = rs("soeno&quot
'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&quot

%>', '', 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" %>



<%

reportname = Request.QueryString("ReportName&quot

BeginDate = Request.QueryString("BeginDate&quot

EndDate = Request.QueryString("EndDate&quot

Soeno = Request.QueryString("Soeno&quot

'reportname = "DailyDealerReport.rpt"
' CREATE THE APPLICATION OBJECT
If Not IsObject (session("oApp&quot

) Then
Set session("oApp&quot

= Server.CreateObject("CrystalRuntime.Application&quot

End If

' CREATE THE REPORT OBJECT
Path = Request.ServerVariables("PATH_TRANSLATED&quot

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&quot

) then
Set session("oRpt&quot

= nothing
End if

' rpttoview = "D:\Webdata\Release\oms2003\soa.rpt"
' Set session("oRpt&quot

= session("oApp&quot

.OpenReport(rpttoview,1)
set session("oRpt&quot

= session("oApp&quot

.OpenReport(path & reportname,1)
session("oRpt&quot

.MorePrintEngineErrorMessages = False
session("oRpt&quot

.EnableParameterPrompting = False
session("oRpt&quot

.DiscardSavedData

'check for parameters
select case reportname

case "DailyDealerReport.rpt"
set session("ParamCollection&quot

= Session("oRpt&quot

.Parameterfields
set Param1 = session("ParamCollection&quot

.Item(1)
Call Param1.SetCurrentValue (cstr(BeginDate),12)

case "DailyDealerReportControl.rpt"
set session("ParamCollection&quot

= Session("oRpt&quot

.Parameterfields
set Param1 = session("ParamCollection&quot

.Item(1)
Call Param1.SetCurrentValue (cstr(BeginDate),12)

case "DailyDealerLabels.rpt"
set session("ParamCollection&quot

= Session("oRpt&quot

.Parameterfields
set Param1 = session("ParamCollection&quot

.Item(1)
Call Param1.SetCurrentValue (cstr(BeginDate),12)

case "WeeklyManufacturerReportSpa.rpt"
set session("ParamCollection&quot

= Session("oRpt&quot

.Parameterfields
set Param1 = session("ParamCollection&quot

.Item(1)
Call Param1.SetCurrentValue (cstr(BeginDate),12)

set Param2 = session("ParamCollection&quot

.Item(2)
Call Param2.SetCurrentValue (cstr(EndDate),12)

set Param3 = session("ParamCollection&quot

.Item(3)
Call Param3.SetCurrentValue (cstr("S&quot

, 12)

set Param4 = session("ParamCollection&quot

.Item(4)
Call Param4.SetCurrentValue (cstr("%&quot

, 12)

case "WeeklyManufacturerReportSpaControl.rpt"
set session("ParamCollection&quot

= Session("oRpt&quot

.Parameterfields
set Param1 = session("ParamCollection&quot

.Item(1)
Call Param1.SetCurrentValue (cstr(BeginDate),12)

set Param2 = session("ParamCollection&quot

.Item(2)
Call Param2.SetCurrentValue (cstr(EndDate),12)

set Param3 = session("ParamCollection&quot

.Item(3)
Call Param3.SetCurrentValue (cstr("S&quot

, 12)

set Param4 = session("ParamCollection&quot

.Item(4)
Call Param4.SetCurrentValue (cstr("%&quot

, 12)

case "WeeklyManufacturerReportBath.rpt"
set session("ParamCollection&quot

= Session("oRpt&quot

.Parameterfields
set Param1 = session("ParamCollection&quot

.Item(1)
Call Param1.SetCurrentValue (cstr(BeginDate),12)

set Param2 = session("ParamCollection&quot

.Item(2)
Call Param2.SetCurrentValue (cstr(EndDate),12)

set Param3 = session("ParamCollection&quot

.Item(3)
Call Param3.SetCurrentValue (cstr("B&quot

, 12)

set Param4 = session("ParamCollection&quot

.Item(4)
Call Param4.SetCurrentValue (cstr("%&quot

, 12)

case "WeeklyManufacturerReportCanada.rpt"
set session("ParamCollection&quot

= Session("oRpt&quot

.Parameterfields
set Param1 = session("ParamCollection&quot

.Item(1)
Call Param1.SetCurrentValue (cstr(BeginDate),12)

set Param2 = session("ParamCollection&quot

.Item(2)
Call Param2.SetCurrentValue (cstr(EndDate),12)

set Param3 = session("ParamCollection&quot

.Item(3)
Call Param3.SetCurrentValue (cstr("S&quot

, 12)

set Param4 = session("ParamCollection&quot

.Item(4)
Call Param4.SetCurrentValue (cstr("C&quot

, 12)

case "WeeklyManufacturerReportCanadaBath.rpt"
set session("ParamCollection&quot

= Session("oRpt&quot

.Parameterfields
set Param1 = session("ParamCollection&quot

.Item(1)
Call Param1.SetCurrentValue (cstr(BeginDate),12)

set Param2 = session("ParamCollection&quot

.Item(2)
Call Param2.SetCurrentValue (cstr(EndDate),12)

set Param3 = session("ParamCollection&quot

.Item(3)
Call Param3.SetCurrentValue (cstr("B&quot

, 12)

set Param4 = session("ParamCollection&quot

.Item(4)
Call Param4.SetCurrentValue (cstr("C&quot

, 12)

case "CallOutcomes.rpt"
set session("ParamCollection&quot

= Session("oRpt&quot

.Parameterfields
set Param1 = session("ParamCollection&quot

.Item(1)
Call Param1.SetCurrentValue (cstr(BeginDate),12)

set Param2 = session("ParamCollection&quot

.Item(2)
Call Param2.SetCurrentValue (cstr(EndDate),12)

case "RequestSourcesBath.rpt"
set session("ParamCollection&quot

= Session("oRpt&quot

.Parameterfields
set Param1 = session("ParamCollection&quot

.Item(1)
Call Param1.SetCurrentValue (cstr(BeginDate),12)

set Param2 = session("ParamCollection&quot

.Item(2)
Call Param2.SetCurrentValue (cstr(EndDate),12)

set Param3 = session("ParamCollection&quot

.Item(3)
Call Param3.SetCurrentValue (cstr("B&quot

, 12)

case "RequestSourcesSpa.rpt"
set session("ParamCollection&quot

= Session("oRpt&quot

.Parameterfields
set Param1 = session("ParamCollection&quot

.Item(1)
Call Param1.SetCurrentValue (cstr(BeginDate),12)

set Param2 = session("ParamCollection&quot

.Item(2)
Call Param2.SetCurrentValue (cstr(EndDate),12)

set Param3 = session("ParamCollection&quot

.Item(3)
Call Param3.SetCurrentValue (cstr("S&quot

, 12)

case "LeadsByChannel.rpt"
set session("ParamCollection&quot

= Session("oRpt&quot

.Parameterfields
set Param1 = session("ParamCollection&quot

.Item(1)
Call Param1.SetCurrentValue (cstr(BeginDate),12)

set Param2 = session("ParamCollection&quot

.Item(2)
Call Param2.SetCurrentValue (cstr(EndDate),12)

set Param3 = session("ParamCollection&quot

.Item(3)
Call Param3.SetCurrentValue (cstr("%&quot

, 12)

case "ManufacturerReferral.rpt", "DealerReferral.rpt", "CallPrompts.rpt"
set session("ParamCollection&quot

= Session("oRpt&quot

.Parameterfields
set Param1 = session("ParamCollection&quot

.Item(1)
Call Param1.SetCurrentValue (cstr(BeginDate),12)

set Param2 = session("ParamCollection&quot

.Item(2)
Call Param2.SetCurrentValue (cstr(EndDate),12)

case "soa.rpt"
set session("ParamCollection&quot = Session("oRpt&quot.Parameterfields
set Param1 = session("ParamCollection&quot.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&quot

= Session("oRpt&quot

.Parameterfields
set Param1 = session("ParamCollection&quot

.Item(1)
Call Param1.SetCurrentValue (cstr(BeginDate),12)

set Param2 = session("ParamCollection&quot

.Item(2)
Call Param2.SetCurrentValue (cstr(EndDate),12)
end select

If IsObject(session("oPageEngine&quot

) Then
set session("oPageEngine&quot

= nothing
End If
set session("oPageEngine&quot

= session("oRpt&quot

.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&quot

, session("oRpt&quot

and session("oPageEngine&quot

'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&quot

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&quot

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.

synapsevampire · Jul 31, 2003

Here's the sample ASP apps:

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

SmileyFace · Jul 31, 2003

Thanks Synapsevampire. I'll check it out!

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Passing parameters to a crystal report

SmileyFace

Programmer

synapsevampire

Programmer

SmileyFace

Programmer

synapsevampire

Programmer

SmileyFace

Programmer

Similar threads

Part and Inventory Search

Sponsor