Hi Jeanie,
You have to make your attribute exclude prompts required. Then add a static choice 'NONE' and make this the default selection.
The required filter for the attribute queries is: [Attribuut Exclude Value] in ( ?pExcludeValue? )
The required filter for the main query is: [Attribuut Exclude ID] not in ( [qExcludeAttribute].[ID] )
I also tried using a promptmacro in the filter for the atteribute query:
[Attribuut Exclude Value] in ( #promptmany ( 'pExcludeValue'; 'string'; '''NONE''' )# )
But this works only the first time I run the report. When I ran the report the second time it did show the same result for the first run. I think this is a caching issue.
------------------------------
Thomas van der Meer
------------------------------
Original Message:
Sent: Fri April 09, 2021 12:40 PM
From: Jeanie Krieger
Subject: Disposable prompt values
Thomas,
We have a database with a lot of attribute tables where the common link is the ID field to the individual main record number. In this huge report I'm creating, I want to use the queries tab of the detailed filter expression to include/exclude different attributes of the database which filter a main results query. The alternative would be to join all of these tables into a large query where I could filter directly on the data items within the main query. Those kinds of queries have really poor processing time and I want to avoid that.
For example:
Include any degree year between 2015 and today. In the prompt page, there is an option of degree years. Choosing this filter allows the user to choose which degree years to include (optional). This filters the degree year query, a small query of IDs and degree year. In my main query, I have a filter that says Main Query ID includes all IDs from the degree year query. These are optional include filters and are easy and work well.
My challenge is I have another prompt page lists Exclude filters. So, let's say I have exclude prompt of eye color and the user selects they don't want anyone with blue eyes in the results. I have a query called eye color that is just ID and eye color, that filters on the records from the prompt selection. When I go back to my main query, it has an optional filter that reads: Main Query ID <> IDs from the eye color query (the user selected blue). This works. However, if my user does not care which eye color the record has, then that filter is going to exclude all eye colors.
I am looking for a way for prompt values to not exist unless a selection is made on the prompt page. Or I'm looking for another way to tackle this.
I apologize if this is confusing. Writing out an obscure example that people will understand is a challenge.
------------------------------
Jeanie Krieger
Business Analyst
California State University, Sacramento
Sacramento CA
Original Message:
Sent: Thu April 08, 2021 10:28 AM
From: Thomas van der Meer
Subject: Disposable prompt values
Hi Jeanie,
I'm not sure if I understand your problem correctly, but does your report work if you make the exclude prompt optional and add an optional filter to the report with the expression:
Code not in ( ?pExcludeValues? )
------------------------------
Thomas van der Meer
Original Message:
Sent: Tue April 06, 2021 06:27 PM
From: Jeanie Krieger
Subject: Disposable prompt values
I'm currently writing a report in Report Studio with Cognos 11.1. I've created two different prompt pages: Page 1 = Include prompts. ie: Between these dates, etc. Page 2 = Exclude prompts. Don't include records if they have XXX code assigned to their record, etc.
I'd like the user to be able to select from a queried list of the Exclude Prompt values. But, the default action on a prompt page is if a user does not select anything is to include all selections. I'd like the Exclude prompts to do the opposite. If nothing is selected filter by none of the options.
Is there a way to make a prompt not exist until a selection is made?
I've searched through the archives and Google and cannot find anything that will help me resolve this. I'd appreciate ideas from other developers.
------------------------------
Jeanie Krieger
Business Analyst
California State University, Sacramento
Sacramento CA
------------------------------
#CognosAnalyticswithWatson