Hello,

I have a Company Number prompt, and I'd like some insight on the best option to automatically have the prompt default to Company Number < 144 if the user does NOT make a selection.


Thanks,
Jen

------------------------------
Jenifer Broughton
------------------------------

#CognosAnalyticswithWatson

Hi Jenifer,

one of my quick but dirty solutions: I usually use a dummy value that doesn't exist in the data E.G. "-1". Set this to the default value for the prompt and make the prompt mandatory.

The filter in your query should then look like this (on demo data):

([Sales (query)].[Sales order].[Order number]<100020 AND ?orderno?=-1) OR
[Sales (query)].[Sales order].[Order number] = ?orderno?

If the users don't change the value, the query gets filtered by Order number <100020. If there's a number, then this order will be filtered.
The down side here is that the user will see "-1" as default selection.

A second but more elaborate solution is to work with conditional blocks.
Create a boolean variable pointing to your parameter:
Duplicate your query and set one filtered to [data]=?parameter? and the second one to [data]<144.

Insert a conditional block  and link the variable just created to the block.

Then you "just" have to put one content from the dynamically filtered query into the "No" block and another content from the fixed filtered query into the "Yes" block.Works but you have to maintain double the queries and content - depending om how your report should look like and what it contains.

Br

------------------------------
Robert Dostal
Team Leader BI
GEMÜ
Ingelfingen
------------------------------

Original Message:
Sent: Mon August 01, 2022 08:23 AM
From: Jenifer Broughton
Subject: Cognos Prompt Default Criteria question

Hello,

I have a Company Number prompt, and I'd like some insight on the best option to automatically have the prompt default to Company Number < 144 if the user does NOT make a selection.


Thanks,
Jen

------------------------------
Jenifer Broughton
------------------------------

#CognosAnalyticswithWatson

Hi Robert,

Thank you so much for the quick assist.  I need to use a value prompt.  I tried what you recommended with the 1st option (as I do not want to maintain 2 reports).  I was not able to get it to work.  I do not see the -1 in the Company Prompt list.

Thanks,
Jen

------------------------------
Jenifer Broughton
------------------------------

Original Message:
Sent: Mon August 01, 2022 09:07 AM
From: Robert Dostal
Subject: Cognos Prompt Default Criteria question

Hi Jenifer,

one of my quick but dirty solutions: I usually use a dummy value that doesn't exist in the data E.G. "-1". Set this to the default value for the prompt and make the prompt mandatory.

The filter in your query should then look like this (on demo data):

([Sales (query)].[Sales order].[Order number]<100020 AND ?orderno?=-1) OR
[Sales (query)].[Sales order].[Order number] = ?orderno?

If the users don't change the value, the query gets filtered by Order number <100020. If there's a number, then this order will be filtered.
The down side here is that the user will see "-1" as default selection.

A second but more elaborate solution is to work with conditional blocks.
Create a boolean variable pointing to your parameter:
Duplicate your query and set one filtered to [data]=?parameter? and the second one to [data]<144.

Insert a conditional block  and link the variable just created to the block.

Then you "just" have to put one content from the dynamically filtered query into the "No" block and another content from the fixed filtered query into the "Yes" block.Works but you have to maintain double the queries and content - depending om how your report should look like and what it contains.

Br

------------------------------
Robert Dostal
Team Leader BI
GEMÜ
Ingelfingen

Original Message:
Sent: Mon August 01, 2022 08:23 AM
From: Jenifer Broughton
Subject: Cognos Prompt Default Criteria question

Hello,

I have a Company Number prompt, and I'd like some insight on the best option to automatically have the prompt default to Company Number < 144 if the user does NOT make a selection.


Thanks,
Jen

------------------------------
Jenifer Broughton
------------------------------
#CognosAnalyticswithWatson

Try setting your filter like this:

[Namespace].[Table].[Company Number] #prompt('Company Number','integer','< 144',' = ')#​

The prompt macro has the following functions:
prompt/many(

1. Parameter Name
2. Data Type
3. Default Value
4. Fragment before parameter
5. Source for generated prompt
6. Fragment after parameter

)

In this case the operator is inside the macro function, first as part of the default value. If the user does provide a value to the parameter, the function will automatically prepend the = before the number.

------------------------------
Paul Mendelson
------------------------------

Original Message:
Sent: Mon August 01, 2022 10:01 AM
From: Jenifer Broughton
Subject: Cognos Prompt Default Criteria question

Hi Robert,

