Impromptu Macro with prompts

[crndev]

Hello

I have an impromptu report, with prompt on one column(City).
There are basically 300 different cities.
I have to create a Macro, to run this impromptu report,
and have the city entered 300 times ( each city, for each time) and get it saved (excel format) with the city as a part of the file name.
SO I should have totally 300 reports (in Excel format) for all the cities.

Could someone help me how this could be done with a macro.
Thankyou

 

[drlex]

Sure,
The best way would be a 2 stage macro - the first stage would fill an array with the name of each city and then the second stage would be a loop to run the report for each city in the array.

What you need to do is determine the best way to find all the cities - do you want to read a flat file list or is it best to run an impromptu report against a database to provide the list?

If the former, then the get function will allow you to read lines; if the latter, then the get data value command is required. (I can't check syntax as I've not got impromptu running here).

To get started, have a look at the help file in cognos scripteditor and the pdf file for macros which should be in the documentation folder of your install. If you've Visual Basic experience, you should have no problems.

Post back if you get stuck or have further questions.

soi la, soi carré

 

[crndev]

Thanx Drlex,
I am kinda new to this subject.

I want to pull from database to provide the list (300 cities).
Could you please give me more step by step process.

How does the macro look like for example?
Array as u said, makes sense, but how to implement it
as a code?

Thanku

 

[crndev]

Hi drlex, i am enclosing the sample

Sub Main()
Dim objImpApp As object
Dim objImpRep As Object
Set objImpApp = CreateObject("CognosImpromptu.Application")
objImpApp.Visible 1
objImpApp.OpenCatalog _
"c:\cognos\samples\outdoors.cat","Creator",,,,1
Set objImpRep = _
objImpApp.OpenReport("c:\cognos\samples\" & _
"prompt1.imr","40100,40101|42000")
objImpRep.CloseReport
objImpApp.Quit
Set objImpApp = Nothing
Set objImpRep = Nothing

End Sub

 

[crndev]

Hi
I have done until so far for just one city,Calgary.
Now how do I feed 300 cities to the report as prompts and save with the city name as part of the output file excel.

----------------
Sub Main()
Dim objImpApp As object
Dim objImpRep As Object

Set objImpApp = CreateObject("CognosImpromptu.Application")
objImpApp.OpenCatalog _
"C:\Sample.CAT","Creator"

Set objImpRep = _
objImpApp.OpenReport("C:\report.imr","Calgary")
objImpRep.ExportExcel("C:\resultCalgary.xls")
objImpRep.CloseReport

objImpApp.Quit
Set objImpApp = Nothing
Set objImpRep = Nothing

End Sub

 

[griffindm]

crndev,

I usually do this with three reports. The first report just returns the number of distinct cities. This is used in a redim statement in the macro to set the size of the array. Then the second report gives the distinct city values. (You can alternately do both in a single report). You then use a loop to populate the array with the city values. Both of these are done with the GetDataValue call that drlex mentioned above. Check the macro help file for specifics on it's use.

Then you repeat the loop again calling the report and using the appropriate array value (using the loop counter) to create your output for each city.

Give it a try and come back with specific questions.

Regards,

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

 

[crndev]

ok Thankyou,
I will be back