adding conditional prompt macro native sql query

[ibm1983]

Cognos BI version: 10.2.1 using report studio
I have a report with one prompt p1 and its a sql query based report.
Prompt UI: p1 is a DropdownList(use value, display value)
1-value1, 2-value2, 3-value3, 4-value3

I am trying to add condition based prompt syntax the sql query.. based on the dropdown prompt selected value, i need to query a different column in the database sql. The prompt p1 is a dropdownlist in which the default selection is set to 1

Code Select Expand

select col1, col2, col3, col4 from table1
where col4  is not null
case when #Prompt('p1')# = 1 then ' and col1 = 1'
     when #Prompt('p1')# = 2 then ' and col2 =2'
     when #Prompt('p1')# = 3 then ' and col3 =3'
end

I tried to use lot of syntax searching online but couldn't find the examples with respect to my scenario. I need help in achieving the correct way of writing the statements to satisfy my requirement.any help is appreciated..

[CognosPaul]

Are you using DQM?

If so, you can modify the macro in your where clause to be a simple case:

Code Select Expand


select col1, col2, col3, col4 from table1
where col4  is not null
#
case prompt('p1','token')
  when 1 then ' and col1 = 1'
  when 2 then ' and col2 =2'
  when 3 then ' and col3 =3'
end
#


That being said, please remember the usual admonishments against using hand-written SQL. Dynamic filters are easier to build and maintain when using the standard query builder.

[ibm1983]

Thank you. We are not using DQM. We manually created a query object and bind the sql object to it. We are trying to modify the native sql to achieve..

[CognosPaul]

A very wise man on the board once compared using hand written SQL in Cognos to pushing your Ferrari down the highway. You're spending a lot of money on a very powerful tool and not using it's capabilities.

If you absolutely must use hand written SQL here, the next step is to modify the framework. Create a parameter map that will return the correct filter, and then call that:

Code Select Expand


select col1, col2, col3, col4 from table1
where col4  is not null
#$sqlFilterLookup{prompt('p1','token')}#


The parameter map key will be 1, 2, 3, with the values being ' and col1 = 1'

[ibm1983]

Thank you Paul. I agree with your statement. We always use the model but there's an urgent requirement from the client wanting a report. Our lead doesn't want to make any changes to the model or the framework manager since its a one time report. Also this is like a admin report which will query tables that are not really part of the data model. That's the only reason am choosing the crude way of adding dynamic filters to the native sql where clause.

I tried to do the below but for some reason its showing prompt page when it runs. This prompt p1 is optional prompt and a default selection is set to 1.

Code Select Expand

select col1, col2, col3, col4 from table1
where col4  is not null
and
(
        (#Prompt('p1')# = 1 and col1 = 1) OR
        (#Prompt('p1')# = 2 and col2 = 2) OR
)

Any help is greatly appreciated..

[CognosPaul]

A prompt page will appear for optional prompts if the report page does not contain a prompt control using that parameter name. Make sure that the prompt control always appears in the report, and is not hidden through render variables or conditional blocks.

Also, the way it appears in your SQL implies it is not optional. Change the macro to #prompt('p1','integer','1')#, doing this will provide 1 as the default value.