Thank you so much for the quick assist.  I need to use a value prompt.  I tried what you recommended with the 1st option (as I do not want to maintain 2 reports).  I was not able to get it to work.  I do not see the -1 in the Company Prompt list.

Thanks,
Jen

------------------------------
Jenifer Broughton

Original Message:
Sent: Mon August 01, 2022 09:07 AM
From: Robert Dostal
Subject: Cognos Prompt Default Criteria question

Hi Jenifer,

one of my quick but dirty solutions: I usually use a dummy value that doesn't exist in the data E.G. "-1". Set this to the default value for the prompt and make the prompt mandatory.

The filter in your query should then look like this (on demo data):

([Sales (query)].[Sales order].[Order number]<100020 AND ?orderno?=-1) OR
[Sales (query)].[Sales order].[Order number] = ?orderno?

If the users don't change the value, the query gets filtered by Order number <100020. If there's a number, then this order will be filtered.
The down side here is that the user will see "-1" as default selection.

A second but more elaborate solution is to work with conditional blocks.
Create a boolean variable pointing to your parameter:
Duplicate your query and set one filtered to [data]=?parameter? and the second one to [data]<144.

Insert a conditional block  and link the variable just created to the block.

Then you "just" have to put one content from the dynamically filtered query into the "No" block and another content from the fixed filtered query into the "Yes" block.Works but you have to maintain double the queries and content - depending om how your report should look like and what it contains.

Br

------------------------------
Robert Dostal
Team Leader BI
GEMÜ
Ingelfingen

Original Message:
Sent: Mon August 01, 2022 08:23 AM
From: Jenifer Broughton
Subject: Cognos Prompt Default Criteria question

Hello,

I have a Company Number prompt, and I'd like some insight on the best option to automatically have the prompt default to Company Number < 144 if the user does NOT make a selection.


Thanks,
Jen

------------------------------
Jenifer Broughton
------------------------------
#CognosAnalyticswithWatson

Thanks Paul.  

I will give this a try!


Original Message:
Sent: Mon August 01, 2022 10:29 AM
From: Paul Mendelson
Subject: Cognos Prompt Default Criteria question

Try setting your filter like this:

[Namespace].[Table].[Company Number] #prompt('Company Number','integer','< 144',' = ')#​

The prompt macro has the following functions:
prompt/many(

1. Parameter Name
2. Data Type
3. Default Value
4. Fragment before parameter
5. Source for generated prompt
6. Fragment after parameter

)

In this case the operator is inside the macro function, first as part of the default value. If the user does provide a value to the parameter, the function will automatically prepend the = before the number.

------------------------------
Paul Mendelson

Original Message:
Sent: Mon August 01, 2022 10:01 AM
From: Jenifer Broughton
Subject: Cognos Prompt Default Criteria question

Hi Robert,

Thank you so much for the quick assist.  I need to use a value prompt.  I tried what you recommended with the 1st option (as I do not want to maintain 2 reports).  I was not able to get it to work.  I do not see the -1 in the Company Prompt list.

Thanks,
Jen

------------------------------
Jenifer Broughton

Original Message:
Sent: Mon August 01, 2022 09:07 AM
From: Robert Dostal
Subject: Cognos Prompt Default Criteria question

Hi Jenifer,

one of my quick but dirty solutions: I usually use a dummy value that doesn't exist in the data E.G. "-1". Set this to the default value for the prompt and make the prompt mandatory.

The filter in your query should then look like this (on demo data):

([Sales (query)].[Sales order].[Order number]<100020 AND ?orderno?=-1) OR
[Sales (query)].[Sales order].[Order number] = ?orderno?

If the users don't change the value, the query gets filtered by Order number <100020. If there's a number, then this order will be filtered.
The down side here is that the user will see "-1" as default selection.

A second but more elaborate solution is to work with conditional blocks.
Create a boolean variable pointing to your parameter:
Duplicate your query and set one filtered to [data]=?parameter? and the second one to [data]<144.

Insert a conditional block  and link the variable just created to the block.

Then you "just" have to put one content from the dynamically filtered query into the "No" block and another content from the fixed filtered query into the "Yes" block.Works but you have to maintain double the queries and content - depending om how your report should look like and what it contains.

Br

------------------------------
Robert Dostal
Team Leader BI
GEMÜ
Ingelfingen

Original Message:
Sent: Mon August 01, 2022 08:23 AM
From: Jenifer Broughton
Subject: Cognos Prompt Default Criteria question

Hello,

