populating a prompt from another report 1

[smartglass]

Hi: griffindm kindly helped with my question "macros and null data" on 14th march. I have the first report containing a list of all customers who should get the second report. Is it possible to run a loop that populates the prompt of the second report with each record in turn from the first, generating reports to fax? I have looked at arrays but can see nothing. I have tried hard coding every customer but above 50 or so the macro errors; is there a limit on the number of times a report can be run?
And finally I use send keys to populate winfax. THis rapidly deteriorates as the macro runs until completely out of synch. I guess a pause may calm down send keys; is there a "wait for a second" I can write between each send key?
Thanks again!

 

[drlex]

I guess you're using impromptu and a macro (cognos script)

If you put in a reference to the sleep function right at the start of the macro, you can call it in the script

Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Call with

Sleep 60000

where 60000 is the number of milliseconds to sleep for.

With re-running a report for a number of times, I find it best to have a report that lists the customers, with an initial column giving the total. I can then read the total, populate an array and use the total as the limit in a loop for the required report.

Here's a snippet (dim statements omitted)

   Set objImpApp = CreateObject("CognosImpromptu.Application")
   objImpApp.Visible 1
   objImpApp.OpenCatalog "\\fileserver\cognos\catalogues\mycat.cat","Creator",,,,1
   Set objImpRep = objImpapp.OpenReport("\\fileserver\cognos\Reports\Branch List.imr")
   total = objImpRep.GetDatavalue(1,1)
   'Get the value in the first column, first row of the query = no. of branches
   For x = 1 to total
      strbranch(x) = objImpRep.GetDatavalue(2,x)
      Next x
   objImpRep.CloseReport
   Set objImpRep = Nothing

   For x = 1 to total   
      strprompt = strbranch(x)
      strfile = strfiledest + strbranch(x) + ".xls"
      Set objImpRep = objImpapp.OpenReport(strreport,strprompt)
      Set objImpRep = objImpApp.ActiveDocument
      objImpRep.RetrieveAll
      objImpRep.ExportExcelWithFormat strfile
      objImpRep.CloseReport
      Set objImpRep = Nothing
   Next x
   objImpApp.Quit

Sendkeys is a nasty kludge. If you can't use OLE to control WinFax in CognosScript, can you do everything in VBA?

lex


soi la, soi carré