Automatic report generation on a monthly basis

[25456378]

Dear COGNOS users,

I generate monthly reports calculating the number of requests received in the past month. This means that in the first day of each month I have to run approximately 10 different reports each time changing the start and end date to that of the previous month. Is there a possibility of automating this process with COGNOS running the reports one ofther the other in the background, perhaps overnight and sending these automatically via e-mail to the dept requiring this data. I am using Cognos Impromptu Version 7.5. Please note that I am not an expert user so I will need some extra guidance!!

Thanks,
Ian

 

[drlex]

Ian,
This is not only possible, but anticipated by Cognos. You can use Cognos Scheduler in conjunction with Cognos Script (VBA-like). There should be some pdfs in the Documentation of your installation which should guide you here. Mailing is sometimes an issue, as your IT department ought to be wary of automated processes emailing. I use CDO on the Cognos server and this feeds into our corporate Outlook/Exchange set-up.

An example of a Cognos Script that opens a report, saves it in Excel format and mails it using CDO:

Sub Main()
   'objects
   Dim objImpApp As object
   Dim objImpRep As Object 
   Dim objOutlookEmail as Object
   '
   Set objImpApp = CreateObject("CognosImpromptu.Application")
   objImpApp.OpenCatalog "E:\catalogues\My_catalog.cat","Creator",,,,1
         Set objImpRep = objImpApp.OpenReport ("E:\Reports\Open Accounts.imr")
         Set objImpRep = objImpApp.ActiveDocument    
         objImpRep.RetrieveAll
         objImpRep.ExportExcelWithFormat  "E:\Excel\Open Accounts.xls"
         objImpRep.CloseReport
      Set objImpRep = Nothing   
   objImpApp.Quit
   set objImpApp = Nothing
   '
   Set objOutlookEmail = CreateObject("CDONTS.NewMail")
      with objOutlookEmail
         .To = "big.cheese@acme.com"
         .From = "Cognos@acme.com"
         .Subject = strreport
         .Body = "Herewith your report"
         .MailFormat = 0 
         .AttachFile  "E:\Excel\Open Accounts.xls"
         .Send
      End with
   set objOutlookEmail = nothing
   '
End Sub

Prompts for a report can be passed in the OpenReport command using a pipe ("|") as a delimiter and are expected to be strings.
e.g.

... Set objImpRep = objImpApp.OpenReport ("E:\Reports\Open Accounts.imr", "1|2013-09-01|2013-09-30")
[code]

I'd recommend that you read through the pdfs and have a go with the samples. You can then think of looping through the set of reports, using arrays for the report name, prompts, output name, and recipient.

One can also automatate other applications using Cognos Script, so I have used Excel to better format the Impromptu output before emailing/transferring to common folders.
example:
[code]
   'Change report METADATA
   Set objExcel = CreateObject("Excel.Application")
   objExcel.Application.ScreenUpdating = False
   objExcel.Application.DisplayAlerts = False
      For x = 1 to 7
         objExcel.Application.Workbooks.Open strfiledest + strreport(x) + ".xls"
         objExcel.Workbooks(1).BuiltinDocumentProperties("Author") = "Cognos " + Format(Cvar(Date),"D Mmm yyyy")
         objExcel.Workbooks(1).BuiltinDocumentProperties("Comments") = "Produced from Macro CREDIT.MAC"
         objExcel.Application.Workbooks(1).SaveAs strfiledest + strreport(x) + ".xls", Fileformat:=-4143
         objExcel.Application.Workbooks(1).Close  
      Next x
   objExcel.Application.Quit 
   Set objExcel = Nothing

I hope that this gives you a good starting point!



soi là, soi carré

 

[25456378]

Thanks for your reply drlex. It is a good starting point because I have a confirmation that what I need can be done, but I am still a long way from understanding how to write the code required to do these things!! I am a normal humble COGNOS user and not a programmer.

So should I be able to find pre written codes for macros within COGNOS specifically to automatically start a report and set the start and end dates prompts also automatically?

Thanks,
Ian

 

[meffan]

Ian,

Like you, I am not a programmer. We schedule reports all the time. On your home page, there are several icons to the right of the modified dates for your reports. One of the icons looks like a calendar with a clock on it, if you don't see it, click more and you will see the 'New Schedule' link. From here you can schedule your reports. If you want to email it on a schedule, click the 'Override the default values' box under OPTIONS. On the right side you will see the email options.

I hope that helps,
Meaghan