I have a Company Number prompt, and I'd like some insight on the best option to automatically have the prompt default to Company Number < 144 if the user does NOT make a selection.


Thanks,
Jen

------------------------------
Jenifer Broughton
------------------------------
#CognosAnalyticswithWatson

@Paul Mendelson

I still was not able to prompt macro to work.
The Company is a string.  I tried to make the appropriate changes, but had no luck.  Also, the company prompt is located on a prompt page with several other prompts.

Thanks,
Jen​

------------------------------
Jenifer Broughton
------------------------------

Original Message:
Sent: Mon August 01, 2022 10:29 AM
From: Paul Mendelson
Subject: Cognos Prompt Default Criteria question

Try setting your filter like this:

[Namespace].[Table].[Company Number] #prompt('Company Number','integer','< 144',' = ')#​

The prompt macro has the following functions:
prompt/many(

1. Parameter Name
2. Data Type
3. Default Value
4. Fragment before parameter
5. Source for generated prompt
6. Fragment after parameter

)

In this case the operator is inside the macro function, first as part of the default value. If the user does provide a value to the parameter, the function will automatically prepend the = before the number.

------------------------------
Paul Mendelson

Original Message:
Sent: Mon August 01, 2022 10:01 AM
From: Jenifer Broughton
Subject: Cognos Prompt Default Criteria question

Hi Robert,

Thank you so much for the quick assist.  I need to use a value prompt.  I tried what you recommended with the 1st option (as I do not want to maintain 2 reports).  I was not able to get it to work.  I do not see the -1 in the Company Prompt list.

Thanks,
Jen

------------------------------
Jenifer Broughton

Original Message:
Sent: Mon August 01, 2022 09:07 AM
From: Robert Dostal
Subject: Cognos Prompt Default Criteria question

Hi Jenifer,

one of my quick but dirty solutions: I usually use a dummy value that doesn't exist in the data E.G. "-1". Set this to the default value for the prompt and make the prompt mandatory.

The filter in your query should then look like this (on demo data):

([Sales (query)].[Sales order].[Order number]<100020 AND ?orderno?=-1) OR
[Sales (query)].[Sales order].[Order number] = ?orderno?

If the users don't change the value, the query gets filtered by Order number <100020. If there's a number, then this order will be filtered.
The down side here is that the user will see "-1" as default selection.

A second but more elaborate solution is to work with conditional blocks.
Create a boolean variable pointing to your parameter:
Duplicate your query and set one filtered to [data]=?parameter? and the second one to [data]<144.

Insert a conditional block  and link the variable just created to the block.

Then you "just" have to put one content from the dynamically filtered query into the "No" block and another content from the fixed filtered query into the "Yes" block.Works but you have to maintain double the queries and content - depending om how your report should look like and what it contains.

Br

------------------------------
Robert Dostal
Team Leader BI
GEMÜ
Ingelfingen

Original Message:
Sent: Mon August 01, 2022 08:23 AM
From: Jenifer Broughton
Subject: Cognos Prompt Default Criteria question

Hello,

I have a Company Number prompt, and I'd like some insight on the best option to automatically have the prompt default to Company Number < 144 if the user does NOT make a selection.


Thanks,
Jen

------------------------------
Jenifer Broughton
------------------------------
#CognosAnalyticswithWatson

Just to be clear - the company number you're using is a string? Can you paste an example? 

The filter doesn't affect the prompt at all - you should only use it on the report query.

------------------------------
Paul Mendelson
------------------------------

Original Message:
Sent: Tue August 02, 2022 03:31 PM
From: Jenifer Broughton
Subject: Cognos Prompt Default Criteria question

@Paul Mendelson

I still was not able to prompt macro to work.
The Company is a string.  I tried to make the appropriate changes, but had no luck.  Also, the company prompt is located on a prompt page with several other prompts.

Thanks,
Jen​

------------------------------
Jenifer Broughton

Original Message:
Sent: Mon August 01, 2022 10:29 AM
From: Paul Mendelson
Subject: Cognos Prompt Default Criteria question

Try setting your filter like this:

[Namespace].[Table].[Company Number] #prompt('Company Number','integer','< 144',' = ')#​

The prompt macro has the following functions:
prompt/many(

1. Parameter Name
2. Data Type
3. Default Value
4. Fragment before parameter
5. Source for generated prompt
6. Fragment after parameter

)

In this case the operator is inside the macro function, first as part of the default value. If the user does provide a value to the parameter, the function will automatically prepend the = before the number.

------------------------------
Paul Mendelson

