Macro and Prompt

[ThunderForest]

v 7.1.3

I have a report that has a subreport with a date prompt in it. I'm trying to write a macro so I can run this report automatically without any intervention. How do I pass the dates to the prompt? I did see examples where the prompt could be created within the script, but this prompt already exists in the report and I wanted to leave this report in tact. Here's what I have so far:

Sub Main()
   Dim objImpApp as Object
   Dim objImpRep as Object
   Dim objPDFPub as Object

   'These are the prompt values
   bDate = Format(CStr(CVar(Date-1)),"yyyy-mm-dd")
   eDate = Format(CStr(CVar(Date-1)),"yyyy-mm-dd")

   Set objImpApp = CreateObject("CognosImpromptu.Application")
   objImpApp.Visible True

   objImpApp.OpenCatalog "\\powervault\commonshare$\Cognos\Catalogs\LaborReporting.cat","User",,,,1

   Set objImpRep = objImpApp.OpenReport("\\powerServer\common$\Cognos\EmployeeHours.imr")
   
   objImpRep.Reexecute

'   Set objPDFPub = objImpRep.PublishPDF
'   objPDFPub.Publish "c:\my documents\RptRepository\EmployeeHours.Pdf"
   
   objImpRep.CloseReport


   objImpApp.Quit
   Set objImpApp = Nothing
   Set objImpRep = Nothing
   Set objPDFPub = Nothing
End Sub

Getting answers before I'm asked.
Providing answers if I can.

 

[drlex]

ThunderForest,
Prompts go after the file path in the OpenReport line of your script with a comma prior.
A pipe ("|") is used as a delimiter between multiple prompts, whilst a comma is used for values within prompts.

Open CognosScriptEditor, choose help and search for OpenReport to see the 'approved' syntax and example.

In your case, assuming that each date is a separate prompt, your code would become

 Set objImpRep = objImpApp.OpenReport("\\powerServer\common$\Cognos\EmployeeHours.imr", bDate + "|" + eDate )
[\code]



[i]soi la, soi carré[/i]

 

[almeids]

Off topic - my apologies -
but drlex, I've been curious, what does the idiom "soi la, soi carré" mean? My usually unfailing friend Google was of no assistance.
thanks...

 

[drlex]

Off topic - my apologies -
but drlex, I've been curious, what does the idiom "soi la, soi carré" mean? My usually unfailing friend Google was of no assistance.
thanks...

With my rusty language skills, closest I could get to the concept of "be there, (or) be square". Cue outraged Francophiles in 3..2..

soi la, soi carré

 

[almeids]

Mercy.
(No outrage here...I only cause it with my fractured franglais)

 

[ThunderForest]

Glad all of you kept the conversation going....

Getting closer. There are two prompts, each have 6 boxes. In this case, only the first two are populated in each prompt. I get r440 object error and the run stops at the line below. The data type is date in the prompt manager. Do I need to convert them?

Set objImpRep = objImpApp.OpenReport("G:\Cognos\EmployeeHours.imr", bDate,eDate,,,, + "|" + bDate,eDate,,,, )

Getting answers before I'm asked.
Providing answers if I can.

 

[drlex]

ThunderForest,
Welcome back; glad you could rejoin the fray...

Commas are to provide multiple values for a prompt; see the help file entry.
Have you tried using pipes in place of the commas and using quotes to surround all?

Set objImpRep = objImpApp.OpenReport("G:\Cognos\EmployeeHours.imr", bDate + "|"+ eDate + "|||||" + bDate + "|" + eDate + "||||"  )

The lack of characters between each pipe in the |||| sequences should cause the default value to be accepted.



soi la, soi carré

 

[ThunderForest]

Thanks. There must be some other problem with the report I gave you above, probably because of the subreport having the prompt. This command worked for another report:

Set objImpRep = objImpApp.OpenReport("G:\Cognos\Reports\ScrapReporting.imr", bDate+"|"+eDate)

But it won't write the PDF, and crashes there. I get R438 internal automation error. Call customer support.

Getting answers before I'm asked.
Providing answers if I can.

 

[ThunderForest]

What I've found since the above:

When publishing to PDF, if I leave out the path then publishing will work and default to ..\windows\system32

The macro will stop for reports that have prompts in sub-reports. The macro will continue once the values are populated manually.

Getting answers before I'm asked.
Providing answers if I can.