[RESOLVED] One prompt page to pass multiple values to multiple SQL

[mameha]

Hello all,

I'm very new to Cognos and I'm having trouble designing prompts. I am using Cognos 10.1.1 Report Studio. I have a report using a custom package.  There are two pages with lists and two queries using native sql that joins two packages. I am trying to design one prompt page that will pass values selected from a list to both queries/sql.

Here is the SQL for the two queries.

Query 1:

Code Select Expand

select
Person_Detail.ID as "SPRIDEN_ID",
Person_Detail.LAST_NAME as "SPRIDEN_LAST_NAME",
Person_Detail.FIRST_NAME as "SPRIDEN_FIRST_NAME",
MSUODS_PERDTOT_TABLE.PERDTOT_YEAR as "PERDTOT_YEAR",
MSUODS_PERDTOT_TABLE.PERDTOT_EMPR_CODE as "PERDTOT_EMPR_CODE",
MSUODS_PERDTOT_TABLE.PERDTOT_BDCA_CODE as "PERDTOT_BDCA_CODE",
MSUODS_PERDTOT_TABLE.PERDTOT_MONTH as "PERDTOT_MONTH",
MSUODS_PERDTOT_TABLE.PERDTOT_EMPL_AMT as "PERDTOT_EMPL_AMT",
MSUODS_PERDTOT_TABLE.PERDTOT_EMPR_AMT as "PERDTOT_EMPR_AMT",
MSUODS_PERDTOT_TABLE.PERDTOT_APPL_GRS as "PERDTOT_APPL_GRS",
MSUODS_PERDTOT_TABLE.PERDTOT_ACTIVITY_DATE as "PERDTOT_ACTIVITY_DATE"

from PAYROLL.PERDTOT MSUODS_PERDTOT_TABLE, ODSMGR.PERSON_DETAIL Person_Detail

where ODSMGR.PERSON_DETAIL.PERSON_UID = PAYROLL.MSUODS_PERDTOT_TABLE.PERDTOT_PIDM
and MSUODS_PERDTOT_TABLE.PERDTOT_YEAR in #promptmany('p_YEAR')#
and MSUODS_PERDTOT_TABLE.PERDTOT_BDCA_CODE in #promptmany('p_BDCA_CODE')#

order by Person_Detail.LAST_NAME, Person_Detail.FIRST_NAME, PERDTOT_YEAR, PERDTOT_BDCA_CODE

Query 2:

Code Select Expand

select 
Person_Detail.ID as "SPRIDEN_ID",
Person_Detail.LAST_NAME as "SPRIDEN_LAST_NAME",
Person_Detail.FIRST_NAME as "SPRIDEN_FIRST_NAME",
sum(MSUODS_PERDTOT_TABLE.PERDTOT_EMPL_AMT) as "EMPL_AMT_TOTAL",
sum(MSUODS_PERDTOT_TABLE.PERDTOT_EMPR_AMT) as "EMPR_AMT_TOTAL",
sum(MSUODS_PERDTOT_TABLE.PERDTOT_APPL_GRS) as "APPL_GRS_TOTAL"
       
from PAYROLL.PERDTOT MSUODS_PERDTOT_TABLE, ODSMGR.PERSON_DETAIL Person_Detail

where ODSMGR.PERSON_DETAIL.PERSON_UID = PAYROLL.MSUODS_PERDTOT_TABLE.PERDTOT_PIDM
and MSUODS_PERDTOT_TABLE.PERDTOT_YEAR in #promptmany('p_YEAR')#
and MSUODS_PERDTOT_TABLE.PERDTOT_BDCA_CODE in #promptmany('p_BDCA_CODE')#

group by Person_Detail.ID, Person_Detail.LAST_NAME, Person_Detail.FIRST_NAME

You can see in the WHERE clause, this is what I've tried. I created a prompt page with two prompts ("p_YEAR" and "p_BDCA_CODE"). They each have their own query pulling a list of values for the prompt.

When testing, if I choose only one value in each prompt, it runs accurately.  But if I choose multiple values in either prompt, it errors with "An error occurred while performing operation 'sqlPrepareWithOptions' status='-9'."  The same goes when I validate the report. If I validate with only single choices in each prompt, it validates fine. If I validate with multiple selections, it errors the same.

It's obviously not passing multiple selections to the SQL correctly. If anyone knows how I can fix this I'd greatly appreciate it - or even a better way to design the report. Thanks for reading!   :)

[calson33]

This is a wild guess, but have you tried putting brackets around the prompts?

e.g.
and MSUODS_PERDTOT_TABLE.PERDTOT_YEAR in (#promptmany('p_YEAR')#)
and MSUODS_PERDTOT_TABLE.PERDTOT_BDCA_CODE in (#promptmany('p_BDCA_CODE')#)

[RKMI]

Hi,

I agree with calson,

We use Oracle also. The syntax if you want multi-select would be:

in (#promptmany('Prompt Name')#)

the key is the extra parentheses on the outside, and "in" not "="

Also, if you want this to be nullable (not required), you would need syntax as follows:

in (#promptmany('Prompt Name', '', 'prompt field name')#)

Thanks,
RK

[mameha]

So simple!  That fixed it. 

Thank you, both of you. And thank you for the syntax!  ;D