Original Message:
Sent: Mon August 01, 2022 10:01 AM
From: Jenifer Broughton
Subject: Cognos Prompt Default Criteria question

Hi Robert,

Thank you so much for the quick assist.  I need to use a value prompt.  I tried what you recommended with the 1st option (as I do not want to maintain 2 reports).  I was not able to get it to work.  I do not see the -1 in the Company Prompt list.

Thanks,
Jen

------------------------------
Jenifer Broughton

Original Message:
Sent: Mon August 01, 2022 09:07 AM
From: Robert Dostal
Subject: Cognos Prompt Default Criteria question

Hi Jenifer,

one of my quick but dirty solutions: I usually use a dummy value that doesn't exist in the data E.G. "-1". Set this to the default value for the prompt and make the prompt mandatory.

The filter in your query should then look like this (on demo data):

([Sales (query)].[Sales order].[Order number]<100020 AND ?orderno?=-1) OR
[Sales (query)].[Sales order].[Order number] = ?orderno?

If the users don't change the value, the query gets filtered by Order number <100020. If there's a number, then this order will be filtered.
The down side here is that the user will see "-1" as default selection.

A second but more elaborate solution is to work with conditional blocks.
Create a boolean variable pointing to your parameter:
Duplicate your query and set one filtered to [data]=?parameter? and the second one to [data]<144.

Insert a conditional block  and link the variable just created to the block.

Then you "just" have to put one content from the dynamically filtered query into the "No" block and another content from the fixed filtered query into the "Yes" block.Works but you have to maintain double the queries and content - depending om how your report should look like and what it contains.

Br

------------------------------
Robert Dostal
Team Leader BI
GEMÜ
Ingelfingen

Original Message:
Sent: Mon August 01, 2022 08:23 AM
From: Jenifer Broughton
Subject: Cognos Prompt Default Criteria question

Hello,

I have a Company Number prompt, and I'd like some insight on the best option to automatically have the prompt default to Company Number < 144 if the user does NOT make a selection.


Thanks,
Jen

------------------------------
Jenifer Broughton
------------------------------
#CognosAnalyticswithWatson

I've tired this in a filter for the query.  But get a QE-DEF-0260 Parsing error and QE-DEF-0261 QFWP Parsing text error.

[namespace_Company] = #prompt('p_company', '< 00143', '=')#​

Thanks,
Jen

------------------------------
Jenifer Broughton
------------------------------

Original Message:
Sent: Mon August 01, 2022 10:29 AM
From: Paul Mendelson
Subject: Cognos Prompt Default Criteria question

Try setting your filter like this:

[Namespace].[Table].[Company Number] #prompt('Company Number','integer','< 144',' = ')#​

The prompt macro has the following functions:
prompt/many(

1. Parameter Name
2. Data Type
3. Default Value
4. Fragment before parameter
5. Source for generated prompt
6. Fragment after parameter

)

In this case the operator is inside the macro function, first as part of the default value. If the user does provide a value to the parameter, the function will automatically prepend the = before the number.

------------------------------
Paul Mendelson

Original Message:
Sent: Mon August 01, 2022 10:01 AM
From: Jenifer Broughton
Subject: Cognos Prompt Default Criteria question

Hi Robert,

Thank you so much for the quick assist.  I need to use a value prompt.  I tried what you recommended with the 1st option (as I do not want to maintain 2 reports).  I was not able to get it to work.  I do not see the -1 in the Company Prompt list.

Thanks,
Jen

------------------------------
Jenifer Broughton

Original Message:
Sent: Mon August 01, 2022 09:07 AM
From: Robert Dostal
Subject: Cognos Prompt Default Criteria question

Hi Jenifer,

one of my quick but dirty solutions: I usually use a dummy value that doesn't exist in the data E.G. "-1". Set this to the default value for the prompt and make the prompt mandatory.

The filter in your query should then look like this (on demo data):

([Sales (query)].[Sales order].[Order number]<100020 AND ?orderno?=-1) OR
[Sales (query)].[Sales order].[Order number] = ?orderno?

If the users don't change the value, the query gets filtered by Order number <100020. If there's a number, then this order will be filtered.
The down side here is that the user will see "-1" as default selection.

A second but more elaborate solution is to work with conditional blocks.
Create a boolean variable pointing to your parameter:
Duplicate your query and set one filtered to [data]=?parameter? and the second one to [data]<144.

Insert a conditional block  and link the variable just created to the block.

