Topic
3 replies Latest Post - ‏2012-04-24T22:39:56Z by DanielWagemann
mistryd
mistryd
28 Posts
ACCEPTED ANSWER

Pinned topic Cognos Macro

‏2012-02-13T16:45:23Z |
Hi,

I am looking for a Cognos macro which would run my Impromptu reports and send the output to user's email. Are there any macro's available.If yes request you to please share it. Your help is much appreciated.

Thanx in advance.

Regards,
Mistry
Updated on 2012-04-24T22:39:56Z at 2012-04-24T22:39:56Z by DanielWagemann
  • ktpi
    ktpi
    1 Post
    ACCEPTED ANSWER

    Re: Cognos Macro

    ‏2012-02-22T20:24:35Z  in response to mistryd
    I am looking for the same thing. I am close, but I am having trouble getting it to open the correct instance of impromptu. I have both the admin and user instances installed (possibly several versions of each) and it keeps telling me it can't open the catalog. In the meantime I am just running the job in Cognos scheduler, and emailing the file in a separate operation.
    • mistryd
      mistryd
      28 Posts
      ACCEPTED ANSWER

      Re: Cognos Macro

      ‏2012-03-05T09:06:38Z  in response to ktpi
      Hi,

      Did you got any success? If yes could you please share it with us.

      Thanx.

      Regards,
      Mistry
  • DanielWagemann
    DanielWagemann
    2 Posts
    ACCEPTED ANSWER

    Re: Cognos Macro

    ‏2012-04-24T22:39:56Z  in response to mistryd
    http://www-01.ibm.com/support/docview.wss?uid=swg21337118

    Problem(Abstract)Can one automate running a report, saving it in pdf format, and then sending it as an email attachment?

    Resolving the problem' macro to run an Impromptu report
    ' save the reports in pdf format with the date appended to the report name
    ' create an email in Outlook with the pdf attached

    ' '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ''''

    Declare Sub SaveThePDF(cat$, imr$, userclass$, pdf$ )
    Declare Sub Outlook(strSubject$, strTo$, strBody$, strAttachment$)

    Dim objImpRep as Object
    Dim objImpApp as Object
    Dim objPDFPub as Object

    Dim objOutlook as Object
    Dim objOutlookEmail as Object
    Dim objOutlookAttachments as Object
    ' '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ''''

    Sub Main()

    Dim strToday As String
    Dim strReportName1 As String
    Dim strCatalogName1 As String
    Dim strUserClass As String
    Dim strFileName1 As String
    Dim strTo1 As String

    strToday = date$
    strCatalogName1 = "D:\program files\Cognos\cer1\samples\Impromptu\reports\Great Outdoors Sales Data.CAT"
    strReportName1 = "D:\program files\Cognos\cer1\samples\Impromptu\reports\All Country Sales.imr"
    strUserClass = "Creator"
    strFileName1 = "D:\program files\Cognos\cer1\samples\Impromptu\reports\All Country Sales" & strToday & ".pdf"
    strTo1 = "whoever@company.com"

    call SaveThePDF ( strCatalogName1, strReportName1, strUserClass, strFileName1 )

    msgbox "The pdf report has been generated!"

    strSubject = "All Country Sales Report"
    strBody = "Please find attached your pdf report. If you have any questions, please do not hesitate to contact me." & Chr(13) & Chr(13) & "John Doe" & Chr(13) & "Impromptu Report Administrator" & Chr(13) & "1-800-123-4567" & Chr(13) & Chr(13)

    call Outlook(strSubject, strTo1, strBody, strFileName1)

    msgbox "The email messages has been created in Outlook!"

    End Sub

    ' '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ''''

    Sub SaveThePDF(cat$, imr$, userclass$, pdf$)

    Set objImpApp = CreateObject("CognosImpromptu.Application")
    Set objImpRep = objImpApp.OpenReport(imr)

    objImpApp.OpenCatalog cat, userclass
    strReportName = objImpRep.FullName
    objImpRep.RetrieveAll

    Set objPDFPub = objImpRep.PublishPDF

    objPDFPub.Publish pdf
    objImpRep.CloseReport
    objImpApp.CloseCatalog
    objImpApp.Quit

    Set objImpApp = Nothing
    Set objImpRep = Nothing
    Set objPDFPub = Nothing

    End Sub

    ' '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ''''

    Sub Outlook(strSubject$, strTo$, strBody$, strAttachment$)
    Set objOutlook = CreateObject("Outlook.Application")
    Set objOutlookEmail = objOutlook.CreateItem(OlMailItem)
    objOutlookEmail.Subject = strSubject
    objOutlookEmail.Body = strBody
    objOutlookEmail.To = strTo
    Set objOutlookAttachments = objOutlookEmail.Attachments
    objOutlookAttachments.Add strAttachment

    objOutlookEmail.Save
    objOutlookEmail.Send

    ' the following two options might be useful, but have been commented out for this example

    'objOutlookEmail.Display
    Set objOutlook = Nothing
    Set objOutlookEmail = Nothing
    Set objOutlookAttachments = Nothing

    End Sub

    Note: This macro be written so that it sends it to various people by performing the following action.

    Change the strTo1 to include several email addresses:
    strTo1="fredsmith@a.com;fredsmith@b.com;fredsmith@c.com"