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:
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:
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! :)
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')#)
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
So simple! That fixed it.
Thank you, both of you. And thank you for the syntax! ;D