question on prompts

[rajusus111]

I wrote a sql and created prompts in report studio cognos 8. I have sql and sub query .

Like below

select a,b,c from
table names
where
joins
and column name = prompt - 10
and not exists(select a,b from table names
where joins
and column name = prompt)

column on prompt data type is float. My question is can we do addition,subtraction on prompts in cognos 8 ??
I am not getting any data using above sql .
Please advise me gurus....

 

[BICentre]

Hello, yes you can use a ReportExpression on your report in order to work with the prompt. The sample below will prompt you for a parameter value to add to the EmpCount. Your ReportExpression would then display the EmpCount + your prompt value.

TblA
----
EmpCount (int)


ReportExpression text:
EmpCount + ?p_Add?

Wallace Taylor

http://bicentre.blogspot.com

 

[rajusus111]

You mean to say we can do addition/subtraction in tabular sql on prompt parameters

select a,b,c from
table names
where
joins
and column name = (#('promptname)# - 10)
and not exists(select a,b from table names
where joins
and column name = (#('promptname)# )


As per your comments above sql should work right??

thank you very much for your reply...

 

[BICentre]

Yes. Assuming of course that your operations have the correct data types where column name is a numerical value and not a textual value. The syntax that you have in your example wouldn't work.

Wallace Taylor

http://bicentre.blogspot.com

 

[rajusus111]

Thanks for reply,

here is the sql i am running in report but i am getting blank report sir.

select T1.STU_ID, T1.TERM from
TAR_F_ENROLLMENT_2008 T1
where (T1.TERM =((#prompt('TERM')#)- 7))
and T1.ENRL_CAMPUS='OH'
and T1.SUB_CAMPUS ='WWW'
and T1.TERM_ENRL_NRS = 'Y'
and not EXISTS(select T2.stu_id from
TAR_F_ENROLLMENT_2008 T2 where T2.TERM = (#prompt('TERM')#)
and T2.STU_ID=T1.STU_ID
and T2.TERM_ENRL_NRS = 'Y')

Can we put multiple prompts on same field ?? PLease correct me if my format is wrong. Also data types is float for term on which i am trying to create prompt.

I appriciate your help.

 

[BICentre]

Hello, what happens when you hard code a value instead of using the prompt? Do you get a result set? If so, then are you having any issues with datatypes where you think that you're passing 2 but it's actually '2'?

Did you try breaking down the query to a very basic:

select T1.STU_ID, T1.TERM from
TAR_F_ENROLLMENT_2008 T1
where (T1.TERM =((#prompt('TERM')#)))

and then adding in the:

select T1.STU_ID, T1.TERM from
TAR_F_ENROLLMENT_2008 T1
where (T1.TERM =((#prompt('TERM')#)- 7))



Wallace Taylor

http://bicentre.blogspot.com

 

[rajusus111]

As you suggested i tried breaking query
By hard coding values it is working
Like below

select T1.STU_ID, T1.TERM from
TAR_F_ENROLLMENT_2008 T1
where T1.TERM =('1083' - '7')
and T1.ENRL_CAMPUS='OH'
and T1.SUB_CAMPUS ='WWW'
and T1.TERM_ENRL_NRS = 'Y'

where as if i put prompt parameter then i am getting blank report.


select T1.STU_ID, T1.TERM from
TAR_F_ENROLLMENT_2008 T1
where (T1.TERM =((#prompt('TERM')#)- 7))
and T1.ENRL_CAMPUS='OH'
and T1.SUB_CAMPUS ='WWW'
and T1.TERM_ENRL_NRS = 'Y'

I tried putting - '7' and '7.0' and applying to_number both sides. Since the cloumn type is float.

Any suggestions would greate help for me.

thanks