Hello Experts,

I wanted to create @prompt function optional in UDT.

I wanted to create a prompt to display "Last Year", "Last Month", "Last Quarter", "Current Year" along with this also I am using start date end date prompt and that are also optional so either my user will select @prompt function or user will enter hardcoded dates

Request you to achieve this scenario.

Currently I am using below formulae to display above options and to make prompt optional using optional word at the end

CASE

WHEN @Prompt('Relative Period', 'A', { 'Last Week', 'Current Week to Date', 'Last Month', 'Last Quarter', 'Relative Month', 'Last Year','Custom'}, MONO,, not_persistent,, User:0, optional) = 'Last Week' THEN CAST(dateadd(wk, datediff(wk, 0, getdate()) - 1, 0) AS DATE)

WHEN @Prompt('Relative Period', 'A', { 'Last Week', 'Current Week to Date', 'Last Month', 'Last Quarter', 'Relative Month', 'Last Year','Custom'}, MONO,, not_persistent,, User:0,

optional

) = 'Last Month' THEN CAST(DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-1, 0) AS DATE)

WHEN @Prompt('Relative Period', 'A', { 'Last Week', 'Current Week to Date', 'Last Month', 'Last Quarter', 'Relative Month', 'Last Year','Custom'}, MONO,, not_persistent,, User:0,

optional

) = 'Last Year' THEN CAST(DATEADD(YEAR,-1, GETDATE()) AS DATE)

WHEN @Prompt('Relative Period', 'A', { 'Last Week', 'Current Week to Date', 'Last Month', 'Last Quarter', 'Relative Month', 'Last Year','Custom'}, MONO,,not_persistent,, User:0,

optional

) = 'Last Quarter' THEN CAST(DATEADD(qq,DATEDIFF(qq,0,GETDATE())-1,0) AS DATE)
END

With this I can see my prompt is showing optional but report will not show any data if I keep prompt blank. So I need to select some value in it.

Please help.

Thanks,

Priyanka