IBM Business Analytics

Hey Guys,

We have a huge data in one of the Query subject (table) in Cognos Framework manager, so we want to reduce the result set based on the user scope. So we have placed an optional prompt in the query subject definition.

Eg: Select * from Table where Location_ID in (#Promptmany('Prm_Location', 'Integer', 'Table.LocationID')#)


This is how the query generated when I referred from Framework Manager


but in report it is asking for the Location ID





Here my moto is to get the limited data when the location is passed from any report prompt in the other cases it should return all the data, but the issue is it is giving the optional filter when user create any list report with out any prompts. Is there a way to avoid that prompt?

Thanks in advance!

------------------------------
Raviteja Chinta
------------------------------

Hi Raviteja,

If you have a prompt (or promptmany) in your query subject in Framework, it will display a prompt in the report.

I guess you shoud use session parameters and parameter maps in macros to dynamically resolve expressions. More information available in:
Business Rules

Best regards,

------------------------------
Patrick Neveu
Positive Thinking Company
------------------------------

Original Message:
Sent: Thu May 05, 2022 03:29 PM
From: Raviteja Chinta
Subject: How to avoid the optional filter in reports when we have optional Prompts in framework

Hey Guys,

We have a huge data in one of the Query subject (table) in Cognos Framework manager, so we want to reduce the result set based on the user scope. So we have placed an optional prompt in the query subject definition.

Eg: Select * from Table where Location_ID in (#Promptmany('Prm_Location', 'Integer', 'Table.LocationID')#)


This is how the query generated when I referred from Framework Manager


but in report it is asking for the Location ID





Here my moto is to get the limited data when the location is passed from any report prompt in the other cases it should return all the data, but the issue is it is giving the optional filter when user create any list report with out any prompts. Is there a way to avoid that prompt?

Thanks in advance!

------------------------------
Raviteja Chinta
------------------------------

Hi @Raviteja Chinta

apart from what Patrick suggested very wisely ... I might have two feasible options which you might consider as well.

I understood, that you want avoid the prompt depending on something and select A in case 1 and B in case 2.

I will wrap the "something" as "MYPROMPT" in the report and let the SQL do A or B depending on the report parameter values.

Solution 1

So, wrapped your prompmany inside an if-then-else with another variable to steer what you want to achieve.

FilterItem in Query looks like this:

[TABSCHEMA] in (

IF ( ?MYPROMPT? = 'FOO' )
THEN ( #sq(promptmany( 'EXTRAPROMPT' , 'string','MYTABLES' ))#)
ELSE ( 'SYSTOOLS' )

)

Case 1. Do not prompt me and show A
Setting the value for MYPROMPT in Report properties ... that is Report > Properties > Report > 
Prompt for values [ ]
Current Values > MYPROMPT = "DO NOT PROMPT ME" 

Now clicking on the report it gives me all tables from schema "SYSTOOLS"

Case 2. Do not prompt me and show B
Set MYPROMPT value as before using the value "FOO".
Shows me the values from MYTABLES without prompt.

Case 3. You want the prompt and show A or B depending on selection
Select in the Report Parameter Values > Prompt for values [X]

Solution 2

Call the report via URL passing the parameter as ?p_MYPROMPT=FOO in the URL itself (see Details here) or via JSON object (details here )

Let me know if that helped

------------------------------
Ralf Roeber
https://linkedin.com/in/ralf-roeber/
------------------------------

Original Message:
Sent: Thu May 05, 2022 03:29 PM
From: Raviteja Chinta
Subject: How to avoid the optional filter in reports when we have optional Prompts in framework

Hey Guys,

We have a huge data in one of the Query subject (table) in Cognos Framework manager, so we want to reduce the result set based on the user scope. So we have placed an optional prompt in the query subject definition.

Eg: Select * from Table where Location_ID in (#Promptmany('Prm_Location', 'Integer', 'Table.LocationID')#)


This is how the query generated when I referred from Framework Manager


but in report it is asking for the Location ID





Here my moto is to get the limited data when the location is passed from any report prompt in the other cases it should return all the data, but the issue is it is giving the optional filter when user create any list report with out any prompts. Is there a way to avoid that prompt?

Thanks in advance!

------------------------------
Raviteja Chinta
------------------------------