Then you "just" have to put one content from the dynamically filtered query into the "No" block and another content from the fixed filtered query into the "Yes" block.Works but you have to maintain double the queries and content - depending om how your report should look like and what it contains.

Br

------------------------------
Robert Dostal
Team Leader BI
GEMÜ
Ingelfingen

Original Message:
Sent: Mon August 01, 2022 08:23 AM
From: Jenifer Broughton
Subject: Cognos Prompt Default Criteria question

Hello,

I have a Company Number prompt, and I'd like some insight on the best option to automatically have the prompt default to Company Number < 144 if the user does NOT make a selection.


Thanks,
Jen

------------------------------
Jenifer Broughton
------------------------------
#CognosAnalyticswithWatson

Try:

[namespace_Company]  #prompt('p_company','string', '< 00143', '=')#​



------------------------------
Paul Mendelson
------------------------------

Original Message:
Sent: Fri August 12, 2022 09:35 AM
From: Jenifer Broughton
Subject: Cognos Prompt Default Criteria question

I've tired this in a filter for the query.  But get a QE-DEF-0260 Parsing error and QE-DEF-0261 QFWP Parsing text error.

[namespace_Company] = #prompt('p_company', '< 00143', '=')#​

Thanks,
Jen

------------------------------
Jenifer Broughton

Original Message:
Sent: Mon August 01, 2022 10:29 AM
From: Paul Mendelson
Subject: Cognos Prompt Default Criteria question

Try setting your filter like this:

[Namespace].[Table].[Company Number] #prompt('Company Number','integer','< 144',' = ')#​

The prompt macro has the following functions:
prompt/many(

1. Parameter Name
2. Data Type
3. Default Value
4. Fragment before parameter
5. Source for generated prompt
6. Fragment after parameter

)

In this case the operator is inside the macro function, first as part of the default value. If the user does provide a value to the parameter, the function will automatically prepend the = before the number.

------------------------------
Paul Mendelson

Original Message:
Sent: Mon August 01, 2022 10:01 AM
From: Jenifer Broughton
Subject: Cognos Prompt Default Criteria question

Hi Robert,

Thank you so much for the quick assist.  I need to use a value prompt.  I tried what you recommended with the 1st option (as I do not want to maintain 2 reports).  I was not able to get it to work.  I do not see the -1 in the Company Prompt list.

Thanks,
Jen

------------------------------
Jenifer Broughton

Original Message:
Sent: Mon August 01, 2022 09:07 AM
From: Robert Dostal
Subject: Cognos Prompt Default Criteria question

Hi Jenifer,

one of my quick but dirty solutions: I usually use a dummy value that doesn't exist in the data E.G. "-1". Set this to the default value for the prompt and make the prompt mandatory.

The filter in your query should then look like this (on demo data):

([Sales (query)].[Sales order].[Order number]<100020 AND ?orderno?=-1) OR
[Sales (query)].[Sales order].[Order number] = ?orderno?

If the users don't change the value, the query gets filtered by Order number <100020. If there's a number, then this order will be filtered.
The down side here is that the user will see "-1" as default selection.

A second but more elaborate solution is to work with conditional blocks.
Create a boolean variable pointing to your parameter:
Duplicate your query and set one filtered to [data]=?parameter? and the second one to [data]<144.

Insert a conditional block  and link the variable just created to the block.

Then you "just" have to put one content from the dynamically filtered query into the "No" block and another content from the fixed filtered query into the "Yes" block.Works but you have to maintain double the queries and content - depending om how your report should look like and what it contains.

Br

------------------------------
Robert Dostal
Team Leader BI
GEMÜ
Ingelfingen

Original Message:
Sent: Mon August 01, 2022 08:23 AM
From: Jenifer Broughton
Subject: Cognos Prompt Default Criteria question

Hello,

I have a Company Number prompt, and I'd like some insight on the best option to automatically have the prompt default to Company Number < 144 if the user does NOT make a selection.


Thanks,
Jen

------------------------------
Jenifer Broughton
------------------------------
#CognosAnalyticswithWatson

Hi Jenifer,

ok - you use a value prompt. I checked a text prompt. A quick insight on the same demo data with a value prompt works even better as "-1" does not appear in the drop down list but the filter works:


Is the default value just not appearing in your filter list at the moment or doesn't the filtering work either?

------------------------------
Robert Dostal
Team Leader BI
GEMÜ
Ingelfingen
------------------------------

Original Message:
Sent: Mon August 01, 2022 10:01 AM
From: Jenifer Broughton
Subject: Cognos Prompt Default Criteria question

Hi Robert,

