Macro to run multiple reports at one time? 1

[leblancfpt]

I am brand new to Cognos. We are planning on using Impromptu for sales reports, and I am looking for the easiest ways for our field sales reps to get the reports "on demand." I know they could get in, run each report, and then download each report to their laptops, but would like to give them an easier way. There are about 5 reports they would like run and downloaded (Excel or PDF) each time they get in. If there is any easy way to let them control which reports are part of the macro that would be an added bonus. If a macro can do it, do I need a separate one for each user? There are several hundred reps so there is ongoing maintenance of the user list. Some are relatively novice users. The report formats are the same for all users, but each rep will only see their territory information when they run each report.

Thanks
Marcia
Application Lead Sales & Marketing
Bausch & Lomb
Rochester NY

 

[leblancfpt]

It looks like we will be using PowerPlay for the reports I am interested in, so please let me know if the same answer would apply for Impromptu and PowerPlay.

 

[drlex]

Marcia,
Your requirements would be suited by a macro. There was a similar thread to your situation within the last 10 days or so, where Dave Griffin (the main man here) pointed out that an array would be the best way to load requirements in so that you could then loop the macro to run the relevant report for the relevant representative. Look back on the last couple of pages or use search for array or macro.

I do something similar (albeit on not the same scale as you require), whereby excel versions are saved to our PDA server for transfer to the salesmen, using a csv file to hold the information required and impromptu to extract it and load an array (altho' one can read a flat file direct into an array in a macro, I like the filtering possibilities in using impromptu).

Given the number and varying experience of your reps, it would be wise to make sure the csv file is only available to one person to maintain - otherwise it's only a matter of time before someone screws it up.

I can post some wildly inelegant code, but I don't want to embarrass myself and take up bandwidth, so I'd email it only!

lex

 

[leblancfpt]

Lex,

Thank you so much for the information. I would love to see a copy of your code (marcia_leblanc@bausch.com). Do you know if there is a way for me to make the macro available to each sales user, or would I have to have separate copies for each person? Can you put a macro in a folder and run it from there?

Marcia

 

[drlex]

Marcia,
Our Exchange system is out of kilter today, so I'll copy the macro and email it from home - use of web mail is forbidden here!

Rather than rely on user activation, my macro runs for all users, as the user details form part of the prompt for the report and also the destination for the saved version. It is called by Cognos scheduler so all reports are generated early in the morning for later access by the users. If you have laptop users with remote access, this can be a time saver. Our fellows have iPaqs on GRPS connections, so having the files ready to go saves plenty of time.

The macro obviously needs to run on the PC or server with the cognos installation and saves the reports to the users' folders on another server. If on a server, one can use SMTP for mailing (to avoid the problems with Exchange denying other programs automail facilities).

At present it calls impromptu reports, but there's no reason why one can't open Powerplay reports tailored to each user (the name of each being part of the array) or one generic report which is then drilled/filtered to the relevant user level within the macro, again using information from the array.

lex

 

[drlex]

Marcia,
did you receive the email or did I manage to mangle your email address?
lex

 

[leblancfpt]

Lex,

Yes, I did receive your mail. Thank you so much for sending it from home. I think it will be a very good starting point.

Really appreciate the help!

Marcia

 

[drlex]

Marcia,
You're welcome - I hope my code isn't too bizarre to trace through.
As a further tip, the help files that come with the Script Editor are a good source of information about the available commands and their syntax, together with some good examples.
Where it gets a bit tricky is calling other (non-Cognos) applications. Knowledge of Visual Basic is a boon, but there appear to be some differences that make coding aggravating rather than rewarding.
Post back here if you've got more Qs.
Lex