Cognos Script Editor 1

[dhulbert]

I'm using sceduler to automate the running of a number of Impromptu reports, The scheduler calls a cognos script editor macro which opens the reports.

I want to add a new report to the macro but I need to pass date parameters to the report in the script.

Is this possible if so how, I've had a look at the help files and they aren't much use.

Thanks in advance.

 

[griffindm]

dhulbert,

Unlike reports, you cannot set prompt content when running a macro with scheduler. What you can do is use the low level file input/output functions to read a parameters file and parse the results into prompt values for the macro. I do this all the time. Just create a text file and put the contents of your prompts on different lines. Then read the file from the macro, pull the results into a prompt string, where the prompts are separated by a pipe "|" symbol, and pass the prompt string to the reports as you open them. See the FAQ's here on prompts and macros for a little more guidance.

Hope this helps,

Dave Griffin


The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"
Want good answers? Read FAQ401-2487 first!

http://www.decisionsupportgroup.com

 

[dhulbert]

Dave.

Thanks

 

[drlex]

dhulbert,
Are the date parameters you need to pass to the report related to the date on which the report runs? Further to Dave's reply, I use the DATE & FORMAT functions of the script editor to generate start and end date prompts for my reports so that generation is truly automatic.
lex

 

[dhulbert]

drlex

Pretty much yes, We just want the report to run based on the last month or the last quarter, at present when the report runs it will prompt for the dates I just want to supply the dates to the report when the scheduler calls it.

 

[drlex]

Then the ability to calculate dates in a script (based on the calendar of the computer on which scheduler is run) would be of benefit.
My reports take a yyyy-mm-dd prompt
so a line
prompt = Format(Cstr(CVar(Date)))
gives a prompt that is the computer's current date.

You can use basic arithmetic with the dates and there are functions such as Day, Month & Year to assist.
Day(Date) gives the day count to current date.
If deducted from the Date within the Cvar function, then you have the last day of the previous month.
Removing the date field and adding -01 can give the first day of the prior month.
i.e.
strendd = Format(Cstr(Cvar(Date-Day(Date))),"yyyy-mm-dd&quot
strstartd = Format(strendd,"yyyy-mm&quot + "-01"
prompt = strstartd + "|"+strendd

If you're doing quarterly, then adjust to:
strdate = Cvar(Date-Day(Date)-89) 'go to last month & then 3 months prior
strstartd = Format(strdate,"yyyy-mm&quot+"-01"
strendd = Format(Cvar(Date-Day(Date)),"yyyy-mm-dd&quot
prompt = strstartd + "|"+strendd

Obviously, this is pretty basic and doesn't adapt for working days, public holidays, but at least keeps things automated.
HTH
lex

 

[dhulbert]

Cheers

I'll give it a try. I dont need holidays etc anyhow as we're a 24/7 business.

 

[drlex]

No probs.
It occured to me that you may have fixed quarters.
You could test the date using the Month(date) and then create a prompt accordingly from a string and the Year(date) function along the lines of 'if month(date) > 3 and month(date) < 6 then prompt = Year(date) + &quot;-01-01|&quot; + Year(date) + &quot;-03-31&quot;' would give you a quarter from 1st Jan to 31st March. Remember to deduct 365 from date to give you last year.

now back to the 7.1 upgrade!
lex

 

[brepauls]

Dave Griffin,

I realize this is a huge delay in posting a response to this topic, but just am a new user of Cognos & new to Tek-tips....

I see in your part of the response you referred to reading in data from a text file. I have been in search of sample code of how to do this.

I'm a total newbie and any help would be "greatly" appreciated!!!

Many thanks in advance!!

Brenda P.

 

[griffindm]

Brenda,

The file "C:\parameter.txt" juse contains the year and period on line 1 and 2.

 Sub Main

  fname = "C:\parameter.txt"
  
  Open fname for Input as #1
  If Err<>0 then
      MsgBox "Error loading file.  Re-run program."
      Exit Sub
  End If
   
  Dim fpmpt(2)   as string  

  For x=1 to 2
      Line Input #1,fpmpt(x)
  Next x   
   
  FY    = fpmpt(1)
  PD    = Val(fpmpt(2))
  
  Close #1
  
End Sub

Hope this helps.

Dave Griffin




The Decision Support Group
Reporting Consulting with Cognos BI Tools
&quot;Magic with Data&quot;
Want good answers? Read FAQ401-2487 first!

http://www.decisionsupportgroup.com