IDT Date prompt implementation

[system]

Hello

I am using BI 4.1 SP5, and backend is SQL Server 2012. I am new to IDT prompt creation stuff.
My requirement is , i should select a date from the prompt, and based on the selection of the date data gets filter for -

1. Previous Month from selected date
2. Exact Last 12 Months from Selected date
3. Current Year from Selected date.
4. Default date in the prompt should be Today’s date.

I have written SQL scripts in SQLserver for above mentioned first 3 conditions, but not sure how to implement them in universe.

–Lastl Month ----
SELECT datepart(MM,DATEADD(MM,-1,getdate()))

–Current Year—

SELECT * FROM Dim_Date
WHERE Year = datepart(YYYY,getdate())
AND MonthNumber between ‘1’ and ‘12’

–Last 12 Months----

SELECT * FROM dim_date
WHERE col1 >=DATEADD(month,-12,DATEADD(day,DATEDIFF(day,0,GETDATE()),0))
AND col1 <=DATEADD(day,DATEDIFF(day,0,GETDATE()),0)

Catch here is there are multiple queries for the measures to show on the report, but user should see only one prompt to enter one single date.

*So will there be 3 different prompts I need to write? showing same message as we used to write in previous versions.

• How to select default value as today’s date

Could you please let me know, how should I write these scripts in the prompts or how should I implement my prompts in IDT.

Thanks in advance.

---

zgoyal14 (BOB member since 2011-08-03)

[MarkP]

If each prompt is in a separate query, you will get one prompt dialog box with three prompts in it. However, I don’t see a single @Prompt anywhere in your code. This thread may help:

DB-specific Relative Dates SQL Server, Oracle, Netezza, DB2

As per this topic on Oracle relative dates, here’s the same set of dates in SQL Server. Please note that some may not work on SQL Server 2000, but all definitely work in 2005/8. Yesterday cast(convert(char(10),dateadd(d,-1,getdate()),23) as datetime) Today cast(convert(char(10),getdate(),23) as datetime) The following also works for today: dateadd(dd, datediff(dd,0,getdate()), 0) Start of Current Month cast(convert(char(7),getdate(),23)+'-01' as datetime) End of Current Month dateadd(d,-1…

[system]

Thanks Mark for the reply. This post will surely help me in future.

I have implemented it with some help from youtube on how to create prompts in latest version - IDT.

1. I have selected LOV’s from date table and union with string.
   Select Convert(Date,21) from Dim_date
   Union
   Select ‘Today’
   Order by Date

2. Created parameters (Select Date) for user to select date, assigned above created LOV’s to this parameters and selected default value as Today.

3. Created 3 different prompts for required time period - Last Month/ Last 12 Months and / Current Year with Case statement that if @Prompt(Select Date) = Today then data should be filter from getdate()
   Otherwise from the selected date, data should filter.

**Only drawback is, I couldn’t show date here as date/calendar in the prompt for user to select date, I had to convert it to string to show default date as ‘today’.
if we would have default value to be shown as Date Type, it would have been even more easier

Thanks

---

zgoyal14 (BOB member since 2011-08-03)