phone: (540)338-0194
email: ken@kenhamady.com

ReCrystalize

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")
%>