Impromptu Question 2

[Swathi37]

Hi,
I am new to Cognos. I generate monthly reports. There is one such report which needs to be scheduled. This report should run every month. My question is how should I use the date function that pulls out the information for the previous month. Ex. It should pull Feb data since current month is March. If month is April it should pull March data. Please help me.

 

[umag]

Define catalog prompts like start date and end date. In your filter use start date>= 'database date field' and end date <= 'database date field'.

 

[drlex]

And then you could have a script in scheduler to open and run the report - this will create a prompt of the first and last dates of last month, in the format yyyy-mm-dd and then print the report. You may need to tailor it for your DB, catalogue security and version of Impromptu.
HTH
lex

Sub Main
'Variables
Dim startdate
Dim strtoday as String
Dim strendprevmonth as String
Dim strstartprevmonth as String
Dim strprompt as String
'Objects
Dim objImpApp As object
Dim objImpRep1 As object ' is report to test date
'Set variables
startdate = CVar(Date)
strtoday = CStr(startdate)
strendprevmonth = DateValue(strtoday)-Day(strtoday)
strstartprevmonth = CVdate((&quot;01/&quot;+Right(strendprevmonth,7)))
strprompt = Format (strstartprevmonth,&quot;yyyy-mm-dd&quot + &quot;|&quot; + Format(strendprevmonth,&quot;yyyy-mm-dd&quot
'
'Start Impromptu and make it visible
Set objImpApp = CreateObject(&quot;CognosImpromptu.Application&quot
objImpApp.Visible 1
objImpApp.OpenCatalog &quot;{your catalogue location}&quot;,,,,1
Set objImpRep = objImpapp.OpenReport(&quot;{Your report location&quot;,strprompt)
objImpRep.Print
'or what ever you want to do with it
- HTML, pdf, etc
Set objImpRep = Nothing
Set objImpApp = Nothing

End Sub

 

[Prasad678]

Hi,
Instead of putting prompts & writing codes, this could be a better solution :

In ur Filter condition put :

urDATEField < make-datetime(yyyy,mm,dd)
yyyy = Year(today())
mm = month(today()-1)
dd=1

or check if there is a function to subtract 1 month from today().


check if its working.

Prasad.

 

[drlex]

But note that putting some functions in the filter can result in all records being returned to the client (PC) for filtering, rather then being performed on the DB. Checking the SQL generated should show what is being passed to the DB. (I suffered in this way until switching to prompts and scripts.)
lex

 

[griffindm]

lex is right on this one. A better filter would be to use prompt filters on Year and Month and use the database-capable functions for these to drive the filter to the back-end database. The makedate function is local only and can be a killer unless it is coupled with another filter element that allows the database to parse the data to a smaller set if the original table sources are large.

Dave Griffin
The Decision Support Group
Reporting Consulting with Cognos BI Tools
&quot;Magic with Data&quot;
Want good answers? Read FAQ20-2863 first!

http://www.decisionsupportgroup.com

 

[Swathi37]

Hi All,

Thank you so much for all your help.