Multiple generation of one report with Macro

[1hrllabs]

I have a single report that has two prompts, month and org.
I have 16 unique orgs. I know how to write the script for this part. But since the month would be the same for all orgs how do I enter this prompt information only once? Can I write the prompt within the macro so that I don't have to hard code it each month?

 

[drlex]

Yes; the best way would be to set an array for the orgs and either hard-code them into the macro or read them from a csv file or another impromptu report.

A loop (For...next) can then be used to run the report for the number of orgs

If there is a relation between the date when you run the macro and the prompt required for the report, then you can derive the date too. CVar(DATE) returns the current date.
e.g. CVar(Date)-Day(CVar(date) will give you the last day of the previous month, so that a line like

strdateprompt = Format(CVar(Date)-Day(CVar(date),"yyyy-mm-")+"01|" + Format(CVar(Date)-Day(CVar(date),"yyyy-mm-dd")

will give you a range of the previous month

As you are probably aware, the prompts can then be concatenated with a pipe ('|') so as to be passed to the report in one instruction.

So if you have an array strorg(x) and the current month, you could construct a prompt like

strprompt = strorg(x)+"|" +str$(month(Cvar(date)))

I'd recommend using a temporary variable like above, as I've had some issues with using an array directly as a prompt.

Post your code if you want more tailored help

happy friday
lex

soi la, soi carré

 

[1hrllabs]

I should have been more specific in regarding the month. The prompt is for the month number. For instance, if I'm running the reports for January, 1 would be entered for that prompted field.
I've already hardcoded the orgs, so I'm almost there. Where I need help is exactly how to write the code for the
?month? prompt. Again, I like to do this once and hopefully able to do this via the macro in the form of a loop or however. Thanks.

I'll post a portion of the code since it's pretty lengthy.
The first prompt is org and the second prompt (the one I'm looking for help on) is the month: 30.2 is an org, 2 is for month 2 or February.

Set objImpRep = _
objImpApp.OpenReport("s:\imp71_reporting_tools\joe's reports\green book\" & _
"03-ORG Calendarized GB.imr","30.2|2")
objImpRep.RetrieveAll
objImpRep.Print

Set objImpRep = _
objImpApp.OpenReport("s:\imp71_reporting_tools\joe's reports\green book\" & _
"03-ORG Calendarized GB.imr","30.3|2")
objImpRep.RetrieveAll
objImpRep.Print

Set objImpRep = _
objImpApp.OpenReport("s:\imp71_reporting_tools\joe's reports\green book\" & _
"03-ORG Calendarized GB.imr","30.5|2")
objImpRep.RetrieveAll
objImpRep.Print

Set objImpRep = _
objImpApp.OpenReport("s:\imp71_reporting_tools\joe's reports\green book\" & _
"03-ORG Calendarized GB.imr","30.6|2")
objImpRep.RetrieveAll
objImpRep.Print

 

[griffindm]

Your response is unclear as to the problem you are having with the month prompt. I'll take a leap of faith and say it's probably because the month is a number. To handle this, I usually use a string-to-number conversion within the report, which allows the month to be passed from the macro as a string without problem.

Let us know if this is the problem, or give us more info.

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

 

[1hrllabs]

What I'd like to do is to only have to enter the month once, the first instance, and have the macro via a loop or some other means (I'm not a programmer, if that's not already obvious) enter the month number from the first instance into the remainder of the macro where the month is requested.

 

[griffindm]

x$ = "1"
org$ = "30.2"
pstring$ = org$+"|"+x

Set objImpRep = _
objImpApp.OpenReport("s:\imp71_reporting_tools\joe's reports\green book\" & _
"03-ORG Calendarized GB.imr",pstring$)
objImpRep.RetrieveAll
objImpRep.Print


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

http://www.decisionsupportgroup.com