Thank you so much for the quick assist.  I need to use a value prompt.  I tried what you recommended with the 1st option (as I do not want to maintain 2 reports).  I was not able to get it to work.  I do not see the -1 in the Company Prompt list.

Thanks,
Jen

------------------------------
Jenifer Broughton

Original Message:
Sent: Mon August 01, 2022 09:07 AM
From: Robert Dostal
Subject: Cognos Prompt Default Criteria question

Hi Jenifer,

one of my quick but dirty solutions: I usually use a dummy value that doesn't exist in the data E.G. "-1". Set this to the default value for the prompt and make the prompt mandatory.

The filter in your query should then look like this (on demo data):

([Sales (query)].[Sales order].[Order number]<100020 AND ?orderno?=-1) OR
[Sales (query)].[Sales order].[Order number] = ?orderno?

If the users don't change the value, the query gets filtered by Order number <100020. If there's a number, then this order will be filtered.
The down side here is that the user will see "-1" as default selection.

A second but more elaborate solution is to work with conditional blocks.
Create a boolean variable pointing to your parameter:
Duplicate your query and set one filtered to [data]=?parameter? and the second one to [data]<144.

Insert a conditional block  and link the variable just created to the block.

Then you "just" have to put one content from the dynamically filtered query into the "No" block and another content from the fixed filtered query into the "Yes" block.Works but you have to maintain double the queries and content - depending om how your report should look like and what it contains.

Br

------------------------------
Robert Dostal
Team Leader BI
GEMÜ
Ingelfingen

Original Message:
Sent: Mon August 01, 2022 08:23 AM
From: Jenifer Broughton
Subject: Cognos Prompt Default Criteria question

Hello,

I have a Company Number prompt, and I'd like some insight on the best option to automatically have the prompt default to Company Number < 144 if the user does NOT make a selection.


Thanks,
Jen

------------------------------
Jenifer Broughton
------------------------------
#CognosAnalyticswithWatson

@Robert Dostal

Hi.  I tried your latest option.  I went to the prompt page and for the Company Prompt i set the following:
Required:  No
Multi-select:  Yes
Default selection:  -1

The Company prompt is hitting a standalone prompt query called Company.
The prompt is being used in a query called Report Data.

The prompt filter in the Report Data query looks like this:
([Campany Path].[Company] between '00000' and '00143' and ?Document Company2? = -1)
OR
[Campany Path].[Company] = ?Document Company2?

When I run the report, I do not select any Company, but all companies return.
Any ideas?

Thanks,
Jen

------------------------------
Jenifer Broughton
------------------------------

Original Message:
Sent: Tue August 02, 2022 02:43 AM
From: Robert Dostal
Subject: Cognos Prompt Default Criteria question

Hi Jenifer,

ok - you use a value prompt. I checked a text prompt. A quick insight on the same demo data with a value prompt works even better as "-1" does not appear in the drop down list but the filter works:


Is the default value just not appearing in your filter list at the moment or doesn't the filtering work either?

------------------------------
Robert Dostal
Team Leader BI
GEMÜ
Ingelfingen

Original Message:
Sent: Mon August 01, 2022 10:01 AM
From: Jenifer Broughton
Subject: Cognos Prompt Default Criteria question

Hi Robert,

Thank you so much for the quick assist.  I need to use a value prompt.  I tried what you recommended with the 1st option (as I do not want to maintain 2 reports).  I was not able to get it to work.  I do not see the -1 in the Company Prompt list.

Thanks,
Jen

------------------------------
Jenifer Broughton

Original Message:
Sent: Mon August 01, 2022 09:07 AM
From: Robert Dostal
Subject: Cognos Prompt Default Criteria question

Hi Jenifer,

one of my quick but dirty solutions: I usually use a dummy value that doesn't exist in the data E.G. "-1". Set this to the default value for the prompt and make the prompt mandatory.

The filter in your query should then look like this (on demo data):

([Sales (query)].[Sales order].[Order number]<100020 AND ?orderno?=-1) OR
[Sales (query)].[Sales order].[Order number] = ?orderno?

If the users don't change the value, the query gets filtered by Order number <100020. If there's a number, then this order will be filtered.
The down side here is that the user will see "-1" as default selection.

A second but more elaborate solution is to work with conditional blocks.
Create a boolean variable pointing to your parameter:
Duplicate your query and set one filtered to [data]=?parameter? and the second one to [data]<144.

Insert a conditional block  and link the variable just created to the block.

