auto selection of entire picklist prompt

[KellyK]

We have an Access database with a form that contains a button. Once this button is clicked, a series of Impromptu reports are run and then exported to Excel and emailed out. My question is, is there a way to automatically select all members of a picklist prompt without having to manually go in and select them? I realize I could add the filter to the report itself, but the file is already created so this would be a tedious process. Has anyone tried doing this?

Thanks in advance.

 

[griffindm]

Kelly,

This is an area that generates a lot of questions. I have seen ideas, such as creating a union view using an extra "ALL" record to appear at the top of the list. One approach that may work better in a macro tool is to use an extra prompt (Type-in: "Enter 'A' for All", default 'N') and use it with a conditional filter. Many people don't realize you can use an If-Then-Else statement in your filter if you set it within an equality check, such as:

..... And 1 = If (?All? = 'A') then (1) else if (member IN (?memlistprompt?) then (1) else (0) ...

This will work, but it may have performance issues depending on how much data is returned by the query.

Hope this helps,

Regards,

Dave Griffin

 

[KellyK]

Thanks Dave for the response, it got the ball rolling for me. Unfortunately I'm unable to get your code to work. The filter still only returns values that are highlighted within the prompt. What I want to do is programmically highlight ALL values in my list, without hard-coding the values into the filter itself. For instance if my picklist file contains the values:
1
2
3
4

I want the report to run and select 1,2,3,and 4 without me pressing shift and selecting all values.

Instead, number 1 is highlighted by default so when I use the conditional filter, I still only get results with value 1. Am I making any sense?

 

[quatermass]

Hi Kelly,

It's been a while since I have done this, but Dave's post jogged the memory...

What I can recall doing, and have not had the chance to try it again, was something along the lines of:

Make the filter on the report read

item field in (if(?All? = 'ALL') then (item field) else (?All?))

?All? being a type in prompt.

If ALL is typed in then item field should be in itself (ie will pick up everything). If something else is typed in, then that value is used as the filter.


Hope it makes some sense and help a little

Kevin **************************************************************
The difference between fiction and reality is that fiction has to make sense.
**************************************************************

 

[griffindm]

Kelly,

I may not have made this clear, but the equality check in my first post REPLACES your existing filter condition that uses your original prompt, which I referred to as
?memlistprompt?.

If your report is still filtering by the list, it seems that your existing filter condition is still in place.

The prompt ?All? is a new prompt that should be ordered to appear in the prompt dialog before your exiting prompt. If the user makes this prompt = 'A', then the new filter clause should bypass any use of the existing prompt, as it is satisfied by the first condition of the If-Then-Else (i.e. (?All? = 'A').

I hope this is clearer.

Regards,

Dave Griffin

 

[griffindm]

Kelly,

Another thought I had after I replied above. This approach will NOT explicitly include all the members of your picklist in the filter. It will BYPASS a check that a data rows has to contain a column member of the picklist to be included in the report. This is not a problem if the picklist is a catalog picklist and the list encompasses the entire universe of data. If the picklist is a report picklist, and that report contains other filtering criteria that still needs to be applied to the final report, you would still have to work that logic into the equality check shown above.

Probably not a problem, but I just wanted to clarify how this works.

Dave Griffin

 

[KellyK]

Dave, thank you for all the attention you've given my post, I really appreciate the help. I am new to Cognos, so I'm probably asking things that an intermediate user would already know. However I think we are not on the same page as far as what I'm trying to do here. In essence, I would like not to *bypass* my prompt, but to select all values in the file picklist, not the catalog. My filter is like this:

... and Vendor Id not in ( ?Carrier List? )

where ?Carrier List? is a FILE picklist containing our "approved carriers". This report is meant to show all records with an unapproved carrier, therefore I want to select ALL records in the file. Since this report is run and sent out through automation (VBA within Access), I don't want to have to highlight all members of my picklist. If I bypass my original prompt, this report will simply show me all records, not just the ones with unapproved carriers. Since we have a few hundred approved carriers, I don't want to have to manually enter these into the filter because a file has already been created.

sorry to beat this to death.

quatermass, thanks to you as well.

Kelly

 

[griffindm]

Thanks for clarifying Kelly, I was confusing the picklist question here with another post you have going relating to the Oracle error on a catalog picklist.

Sounds like you may have an automated process that creates the the approved carrier list. Anyway you could get this to be loaded in the same database as your report datasource? If so the logic to look for carriers not in your list would be easier to achive without intervention. This approach done directly in Impromptu would require an outer join to look for rows without a match in the 'good carrier' table, which may entail a performance hit. If you could get the data into the database, another approach might be a database view using a sub select, as in

CREATE VIEW BAD_CARRIERS AS
SELECT CARRIER, etc FROM TABLE1
WHERE CARRIER NOT IN
(SELECT CARRIER FROM EXCL_CARRIERS);

[language may vary depending on your data source]

The alternatives are to try to kludge something with SendKeys, or to use a text ODBC driver to try to create a hotfile you could include with your report.

Sorry for misreading your intent in the report. It's always dangerous, but often necessary to make assumptions about what someone is trying to accomplish.

Regards,

Dave Griffin