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

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
------------------------------

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
------------------------------

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: 4/8/2021 10:28:00 AM
From: Thomas van der Meer
Subject: RE: 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
------------------------------

Disposable prompt values UPDATE: for Negative Prompts (do not include)
I figured out a solution.  On the Prompt Page Design, Under the General Tab, there is a "Default Selections" feature. 
If you put in a Default selection that is not an eye color and the user does not select a value, no records will match to be excluded.  I've called my default selection "No Selection Made".  
Recap,  This is just excluding prompt values.  Using my example of eye colors on my 2nd post.  When the user selects an eye color, the report will exclude all "Blue" eyes from the results.  Before the default value entry, if the user did not select any eye color value, the query would exclude all eye colors because a selection was not made.  By adding a bogus default value, the query does not return any values to exclude.  

Whew! I can't believe I figured this out.  Big thanks for those that tried to assist me with this project.  Hopefully someone else can use the technique to improve their report writing.

--- A very happy Report Developer

------------------------------
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
------------------------------

Hi Jeanie,

Have you tried conditional blocks?  For example, create a radio button value prompt with with 2 options (show prompts, hide prompts).  Set Auto Submit to True so that it refreshes the page when you change it.  Create a conditional block and use a Boolean variable to check the value of the radio button and use the result to show/hide the block.  You can add as many tables, prompts, etc. as you need inside this block and it will only render when you select "show prompts" from the radio button.

For all of your filters on the queries, set something like ?pRadioButon? = 'Hide" OR [whatever you want to filter from the prompts].  The OR clause will stop checking for the second condition if you are hiding the prompts.

Feel free to reach out to me via email if you want me to show you over a screen share.

-Todd

------------------------------
Todd Schuman
------------------------------

Like Todd mentioned, we use conditional blocks on our prompt pages to determine what fields are needed based on report type.  For example, As of Date, Official Reporting Report or Current Date.  Based on the Report Type, then it either prompts for As of Date, Reporting Period or nothing.

In the report, we create a query item based on the Reporting type and values entered in the conditional prompt and it is then used in the filter. 

------------------------------
Jo Marie Sellner
------------------------------