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 -
Previous Month from selected date
Exact Last 12 Months from Selected date
Current Year from Selected date.
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.
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:
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.
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
Created parameters (Select Date) for user to select date, assigned above created LOV’s to this parameters and selected default value as Today.
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