Automation of Impromtu reports using macros !!

[zaibz]

I have to automate creation of imr reports which takes two parameters and this has to run every week. I know macros can help in automating the creation of imr reports. But I have to create 50 reports (50 different parameters have to be passed) every week. I intend to use unix shell script which can run this macro. But I am not able to automate the picking of 50 different parameters(2 para for each file) to run for the same imr file and then save it as excel file.
Is there any way out for this?
Can somebody give me some other good solution for my problem?
Thanks in advance.

 

[griffindm]

zaibz,

Two different approaches can help with this problem. In both cases, you should run the report within a loop inside the macro. Store the parameters within an array and pass the values to the loop using the loop counter.

In the first approach, which is the easier of the two fconceptually, store the parameters within a static array which you initialize before the loop. An example using a sub procedure is :

Private sub Setarray ()

ASize = 3

Rptdata(1,1) = "1st value"
Rptdata(1,2) = "2nd Value
Rptdata(2,1) = "1st value"
Rptdata(2,2) = "2nd Value
Rptdata(3,1) = "1st value"
Rptdata(3,2) = "2nd Value

End Sub

The ASize value tells you how many times to run the loop.

A more complex, but potentially more useful approach, is to store the values in a table and query the table using SQLExecute and SQLRetrieve. Alternately, you could also do a report and use GetDataValue. This is especially useful if the data changes and an end-user has to maintain the table.

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

 

[zaibz]

Thanks Dave !
I think static array will not help as the second parameter is changing every week. Its like for 50 Vendors I have to create reports every week.So for each report the parameters will be the vendor code and the week number.Hence I cannot set the values in static array as week number will cahnge every week where as Vendor codes remains the same.
Is it possible to get value from stored proc and then assign that as parameter to the call of imr file in cognos macro ?


objImpApp.OpenCatalog "D:\Cognos\Test\ServiceLevel.cat", "Creator"
Set objImpRep1 = objImpApp.OpenReport("D:\Aug7_SL_report2.imr", "38937|30&quot
objImpRep1.RetrieveAll
objImpRep1.ExportASCII("d:\Cognos\sl_zaibz1.csv&quot
Set objImpRep2 = objImpApp.OpenReport("D:\Aug7_SL_report2.imr", "03075|30&quot
objImpRep2.ExportASCII("d:\cognos\sl_zaibz2.csv&quot

In above code, parameters are hard coded. Pls note second parameters remains the same which changes every week.

Any other suggestions are welcome!

 

[griffindm]

zaibz,

If the second parameter changes every week, but is constant for all the vendors being run, why not just enter it in a prompt in the macro, or if it's being run via scheduler, use logic in the macro or report to determine it on the fly if the report is run the same day each week? Then just use the static array for the vendor list, which I gather from your comments is static.

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

 

[zaibz]

Yes Dave, I am trying the same. I want to get week value on the fly only. Is it possible in macro to read parameter value from some text file. I am still thinking to avoid array and hardcode the values. Instead looking for a solution which is more dynamic.
Can you pls send looping code in macros.
Thanks!

 

[griffindm]

zaibz,

Here is a code snippet for the looping process:

Set ImpApp = CreateObject ("Impromptu.Application&quot
ImpApp.Visible 1
ImpApp.OpenCatalog CatDir+"\"+Cat1,"Run_Only",,,,1

for k = 1 to ASize
prpmt$ = RptData(k,1)+"|"+wkno
Set ImpRep=ImpApp.OpenReport(LocCopy,prmpt$)
ImpRep.ExportExcel path+"\"+fname+".xls"
ImpRep.CloseReport
next k

You can use the low level file I/O macro commands to open and read parameters from a text file, but if you can determine logic for determining the value on the fly it is much easier to maintain.

Try this code as a starting point for generating the week number:

Sub main
Dim str1 as String
Dim msgtext
startdt=CVDate("01/01/2003&quot
enddt = Now()
diff% = enddt-startdt
wkno% = int(diff%/7)
msgtext="The current week number is: " & _
CStr(wkno%)
MsgBox msgtext
End Sub

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

 

[zaibz]

Thanks Dave for your help. I am actually looking for fiscal week and not just week number which I can get the value on the fly. Oracle query is
Select to_char(Sysdate,ww) from dual;
I think i can get the value by executing the proc and receiving the value in macro.
Can you pls tell me whether these Impromptu macros can run on Unix server or not? I am working with Macros for the first time. The production Cognos server is on Unix environment.

Regards,
zaibz

 

[griffindm]

zaibz,

I'm sorry to say it doesn't look like Cognos macros are supported in a unix environment. This relates to the OS for the computer running the macro, not the OS on the database or Cognos Upfront portal.

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