I have a request for the following logic to be applied on a reports input prompt for date/time. Anyone know how to accomplish this:
Add a check to the logic
a. When a user selects a begin/end date combination that extends past 30 days,
i. Calculate [newEndDate] = Begin Date + 30
ii. Display a message that states This report is restricted to 30 days of data. End date has been moved to [newEndDate]. If you require a larger data set, please contact your Customer Manager to schedule.
Create this variable to calculate the days between two selected dates
Diff=DaysBetween(ToDate(UserResponse("Enter End Date:");"");ToDate(UserResponse("Enter Start Date:");""))
Make sure, you write the End Date first in this code else it will return a negative value. Create a variable to display a message.
=If([Diff]>=30) Then "This report is restricted to 30 days of data. End date has been moved to [newEndDate]. If you require a larger data set, please contact your Customer Manager to schedule." Else ""
I have to ask the purpose of this, as typically something like this is done so that the query comes back in a reasonable amount of time. (Like to prevent someone from querying 20+ years of data).
If that’s the case, there is NOTHING you can do report side alone to limit things based on objects. You can limit via properties by Query Run time and query Records, but nothing else.
M H Mohammed’s solution will work fine if you just want to display a message to the user once the query returns, but by then the damage is usually done
My suggestion would be to create a universe object to handle the prompts.
I Think you could create an object which will basically say (psudo-text below)
<WhateverDateObject> between
@prompt('<PromptStartDate','D',,mono,free)
and
Case When DateDiff ( @prompt('<PromptStartDate','D',,mono,free),
@prompt('<PromptEndtDate','D',,mono,free) > 30 )
THEN DateAdd(d,30,@prompt('<PromptStartDate','D',,mono,free)
Else @prompt('<PromptEndDate','D',,mono,free) END
I’d also create a small flag object that has similar prompts to check and see if the daterange was greater than 30 and return a True/False value. That way you can include both the normal prompt, and the flag object in the query and use the flag to determine if any special message should be shown.
NOTE: I’ve never needed to create such a prompt, but I’m fairly sure this will work in MS SQL, no clue about other DB types.
(BOB member since 2010-06-16)
(BOB member since 2005-01-06)