#Prompt# in a Relational Report

[MMcBride]

I use prompt macro's in my Cube based reports alot to provide a dynamic date that defaults to the most recent month but still allows the users to "Drill back in time"

Cube based Data Item that allows us to filter the query by current date and allow drill based on date. - This works great I have used it for around 5 years with no issue.

Code Select Expand

#prompt('finishMonth','memberUniqueName','closingPeriod([CBMR].[Accounting Date].[Accounting Date].[Month],
item(tail([CBMR].[Accounting Date].[Accounting Date].[Year], 1), 0))')#

Now the problem is I have a set of data that is relational based but with the same requirement. If I wanted to show yesterdays data as the default this statement below works perfect. The problem is the data is not processed on Sunday so Monday's reports show blank because there is no data for current_date -1...

Code Select Expand

#prompt('dateparm','date','_add_days(current_date, -1)')#

If I just wanted to force a Default value in a prompt I use a seperate set of java script

Code Select Expand

<script language="javascript">
var form = getFormWarpRequest();
var DateDropdown = form._oLstChoicesdateparm;
DateDropdownInit();
function rePromptPage() {
self.setTimeout("promptAction( 'reprompt' );", 10 );
}
function DateDropdownInit() {
if (DateDropdown.value=='') DateDropdown.value = DateDropdown[0].value;
}
</script>


But using the Java Script to set the default date means I cannot create report views that always default to the most recent date... So I need to use the #prompt#...

Can anyone think of anything I can use in a relational report that will give me "max('[Eval Date]')"?
Yes I tried the Max and Maximum etc but these cause aggregation errors, I have tried making a seperate data item to return the max eval date, and used this item in the statement - nothing I have tried works...

Anyt thoughts or ideas would be greatly appreciated...   ;D

[calson33]

Depending on your database, evaluate the selected day and then use a case statement or something similar.
e.g.
case get_day_of_week(current_date)
   when 'Monday' then
         #prompt('dateparm','date','_add_days(current_date, -3)')#
   else #prompt('dateparm','date','_add_days(current_date, -1)')#
end


EDIT:
You could also
select Max(The_date) from mytable where the_date < current_date

[MMcBride]

I am on DB2 so my version

case dayname (current_date)
   when 'Monday' then
         #prompt('dateparm','date','_add_days(current_date, -2)')#
   else #prompt('dateparm','date','_add_days(current_date, -1)')#
end

Calson33 thank you  ;D

Sometimes I get so caught up in making something specific work the way I want it to I miss the obvious  8)