Then you "just" have to put one content from the dynamically filtered query into the "No" block and another content from the fixed filtered query into the "Yes" block.Works but you have to maintain double the queries and content - depending om how your report should look like and what it contains.

Br

------------------------------
Robert Dostal
Team Leader BI
GEMÜ
Ingelfingen

Original Message:
Sent: Mon August 01, 2022 08:23 AM
From: Jenifer Broughton
Subject: Cognos Prompt Default Criteria question

Hello,

I have a Company Number prompt, and I'd like some insight on the best option to automatically have the prompt default to Company Number < 144 if the user does NOT make a selection.


Thanks,
Jen

------------------------------
Jenifer Broughton
------------------------------
#CognosAnalyticswithWatson

Hi Jen,

I cannot rebuild your problem with my demo or live data here. I selected a VARCHAR2 column and applied the same filters like you stated above: works perfectly.
This is my filter on the data query
When I don't select any year then years between 2010 and 2012 are in the report as default. Don't see the '-1' in the prompt field either - what is fine.

What I found out also: this solution will block your multi-selection as the filter states "=" instead of "in". But even with "in", the multi-selection isn't working. I also tried Paul's solution but couldn't get it to work either.

I'm sorry but I only have very limited time at the moment. If I find time again I'll let u know.

------------------------------
Robert Dostal
Team Leader BI
GEMÜ
Ingelfingen
------------------------------

Original Message:
Sent: Tue August 02, 2022 03:23 PM
From: Jenifer Broughton
Subject: Cognos Prompt Default Criteria question

@Robert Dostal

Hi.  I tried your latest option.  I went to the prompt page and for the Company Prompt i set the following:
Required:  No
Multi-select:  Yes
Default selection:  -1

The Company prompt is hitting a standalone prompt query called Company.
The prompt is being used in a query called Report Data.

The prompt filter in the Report Data query looks like this:
([Campany Path].[Company] between '00000' and '00143' and ?Document Company2? = -1)
OR
[Campany Path].[Company] = ?Document Company2?

When I run the report, I do not select any Company, but all companies return.
Any ideas?

Thanks,
Jen

------------------------------
Jenifer Broughton

Original Message:
Sent: Tue August 02, 2022 02:43 AM
From: Robert Dostal
Subject: Cognos Prompt Default Criteria question

Hi Jenifer,

ok - you use a value prompt. I checked a text prompt. A quick insight on the same demo data with a value prompt works even better as "-1" does not appear in the drop down list but the filter works:


Is the default value just not appearing in your filter list at the moment or doesn't the filtering work either?

------------------------------
Robert Dostal
Team Leader BI
GEMÜ
Ingelfingen

Original Message:
Sent: Mon August 01, 2022 10:01 AM
From: Jenifer Broughton
Subject: Cognos Prompt Default Criteria question

Hi Robert,

Thank you so much for the quick assist.  I need to use a value prompt.  I tried what you recommended with the 1st option (as I do not want to maintain 2 reports).  I was not able to get it to work.  I do not see the -1 in the Company Prompt list.

Thanks,
Jen

------------------------------
Jenifer Broughton

Original Message:
Sent: Mon August 01, 2022 09:07 AM
From: Robert Dostal
Subject: Cognos Prompt Default Criteria question

Hi Jenifer,

one of my quick but dirty solutions: I usually use a dummy value that doesn't exist in the data E.G. "-1". Set this to the default value for the prompt and make the prompt mandatory.

The filter in your query should then look like this (on demo data):

([Sales (query)].[Sales order].[Order number]<100020 AND ?orderno?=-1) OR
[Sales (query)].[Sales order].[Order number] = ?orderno?

If the users don't change the value, the query gets filtered by Order number <100020. If there's a number, then this order will be filtered.
The down side here is that the user will see "-1" as default selection.

A second but more elaborate solution is to work with conditional blocks.
Create a boolean variable pointing to your parameter:
Duplicate your query and set one filtered to [data]=?parameter? and the second one to [data]<144.

Insert a conditional block  and link the variable just created to the block.

Then you "just" have to put one content from the dynamically filtered query into the "No" block and another content from the fixed filtered query into the "Yes" block.Works but you have to maintain double the queries and content - depending om how your report should look like and what it contains.

Br

------------------------------
Robert Dostal
Team Leader BI
GEMÜ
Ingelfingen

Original Message:
Sent: Mon August 01, 2022 08:23 AM
From: Jenifer Broughton
Subject: Cognos Prompt Default Criteria question

Hello,

