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 Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Impromptu Prompts
Cognos Impromptu
This FAQ addresses the use of Prompts with Cognos Impromptu. It is maintained by griffindm. Suggestions for additions and corrections can be addressed to him via the 'Send a Comment' link below the FAQ. All suggestions are intended for the current release of Impromptu, unless otherwise noted.
Impromptu uses prompts to allow reports to be easily modified at run time without the need for authoring skills by the end user. Prompts can be used in a variety of places within the product. Commonly prompts are used to change filter values, to affect conditional formats, or to affect the values of calculated columns. Prompts may be created anywhere in the report where a constant is called for, appearing as the 'Prompt Manager' option in the 'Available Components' portion of the definition dialog box. Optionally you may add, change, delete, or reorder prompts from the menu option of 'Prompt Manager' under the 'Report' menu option.
The Prompt Definition dialog requires you to name the prompt, and to select both a source data 'Type' (either 'Type-in', 'File', 'Report', or Catalog'), and to select a 'Data Type' ('string','number','date','time','date-time','interval'). You may optionally add a default value and a message to appear in the prompt dialog when the report is run. The 'Advanced>>' button is to define the data-type to a database-level definition for use with calling stored procedures.
The source data types are defined here:
Type in - Any entry is allowed, but no list is offered and no validation other than to proper data type (i.e. date values correct, no characters in numbers, etc.).
File - This allows you to point to a file that contains plain ascii values delimited with line-feeds (i.e. each value on a different line). Users must select from the list only.
Report - This allows you to point to an existing report and selects an indicated query column from that report to generate a list of values the user must pick from. Any prompts in this report picklist will be executed before prompts in the final report!. This makes report picklists a valuable tool via creation of cascading report picklist prompts that lead users down a series of choices, each presenting a relatively small number of values offered. You can also have the prompt show a different column than the column used in the filter (such as Name vs. ID). Any reports chosen as a picklist must run from the same catalog as the final report!
Catalog - This allows you to point the values offered to any existing table in the catalog and select a column from it to produce a list of values the user must chose from. A distinct clause in run in the background so that only unique values are offered. Be careful not to point to a large data source on a non-indexed column as a considerable time may elapse as it determines the uniqueness. A better choice is to point to lookup tables only where possible rather than fact tables. You can also have the prompt show a different column than the column used in the filter (such as Name vs. ID). You cannot alter the sorting order, or filter out values. If you need to do this then report picklists may be a better choice.
Chosing Multiple Values
By using any prompt type except 'Type in' you may allow the end user to select more than one value offered by making the filter use an 'IN' clause. Holding the shift key allows a contiguous selection, and Ctrl allows multiple discrete values. Also pressing the first character of any value takes you to the first occurance in the picklist. If you have done this, and multiple values are still not allowed, look in the filters, conditional formats, and calculated columns to make sure the prompts is never used with an equality ("="). You can also use the menu Prompt Manager (under Reports) to see all the occurances where the prompt is used.
Picklist Size Limit
By default Impromptu restricts picklist size to the first 100 rows seen. This can be changed by locating the Impromptu.ini file (found in c:\Program Files\Cognos\cer2\bin by default) and adding a new item under the [Startup Options] section (or modifying the existing entry) as:
Picklist limit=xxx
where xxx is the new value to allow. Do NOT make this value unreasonably large, as it consumes resources. Also cascading picklists, noted above, may be a better choice to guide the user through selections.
General Picklist Limitations
Picklists cannot have dynamic defaults. This means that date prompts cannot default to the current date when run. There are programatic work-arounds, but you cannot simply have the default offered change to today's date.
Formatting of dates is very restricted. You have three options. The first is to accept the YYYY-MM-DD format offered by default. The second is to turn on use of regional settings. This will affect all date usage on the computers if it is different from the general needs of the user. The third is to set the prompt to a string and offer a guiding example in the message box. Then convert the string to a true date in the report for use with filters, etc.
Macros and Prompts
Macros can be used to call reports with prompts and to automatically fill in the prompts without further user interaction. This is done via an extra section at the end of the '.OpenReport' method, in the following format:
ImpApp.OpenReport(rptname,promptstrng)
Values for multiple prompts may be passed, separated by pipes '|',but all values must be sent as strings! This means that the report must have the prompts as string values and then convert them to other datatypes as required.
You may also pass multiple values to a single prompt that uses the'IN' operator by separating the values with commas. This can also be combined with passing to multiple prompts, keeping your pipes ('|') and commas separate.
Getting an ALL option in the Prompt Picklist
Often you will want to provide a list for the user to pick from, but also provide a way to quickly select all of the data without selecting all of the picklist items. This is important not only for convenience, but also for speed, as an overly long 'IN' clause in the SQL can hinder performance. If you are comfortable with basic SQL, the method to do this is to create a table with a single row and column. Then create a view combining data from your original picklist table with this column, as in
The leading space in the ' ALL' text pushes this option to the top of the list. Remember to add this view to your catalog before trying to use it in a report. If views do not appear when you try to add it, then close the catalog, go to the menu option 'Tools | Options' and uncheck the 'Retrieve database tables when editing catalog tables' checkbox. When you next try to add the view a new button will appear that lets you specify which database objects to include. Using this selected option in your report leads to the next topic:
Conditional Filter Statements
Occasionally you will want to have the report filter use different tables for filtering based on user input into prompts. While the 'If-Then-Else' option does not appear to be available at first glance, it actually is. To use it you must use it within an equality-type expression, as in:
(start of filter) and 1 = if(expression) then (1) else (0) ...
BE CAREFUL!. Depending on your database type and version this may force Impromptu to do all row filtering at the local computer! SQL Server can handle this type of filter using Case When SQL syntax. Oracle users should substitute the Decode statement for the If-Then-Else. Other database users should confirm whether or not their database processes an Impromptu If-Then-Else before using this technique.
An filter example using the ' ALL' option mentioned above would be:
(start of filter) and 1 = If (' ALL' in (?Custprompt?)) then (1) else if (Cust_id IN (?Custprompt?)) then (1) else (0) ...
See thread401-384839 for other examples of how to use this technique creatively.
Prompts and SubReports
When creating a report that uses subreports, add the main query prompts as calculated columns in the main query. This makes them available to all subreports for use in filtering and conditional formats. You do not have to make them visible in the main query frames.
Prompts and Report Flexibility
Prompts can be used in conditional formats to increase the utility of the report. Creating a Yes/No prompt for suppressing report detail can make a single report do both summary and detailed reporting using conditional formating and the Hide option.
Prompts can be used to dynamically change sort and group order, or to control column selection for report display using calculated columns based on prompt values. Prompts can make filters work on different columns dynamically based on prompt values.
The bottom line is, if you're not fully using the power of prompts in your report, you should be.
Please forward along suggestions for other prompt suggestions, tricks, or correctons to me for inclusion into the FAQ.
Regards,
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
Impromptu uses prompts to allow reports to be easily modified at run time without the need for authoring skills by the end user. Prompts can be used in a variety of places within the product. Commonly prompts are used to change filter values, to affect conditional formats, or to affect the values of calculated columns. Prompts may be created anywhere in the report where a constant is called for, appearing as the 'Prompt Manager' option in the 'Available Components' portion of the definition dialog box. Optionally you may add, change, delete, or reorder prompts from the menu option of 'Prompt Manager' under the 'Report' menu option.
The Prompt Definition dialog requires you to name the prompt, and to select both a source data 'Type' (either 'Type-in', 'File', 'Report', or Catalog'), and to select a 'Data Type' ('string','number','date','time','date-time','interval'). You may optionally add a default value and a message to appear in the prompt dialog when the report is run. The 'Advanced>>' button is to define the data-type to a database-level definition for use with calling stored procedures.
The source data types are defined here:
Type in - Any entry is allowed, but no list is offered and no validation other than to proper data type (i.e. date values correct, no characters in numbers, etc.).
File - This allows you to point to a file that contains plain ascii values delimited with line-feeds (i.e. each value on a different line). Users must select from the list only.
Report - This allows you to point to an existing report and selects an indicated query column from that report to generate a list of values the user must pick from. Any prompts in this report picklist will be executed before prompts in the final report!. This makes report picklists a valuable tool via creation of cascading report picklist prompts that lead users down a series of choices, each presenting a relatively small number of values offered. You can also have the prompt show a different column than the column used in the filter (such as Name vs. ID). Any reports chosen as a picklist must run from the same catalog as the final report!
Catalog - This allows you to point the values offered to any existing table in the catalog and select a column from it to produce a list of values the user must chose from. A distinct clause in run in the background so that only unique values are offered. Be careful not to point to a large data source on a non-indexed column as a considerable time may elapse as it determines the uniqueness. A better choice is to point to lookup tables only where possible rather than fact tables. You can also have the prompt show a different column than the column used in the filter (such as Name vs. ID). You cannot alter the sorting order, or filter out values. If you need to do this then report picklists may be a better choice.
Chosing Multiple Values
By using any prompt type except 'Type in' you may allow the end user to select more than one value offered by making the filter use an 'IN' clause. Holding the shift key allows a contiguous selection, and Ctrl allows multiple discrete values. Also pressing the first character of any value takes you to the first occurance in the picklist. If you have done this, and multiple values are still not allowed, look in the filters, conditional formats, and calculated columns to make sure the prompts is never used with an equality ("="). You can also use the menu Prompt Manager (under Reports) to see all the occurances where the prompt is used.
Picklist Size Limit
By default Impromptu restricts picklist size to the first 100 rows seen. This can be changed by locating the Impromptu.ini file (found in c:\Program Files\Cognos\cer2\bin by default) and adding a new item under the [Startup Options] section (or modifying the existing entry) as:
Picklist limit=xxx
where xxx is the new value to allow. Do NOT make this value unreasonably large, as it consumes resources. Also cascading picklists, noted above, may be a better choice to guide the user through selections.
General Picklist Limitations
Picklists cannot have dynamic defaults. This means that date prompts cannot default to the current date when run. There are programatic work-arounds, but you cannot simply have the default offered change to today's date.
Formatting of dates is very restricted. You have three options. The first is to accept the YYYY-MM-DD format offered by default. The second is to turn on use of regional settings. This will affect all date usage on the computers if it is different from the general needs of the user. The third is to set the prompt to a string and offer a guiding example in the message box. Then convert the string to a true date in the report for use with filters, etc.
Macros and Prompts
Macros can be used to call reports with prompts and to automatically fill in the prompts without further user interaction. This is done via an extra section at the end of the '.OpenReport' method, in the following format:
ImpApp.OpenReport(rptname,promptstrng)
Values for multiple prompts may be passed, separated by pipes '|',but all values must be sent as strings! This means that the report must have the prompts as string values and then convert them to other datatypes as required.
You may also pass multiple values to a single prompt that uses the'IN' operator by separating the values with commas. This can also be combined with passing to multiple prompts, keeping your pipes ('|') and commas separate.
Getting an ALL option in the Prompt Picklist
Often you will want to provide a list for the user to pick from, but also provide a way to quickly select all of the data without selecting all of the picklist items. This is important not only for convenience, but also for speed, as an overly long 'IN' clause in the SQL can hinder performance. If you are comfortable with basic SQL, the method to do this is to create a table with a single row and column. Then create a view combining data from your original picklist table with this column, as in
Code:
CREATE TABLE ONEROW (
TXTCOL1 VARCHAR2(10));
COMMIT;
CREATE VIEW ALL_CUSTOMERS AS
SELECT CUSTOMER FROM CUST
UNION
SELECT ' ALL' FROM ONER0W;
COMMIT;The leading space in the ' ALL' text pushes this option to the top of the list. Remember to add this view to your catalog before trying to use it in a report. If views do not appear when you try to add it, then close the catalog, go to the menu option 'Tools | Options' and uncheck the 'Retrieve database tables when editing catalog tables' checkbox. When you next try to add the view a new button will appear that lets you specify which database objects to include. Using this selected option in your report leads to the next topic:
Conditional Filter Statements
Occasionally you will want to have the report filter use different tables for filtering based on user input into prompts. While the 'If-Then-Else' option does not appear to be available at first glance, it actually is. To use it you must use it within an equality-type expression, as in:
(start of filter) and 1 = if(expression) then (1) else (0) ...
BE CAREFUL!. Depending on your database type and version this may force Impromptu to do all row filtering at the local computer! SQL Server can handle this type of filter using Case When SQL syntax. Oracle users should substitute the Decode statement for the If-Then-Else. Other database users should confirm whether or not their database processes an Impromptu If-Then-Else before using this technique.
An filter example using the ' ALL' option mentioned above would be:
(start of filter) and 1 = If (' ALL' in (?Custprompt?)) then (1) else if (Cust_id IN (?Custprompt?)) then (1) else (0) ...
See thread401-384839 for other examples of how to use this technique creatively.
Prompts and SubReports
When creating a report that uses subreports, add the main query prompts as calculated columns in the main query. This makes them available to all subreports for use in filtering and conditional formats. You do not have to make them visible in the main query frames.
Prompts and Report Flexibility
Prompts can be used in conditional formats to increase the utility of the report. Creating a Yes/No prompt for suppressing report detail can make a single report do both summary and detailed reporting using conditional formating and the Hide option.
Prompts can be used to dynamically change sort and group order, or to control column selection for report display using calculated columns based on prompt values. Prompts can make filters work on different columns dynamically based on prompt values.
The bottom line is, if you're not fully using the power of prompts in your report, you should be.
Please forward along suggestions for other prompt suggestions, tricks, or correctons to me for inclusion into the FAQ.
Regards,
Dave Griffin
The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"
www.decisionsupportgroup.com