VBS to Run Report on Enterprise Server and Output to File

Posted on 2006-05-16
Medium Priority
Last Modified: 2012-05-05
I am looking for a way to have a nightly VBS script run an activity report on our Enterprise Server for each employee and email it to them.  There is a single RPT report and the script would provide the date range and employee name as parameters; then repeat for each employee.

I have some very specific requirements for the body of the email, so I'm planning on generating it through the script, not using Crystal's email feature.  My plan is to make Crystal output the finished report to a known location in PDF format so my script can grab it and attach it to the email.

I know how to use VBS to create the email and attach the file and I have a very limited understanding of Crystal's COM objects.  I really need help knowing how to pass the parameters and specify the output file's name and destination.
Question by:etude
  • 2
LVL 23

Expert Comment

by:Ido Millet
ID: 16693423
There is at least one 3rd-party solution among the "Report Managers" listed at http://www.kenhamady.com/bookmarks.html that provides this type of report bursting and emailing functionality as well as a command line api (so you can drive it from within your application).

- Ido

Author Comment

ID: 16694085
Thanks for the post.  Crystal actually has a COM interface, so you can script it using COM Objects, but its a lot harder then your average command line interface, but it works.  I'm slowly working my way through the documentation to get a working script, but its painfully slow.  I was hoping someone already knew how to do what I'm trying to do.

Author Comment

ID: 16709819
Here is the snip of script I wrote to run my report if anyone else is looking.  I am sorry, but I didn't really put much effort into making this easy to understand.  I used the CE_SDK.chm found in the COM_DOCS.ZIP found on a crystal server installation as a referance.

Sub Main
    Dim IStore
    Set IStore = CrystalLogon
    ScheduleReport IStore, "Crystal_report_name", "Bob Smith"
End Sub

Function CrystalLogon
        Dim SessionManager
        Set SessionManager = CreateObject("CrystalEnterprise.SessionMgr")
        Dim Sess
        Set Sess = SessionManager.Logon(Crystal_username, Crystal_password, Crystal_server, Crystal_authtype)
        Dim IStore
        Dim LogonTokenMgr
        Dim LogonToken
        'Create the InfoStore object.
        Set CrystalLogon = Sess.Service("", "InfoStore")
        Set LogonTokenMgr = Sess.LogonTokenMgr
        LogonToken = LogonTokenMgr.CreateLogonTokenEx("", 60, 100)
End Function

Sub ScheduleReport(IStore, SI_NAME, Trader)
    'query for report and retrieve the first resolt
    Dim Result, ReportObject, PluginInterface
    Set Result=IStore.Query("Select Top 1* From CI_INFOOBJECTS Where SI_NAME='"&SI_NAME&"'")
    Set ReportObject=Result.Item(1)
    set PluginInterface = ReportObject.PluginInterface

    'set perameters
    Dim NewParam, parameter
    For Each parameter in PluginInterface.ReportParameters
        Set NewParam = parameter.CreateSingleValue
        Select Case parameter.ParameterName
        Case "BegDate"
          NewParam.Value = Format_Crystal_Date(GetDate(start_date))
        Case "EndDate"
          NewParam.Value = Format_Crystal_Date(GetDate(end_date))
        Case "Trader"
          NewParam.Value = Trader
        End Select
        If NewParam.Value <> "" Then parameter.CurrentValues.Add NewParam

    'schedule object to run now
    Dim SchInfo
    Set SchInfo = ReportObject.SchedulingInfo
    SchInfo.RightNow = true

    'Set PDF as output format
    PluginInterface.ReportFormatOptions.Format = crystal_format_pdf

    ' Set the disk options.    
    dim Disk
    dim DiskSchedulingOptions
    ' Get the Disk InfoObject from the APS.  Note that the SI_PARENTID will always be 29.
    set Disk = IStore.Query("Select SI_DEST_SCHEDULEOPTIONS, SI_PROGID From CI_SYSTEMOBJECTS Where SI_PARENTID=29 and SI_NAME='CrystalEnterprise.DiskUnmanaged'").Item(1)
    ' Get the actual DiskUnanaged object and its ScheduleOptions.
    set DiskSchedulingOptions = Disk.PluginInterface("").ScheduleOptions
    ' Set the path.
    ' Copy the disk options into the report's scheduling options. This will cause the
    ' report's instance to be copied to the directory.
    SchInfo.Destination.SetFromPlugin (Disk)

    'Schedule the report.
    IStore.Schedule Result
End Sub

Accepted Solution

CetusMOD earned 0 total points
ID: 16958166
PAQed with points refunded (500)

Community Support Moderator

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question