I have a Company Number prompt, and I'd like some insight on the best option to automatically have the prompt default to Company Number < 144 if the user does NOT make a selection.


Thanks,
Jen

------------------------------
Jenifer Broughton
------------------------------
#CognosAnalyticswithWatson

Thank you so much!  I appreciate all the insight into this!

------------------------------
Jenifer Broughton
------------------------------

Original Message:
Sent: Wed August 03, 2022 04:41 AM
From: Robert Dostal
Subject: Cognos Prompt Default Criteria question

Hi Jen,

I cannot rebuild your problem with my demo or live data here. I selected a VARCHAR2 column and applied the same filters like you stated above: works perfectly.
This is my filter on the data query
When I don't select any year then years between 2010 and 2012 are in the report as default. Don't see the '-1' in the prompt field either - what is fine.

What I found out also: this solution will block your multi-selection as the filter states "=" instead of "in". But even with "in", the multi-selection isn't working. I also tried Paul's solution but couldn't get it to work either.

I'm sorry but I only have very limited time at the moment. If I find time again I'll let u know.

------------------------------
Robert Dostal
Team Leader BI
GEMÜ
Ingelfingen

Original Message:
Sent: Tue August 02, 2022 03:23 PM
From: Jenifer Broughton
Subject: Cognos Prompt Default Criteria question

@Robert Dostal

Hi.  I tried your latest option.  I went to the prompt page and for the Company Prompt i set the following:
Required:  No
Multi-select:  Yes
Default selection:  -1

The Company prompt is hitting a standalone prompt query called Company.
The prompt is being used in a query called Report Data.

The prompt filter in the Report Data query looks like this:
([Campany Path].[Company] between '00000' and '00143' and ?Document Company2? = -1)
OR
[Campany Path].[Company] = ?Document Company2?

When I run the report, I do not select any Company, but all companies return.
Any ideas?

Thanks,
Jen

------------------------------
Jenifer Broughton

Original Message:
Sent: Tue August 02, 2022 02:43 AM
From: Robert Dostal
Subject: Cognos Prompt Default Criteria question

Hi Jenifer,

ok - you use a value prompt. I checked a text prompt. A quick insight on the same demo data with a value prompt works even better as "-1" does not appear in the drop down list but the filter works:


Is the default value just not appearing in your filter list at the moment or doesn't the filtering work either?

------------------------------
Robert Dostal
Team Leader BI
GEMÜ
Ingelfingen

Original Message:
Sent: Mon August 01, 2022 10:01 AM
From: Jenifer Broughton
Subject: Cognos Prompt Default Criteria question

Hi Robert,

Thank you so much for the quick assist.  I need to use a value prompt.  I tried what you recommended with the 1st option (as I do not want to maintain 2 reports).  I was not able to get it to work.  I do not see the -1 in the Company Prompt list.

Thanks,
Jen

------------------------------
Jenifer Broughton

Original Message:
Sent: Mon August 01, 2022 09:07 AM
From: Robert Dostal
Subject: Cognos Prompt Default Criteria question

Hi Jenifer,

one of my quick but dirty solutions: I usually use a dummy value that doesn't exist in the data E.G. "-1". Set this to the default value for the prompt and make the prompt mandatory.

The filter in your query should then look like this (on demo data):

([Sales (query)].[Sales order].[Order number]<100020 AND ?orderno?=-1) OR
[Sales (query)].[Sales order].[Order number] = ?orderno?

If the users don't change the value, the query gets filtered by Order number <100020. If there's a number, then this order will be filtered.
The down side here is that the user will see "-1" as default selection.

A second but more elaborate solution is to work with conditional blocks.
Create a boolean variable pointing to your parameter:
Duplicate your query and set one filtered to [data]=?parameter? and the second one to [data]<144.

Insert a conditional block  and link the variable just created to the block.

Then you "just" have to put one content from the dynamically filtered query into the "No" block and another content from the fixed filtered query into the "Yes" block.Works but you have to maintain double the queries and content - depending om how your report should look like and what it contains.

Br

------------------------------
Robert Dostal
Team Leader BI
GEMÜ
Ingelfingen

Original Message:
Sent: Mon August 01, 2022 08:23 AM
From: Jenifer Broughton
Subject: Cognos Prompt Default Criteria question

Hello,

I have a Company Number prompt, and I'd like some insight on the best option to automatically have the prompt default to Company Number < 144 if the user does NOT make a selection.


Thanks,
Jen

------------------------------
Jenifer Broughton
------------------------------
#CognosAnalyticswithWatson