Hello Everybody, Can someone recom 2

[baca]

Hello Everybody,
Can someone recommend any good book on Impromptu 6.0 advanced features as stored procedures, filtering reports etc. The documentation provided with the product is not too impresive.
Actually, maybe someone could help me 2 problems, they don't seem to be very complicated, but I can't find good help.
First thing is, I run macro before running the report to gather start and end dates to limit scope of the report,
I'm getting this 2 values ok, just don't know how to filter it. I need one of the fields to be between this 2 dates, I found the following sample in the documention, but don't know what I need to set '.AppendEx' to.

Sub ApplyFilter(dStartDt$, dEndDt$)

Dim objImpApp As Object
Dim objImpCat As Object
Dim objDB as object
Dim objUser As Object
Dim objFilter As Object

Set objImpApp = CreateObject("CognosImpromptu.Application&quot
Set objImpCat = objImpApp.ActiveCatalog
Set objDB = objImpCat.Databases("SCC&quot
Set objUser = objImpCat.ActiveUserClass.UserClasses(1)
Set objFilter = objUser.CreateFilterFor _
(objDB.Tables("SCC_2A_KEYS&quot.Columns("CREATE_TIMESTAMP&quot)

With objFilter
.AppendEx objDB.Tables("SCC_2A_KEYS&quot.Columns("CREATE_TIMESTAMP&quot
.AppendEx 29 '<>
.AppendEx 1, dStartDt 'string constant
.AppendEx 1, dEndDt 'string constant
.Commit
End With

ObjImpCat.Save

Set objDB = Nothing
Set objUser = Nothing
Set objFilter = Nothing
Set objImpCat = Nothing
Set objImpApp = Nothing

End Sub

The other problem is, trying to execute stored procedure from within the macro, I created procedure in SQL Server and trying to execute the following macro code:

Sub Main()
Dim ImpApp As Object
Dim ImpSP As Object

Set ImpApp = CreateObject(&quot;Impromptu.Application&quot
ImpApp.OpenCatalog &quot;C:\Census2001\SCC_ Reporting\Testsp.cat&quot;
Set ImpSP = ImpApp.CreateStoredProcedure
ImpSP.SetProcedure &quot;TestImp&quot;
ImpSP.Execute

End Sub

It doesn't seem to be any link between the procedure and calling macro. I have feeling I'm doing something wrong setting up the catalog.

Any help would be greatly apreciated.

Andrew

 

[THamilton]

I am currently working on putting a start and end date in a filter to pull 1 weeks worth of data everytime the report is opened. The end date is the same as the system date. I am using functions to accomplish that and am very close. This might be a possibility.

 

[CognosProfessional]

I use the prompts that are provided with Impromptu for filtering choice dates. I wouldn't even bother with the stored procedures for this one. Sorry, Am I missing something here?

 

[baca]

That's no problem filtering based on prompts, except I could not find a way to default the date to let's say system date or the date from the first/last record of the recordset/table.
As far as the stored procedure goes, I needed it to update the database field after running the report.
Anyway I'm happy the project is over and I don't have to do anything with it, at least for time being.

 

[mukesh]

well baca

u can use prompts for date. One prompt for start date and one prompt for end date.

Now u van write scripts which will open the reports and at the same time u can pass the date value as a parameter.

this may solve ur problem.

bye

 

[sonksen]

I have a similar problem:
There are many reports with a start date and end date prompt to filter the data.
I have written a macro that gets expressions for these prompts, these expressions are related to the current time:
(Start of|End of) current (time|hour|minute|day|week|month|quarter|year) (+|-) <number> (days|months|years|hours|minutes|seconds)
Other prompts and the report file name are also selected.
Now I want to schedule such a report with the prompts using Cognos Scheduler. At the scheduled time another macro would be executed that converts the relative times to a date/time, depending on the time the macro is run.
The problem is that I can't pass parameters to this second macro called by the Scheduler.
Does anyone has a suggestion to solve this problem ?

 

[griffindm]

Baca - The Exexute method is no longer effective. Cognos no longer executes this method, though no error is returned. To call stored procedures you must either use the stored procedure call via the report (use the sp template) or call it via and ODBC call (see my posting on this from earlier this year). I prefer the second approach as it is more reliable and flexible (IMHO).

sonksen - Use GetDataValue and the low level file I/O options to write the output of the first macro to a file, then read the results into the second macro using the same techniques. I often use this approach when using Scheduler with macros, as it has no facility to deal with macro prompts. The Macro help file has some relevant examples.

Dave Griffin

 

[sonksen]

Hello Dave

Thank you very much for your answer.

I already thought about doing this (writing the data into a file to be read by the second macro), but there is a problem:

If many reports are scheduled, how do I know which file (or part of a file) to use in the second macro ?

Regards,
Ronald Sönksen

 

[griffindm]

sonksen,

You have two choices. I usually have a specific file name used by a single macro, and thereby avoid conflicts. If you are doing this a lot, and you stagger the launching of macros in Scheduler, you may want to use a single file name for simplicity, and just overwrite the file each time for each 'pair' of macros. I haven't tried this, but as long as you order the macros properly in Scheduler it should work.

Glad my suggestion helped you.

Dave Griffin

 

[sonksen]

Dave,

The macros will be scheduled by the end users. It's impossible to know how each report will be scheduled .

It's also possible to schedule the same report with different concurrent schedules, and different prompts.

Thank you for your prompt answer.

Regards,
Ronald Sönksen

 

[sonksen]

A colleague of our enterprise in England has found a solution to my problem.

Parameters can be specified for a macro to be scheduled, in the property ScheduleName, if the name is enclosed in double quotes. The macro name must be followed by a space and the string of parameters.

The parameters can then be accessed in the macro using Command$.

It's strange that this is not mentioned in the Cognos documentation !

 

[griffindm]

Ron Sonksen,

That's a pretty cryptic way of getting parameters to the macro via Scheduler! Tell me , can you only get the extra prompt string into scheduler by using a macro to insert the scheduled task, or can you get it into Scheduler when you insert the task manually?

Thanks,

Dave Griffin

 

[sonksen]

Hello Dave

You also can use parameters for a macro when you insert the task manually.
Just enclose the name of the macro in double quotes.

Regards,
Ronald Sönksen

 

[w860098]

The only one I have come across is &quot;Impromptu Startup!&quot;, written by Brian Olah, published by Prentice Hall.
I have only had a quick look at it, so I cannot say whether it would provide answers to your specific questions.

 

[CognosProfessional]

Another option: Schedule .bat files that call macros. It works great!

CP