Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Macro Use Overview
Macros
This is a short non-technical overview of the uses of macros to solve reporting problems. It is geared to both technical and non-technical users and gives the foundations of the decision-making process to determine whether a macro approach would be beneficial to the particular problem at hand. It also provides examples of the many types of macros that can be designed.
Introduction
A macro, in the context of Cognos products, is a set of Visual Basic-like code entered, debugged, and compiled using the CognosScript macro editor that ships with both Impromptu and Powerplay. Many examples of macros are included with the product in an Acrobat file. The editor provides excellent help, with examples of sample code using each of the functions available in the product.
Why Use Macros?
Macros are used to overcome limitations of the reporting environment using only Impromptu or Powerplay by themselves. They extend the possibilities of what you can accomplish by providing:
1. A customizable interface for prompts. Prompts may be presented in different layout than the standard Impromptu interface. Dates may be entered in any format and then converted in both the macro and Impromptu to the necessary format for the report. You may even load a menuing interface to allow users to select from multiple reports or activities.
2. The ability to load multiple reports in sequence. This is very useful for running reports to generate hotfiles for use in subsequent reports, automating this process. Using arrays (a memory loaded list) macros can repeat the same task for any number of items, such as variables for report prompts. Coupled with the ability to create output (item 3 below) this can automate many tasks,
3. The ability to automate printing of reports or creation of output files. When a large report needs to be run to output (either printed, Acrobat, Excel, or HTML) the macro can run this task for you. Combining this with Scheduler, another tool provided by Cognos, and the process can run unattended on a regular schedule.
4. The ability to call database-driven activity. This means that if you have designed stored procedures in your database, macros can be used to create custom interfaces to call them. Stored procedures, containing SQL code stored and operated on the database server, are extremely useful in updating reporting tables that simplify the reporting tasks for Impromptu. In addition to ImpromptuÆs stored procedure report template, ODBC calls to work with data are provided in the macro language. These can provide alternative methods to populate macro prompt boxes or to perform many other tasks.
5. The ability to link applications. Macros can open many Windows applications and do tasks between them. Examples might include running an Impromptu report to determine a critical value, then opening an Excel spreadsheet and entering that value into the spreadsheet and saving it.
6. Low-Level File Input/Output. Macros allow you to work with ASCII files as either input or output. This makes the macro an excellent tool in creating custom interfaces between applications. You can have precise control over output specifications, crucial for following the input file layouts of other applications for data uploading. Also, unlike the SaveAs Text option of Impromptu, you can have header and detail lines in your output having different layouts.
7. Automate catalog or cube activity. Macros allow the use of specific application calls to Impromptu and Powerplay that allow you to perform tasks, such as changes to user classes or updating of cubes. In some cases macros may be the only way to automate certain tasks, such as printing reports for all user classes of a catalog.
Macro Design Tips
In writing a macro, keep several things in mind. First, almost no one writes a macro from scratch. Take an existing macro that contains some of the elements you will need and extend it to serve your purposes. Next, you should always be able to sketch out the steps your macro will need to perform, in the proper sequence, to perform your task before you start actually coding the macro. Many of these steps can be tested outside of the macro, such as intermediate report design, or database SQL. Other steps can only be tested within the context of the macro itself.
Where ever possible, use standard code that can be called multiple times to perform the same type of step more than once. This makes the macro more scalable, easier to decode, and shorter in length.
Use error trapping to handle exceptions, and provide a meaningful error message. Things will go wrong at times, and well written error traps help you to fix things faster.
Encrypt any passwords for catalogs or databases. While the macro is compiled as a MCX file, this file contains a LOT of clear text, including passwords. Simple encryption techniques prevent unwanted eyes from seeing avenues to access data from outside the constraints of the macro or catalog security.
Document changes within the macro. You may only work on the macro every six months or more. Well written and maintained internal documentation as to what the macro does and how it has been changed can save greatly in ongoing maintenance.
This FAQ is maintained by griffindm. Use the 'Send a Comment' link below the FAQ to send me suggestions for changes, corrections, or enhancements to this FAQ.
Dave Griffin
The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"![[pc2]](/data/assets/smilies/pc2.gif "[pc2] [pc2]")
www.decisionsupportgroup.com
Introduction
A macro, in the context of Cognos products, is a set of Visual Basic-like code entered, debugged, and compiled using the CognosScript macro editor that ships with both Impromptu and Powerplay. Many examples of macros are included with the product in an Acrobat file. The editor provides excellent help, with examples of sample code using each of the functions available in the product.
Why Use Macros?
Macros are used to overcome limitations of the reporting environment using only Impromptu or Powerplay by themselves. They extend the possibilities of what you can accomplish by providing:
1. A customizable interface for prompts. Prompts may be presented in different layout than the standard Impromptu interface. Dates may be entered in any format and then converted in both the macro and Impromptu to the necessary format for the report. You may even load a menuing interface to allow users to select from multiple reports or activities.
2. The ability to load multiple reports in sequence. This is very useful for running reports to generate hotfiles for use in subsequent reports, automating this process. Using arrays (a memory loaded list) macros can repeat the same task for any number of items, such as variables for report prompts. Coupled with the ability to create output (item 3 below) this can automate many tasks,
3. The ability to automate printing of reports or creation of output files. When a large report needs to be run to output (either printed, Acrobat, Excel, or HTML) the macro can run this task for you. Combining this with Scheduler, another tool provided by Cognos, and the process can run unattended on a regular schedule.
4. The ability to call database-driven activity. This means that if you have designed stored procedures in your database, macros can be used to create custom interfaces to call them. Stored procedures, containing SQL code stored and operated on the database server, are extremely useful in updating reporting tables that simplify the reporting tasks for Impromptu. In addition to ImpromptuÆs stored procedure report template, ODBC calls to work with data are provided in the macro language. These can provide alternative methods to populate macro prompt boxes or to perform many other tasks.
5. The ability to link applications. Macros can open many Windows applications and do tasks between them. Examples might include running an Impromptu report to determine a critical value, then opening an Excel spreadsheet and entering that value into the spreadsheet and saving it.
6. Low-Level File Input/Output. Macros allow you to work with ASCII files as either input or output. This makes the macro an excellent tool in creating custom interfaces between applications. You can have precise control over output specifications, crucial for following the input file layouts of other applications for data uploading. Also, unlike the SaveAs Text option of Impromptu, you can have header and detail lines in your output having different layouts.
7. Automate catalog or cube activity. Macros allow the use of specific application calls to Impromptu and Powerplay that allow you to perform tasks, such as changes to user classes or updating of cubes. In some cases macros may be the only way to automate certain tasks, such as printing reports for all user classes of a catalog.
Macro Design Tips
In writing a macro, keep several things in mind. First, almost no one writes a macro from scratch. Take an existing macro that contains some of the elements you will need and extend it to serve your purposes. Next, you should always be able to sketch out the steps your macro will need to perform, in the proper sequence, to perform your task before you start actually coding the macro. Many of these steps can be tested outside of the macro, such as intermediate report design, or database SQL. Other steps can only be tested within the context of the macro itself.
Where ever possible, use standard code that can be called multiple times to perform the same type of step more than once. This makes the macro more scalable, easier to decode, and shorter in length.
Use error trapping to handle exceptions, and provide a meaningful error message. Things will go wrong at times, and well written error traps help you to fix things faster.
Encrypt any passwords for catalogs or databases. While the macro is compiled as a MCX file, this file contains a LOT of clear text, including passwords. Simple encryption techniques prevent unwanted eyes from seeing avenues to access data from outside the constraints of the macro or catalog security.
Document changes within the macro. You may only work on the macro every six months or more. Well written and maintained internal documentation as to what the macro does and how it has been changed can save greatly in ongoing maintenance.
This FAQ is maintained by griffindm. Use the 'Send a Comment' link below the FAQ to send me suggestions for changes, corrections, or enhancements to this FAQ.
Dave Griffin
The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"
www.decisionsupportgroup.com