Print Page - SQL Prompt

Title: SQL Prompt
Post by: ajen7118 on 29 Mar 2017 12:07:02 PM

Hi All,

I have a SQL prompt (Prompt1) with "cd" and "Name" ,and I use "cd" to display value in prompt.

for example "cd"
FY15-16
FY16-17

"Name"
Fiscal year 2015 -2016
Fiscal year 2016 -2017

However, in the report I would like to use "Name" to display what I choose in prompt.

Currently, I have a report expression for headings -->ParamDisplayValue("Prompt1") and this only allows me to bring in header FY15-16 or FY16-17

How can I change the code to bring in Fiscal year 2015 -2016 or Fiscal year 2016 -2017 ?

thank you

Title: Re: SQL Prompt
Post by: New_Guy on 29 Mar 2017 04:05:14 PM

Hi,

You should be ok for another 82 years with the below expression

'Fiscal Year 20' + substring(substring(ParamDisplayValue("Prompt1"),3,5),1,2)
+ '20' + substring(substring(ParamDisplayValue("Prompt1"),3,5),4,2)

Good luck
New guy

Title: Re: SQL Prompt
Post by: ajen7118 on 29 Mar 2017 04:21:34 PM

Thanks New Guy,

is there any way to avoid hardcoding?

thank you so much

Title: Re: SQL Prompt
Post by: New_Guy on 29 Mar 2017 04:25:10 PM

Hi,
It depends on the source data. What is the actual use value and is there any column that we can get the year from?

New guy

Title: Re: SQL Prompt
Post by: ajen7118 on 29 Mar 2017 04:51:22 PM

Hi,

My main source data(SQL base) doesn't have the "Name" associated with but only "cd" which is associated with prompt SQL.

Maybe I can make a join in my main source data to bring in the "Name" Column.

But I was hoping there is a easy way to do it.

thanks.

COGNOiSe.com - The IBM Cognos Community

IBM Cognos 10 Platform => Cognos 10 BI => Report Studio => Topic started by: ajen7118 on 29 Mar 2017 12:07:02 PM