SQL Prompt

[ajen7118]

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

[New_Guy]

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

[ajen7118]

Thanks New Guy,

is there any way to avoid hardcoding?

thank you so much

[New_Guy]

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

[ajen7118]

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.