Crystal
Reports Training by Ken Hamady, MS, Reporting and Training Nationwide
Sample RAS script for ASP:
The following script was
contributed
by Aaron Alexander of Bluebridge.com. Apparently Aaron
spent
a lot of time with Business Objects Developer Support team getting the
RAS code to work. (It isn't
very well documented.) After several significant contributions
from the guys at BO, Aaron came up with the following generic script
that he
thought would benefit others. He says that it has been
working,
trouble free, for several months. It comes with no guarantees, but you can use it however you
like.
Please let me know if you have any feedback.
<%
ReportName=RequesT("ReportName") & ".rpt"
Param1Val=request("param1")
Param2Val=request("param2")
Param3Val=request("param3")
Param4Val=request("param4")
'************************************************************
'***
Create the Report Client
Document
***
'************************************************************
' Creating the Report Client Document is similar to creating the
reference
' to the session("oRpt") object most commonly used when web reporting
' with the Report Designer Component (RDC)
dim Path
dim iLen
Path =
Request.ServerVariables("PATH_TRANSLATED")
While (Right(Path, 1) <> "\" And Len(Path) <>
0)
iLen = Len(Path) -
1
Path = Left(Path,
iLen)
Wend
Dim ReportDoc, objFactory, rptAppSession
'Use the OjectFactory object to abstract the version
number
'to one location
Set objFactory =
CreateObject("CrystalReports.ObjectFactory.2")
Set Session("objFactory") = objFactory
Set rptAppSession =
objFactory.CreateObject("CrystalReports.ReportAppSession")
' The name of the ReportAppServer is specified in
clientSDKOptions.xml
rptAppSession.Initialize
Set Session("rptAppSession") = rptAppSession
'Create a new ReportClientDocument object for this
reportAppSession
Set Session("oClientDoc") =
rptAppSession.CreateService("CrystalClientDoc.ReportClientDocument")
'Open the report
Session("oClientDoc").Open "rassdk://" & Path&ReportName
'=========================================================================================
'
' CHANGING DATABASE INFO AT RUNTIME
'
' Some things to note about the code below:
'
' 1. It gets only the first connection info found in the report.
If
your report/subreports
' contains more than one data source, this code will
have
to be modified accordingly
'
' 2. Crystal Reports 9 stores the fully-qualified table name and one of
the
other things
' this sample does is change the table name
from fully-qualified
to the simple table name
'
' 3. This example demonstrates how to change the database for ODBC - it
will
need to be modified
' to get it to work for OLE DB.
'
'==========================================================================================
'Set the NEW Information: DSN, database, username, and password
DSNName = "xxxxx"
Database = "Dbname"
username = "yourusername"
password = "yourpass"
' Get the connection information from the report document for the first
table
in the collection
Set oDBInfo =
Session("oClientDoc").DatabaseController.GetConnectionInfos().Item(0)
' Get the connection info attributes
Set pbLevel1 = oDBInfo.Attributes
'This 'first level' property bag will be used to define the basic
database
info for the report
pbLevel1.Item("QE_ServerDescription") = Database
With pbLevel1
.Item("Database Name") = Database
.Item("Server Name") = DSNName
.Item("Server Type") = "ODBC - " & DSNName
End With
'This 'second level' property bag contains info that is specific to the
type
of db connection
'in this case, it will contain our OLE DB specific information
Set pbLevel2 = pbLevel1.item("QE_LogonProperties")
pbLevel2.Item("DSN") = DSNName
'Attaching the QE specific properties to the 'level 1' property bag
pbLevel1.Item("QE_LogonProperties") = pbLevel2
'Add thew new property bag info to the connection object (and set the
username/password)
oDBInfo.Attributes = pbLevel1
oDBInfo.UserName = "username"
oDBInfo.Password = "password"
'===============================================================
' CHANGING THE MAIN REPORT DATABASE INFO
'===============================================================
'Get the collection of tables in the main report
Set Tables = Session("oClientDoc").DataDefController.Database.Tables
For Each table in Tables
'clone the table object
Set newTable = Table.Clone
'set the table's connectionInfo to the current
connection
info
newTable.ConnectionInfo = oDBInfo
'set the table object qualified name to include the
new
database name
'i.e. original = 'db1.dbo.myTable', new =
'db2.dbo.myTable'
newTable.QualifiedName = Database & ".dbo."
& Table.Name
'put this newly modified table object back into the
report
client doc
Session("oClientDoc").DatabaseController.SetTableLocation
table, newTable
Next
'===============================================================
' CHANGING THE DATABASE FOR ALL SUBREPORTS
'===============================================================
'get a collection of subreport names
Set subReportNames =
Session("oClientDoc").SubReportController.QuerySubreportNames
For each subName in subReportnames
'we can't reference a subreport table object
directly,
so we get the collection of tables first
Set subTables =
Session("oClientDoc").SubReportController.GetSubreportDatabase(subName).Tables
For each subTable in subTables
'clone the subreport table object
Set newSubTable = subTable.Clone
'set the subreport table's
connectionInfo
to the current connection info
newSubTable.ConnectionInfo =
oDBInfo
'set the table object qualified
name
to include the new database name
'i.e. original =
'db1.dbo.myTable',
new = 'db2.dbo.myTable'
newSubTable.QualifiedName =
Database
& ".dbo." & subTable.Name
'put this newly modified table
object
back into the report client doc
Session("oClientDoc").SubReportController.SetTableLocation
subName, subTable, newSubTable
Next
Next
'************************************************************
'***
Work with the reports
parameter
***
'************************************************************
'This line creates an object to represent the collection of parameter
fields
that are contained in the report.
Set Session("ParamCollection") =
Session("oClientDoc").DataDefinition.ParameterFields
'Create a reference to the parameter value to be set
for i=0 to session("paramcollection").count -1
Set session("ParamToChange") =
session("ParamCollection").Item(i)
'This line creates a temporary variable to store the
value
to pass to the paraemter field.
' In this case it’s defined as a discrete value.
Set session("NewValue") =
objFactory.CreateObject("CrystalReports.ParameterFieldDiscreteValue")
strValue = Trim(Request("param" & i+1))
Select Case (session("ParamToChange").Type)
Case 6 ' Number value
if
isNumeric(strValue)
then
session("NewValue").Value = CLng(strValue)
else
error = true
Exit For
end if
Case 9,15 ' Date value
if
IsDate(strValue)
then
session("NewValue").Value = cdate(strValue)
else
error = true
Exit For
end if
Case 11 'string value
session("NewValue").Value
= cstr(strValue)
Case Else
'not supported
yet
End Select
'The following lines of code creates the Parameter
Field
object.
'From the Parameter collection which will be
changing the
value for
Set session("TempParam") =
objFactory.CreateObject("CrystalReports.ParameterField")
session("ParamToChange").CopyTo session("TempParam")
'This line sets the new current value for the
Parameter.
session("TempParam").CurrentValues.Add(session("NewValue"))
'The ParameterFieldController is used to add,
remove, and
modify parameter fields in a report.
Set session("ParamController") =
Session("oClientDoc").DataDefController.ParameterFieldController
session("ParamController").Modify
session("ParamToChange"),
session("TempParam")
next
'working with recordselection
dim Filter
Set filter =
session("oclientDoc").DataDefController.DataDefinition.RecordFilter
' Set the text of the Record Filter through the FreeEditingText property
' Modify the record filter through the Record Filter Controller
if reportname="test" then
strSelect="{ServiceInvoice.InvoiceDate} >=
cdate('"
& request("PAram1") & "') and {ServiceInvoice.InvoiceDate}
<= cdate('"
& request("PAram2") & "') "
if cint(request("Param3"))<>0 then
strSelect=strSelect & " and
{Company.Branch}="
& request("PAram3")
end if
Filter.FreeEditingText=strSelect
end if
session("oclientDoc").DataDefController.RecordFilterController.modify
filter
Response.Redirect("ActiveXViewer.asp")
%>