VBS to Run Report on Enterprise Server and Output to File

Posted on 2006-05-16
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
    LVL 22

    Expert Comment

    There is at least one 3rd-party solution among the "Report Managers" listed at 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

    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

    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

    PAQed with points refunded (500)

    Community Support Moderator

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    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. …
    Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
    This video is in connection to the article "The case of a missing mobile phone (". It will help one to understand clearly the steps to track a lost android phone.
    Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

    758 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

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now