Do you need to implement Date prompts in report pages without using macros? Then here is the solution using Render Variable. Remember a date prompt will always have a value (current date by default) and default selections cannot be dynamic values. They need to be static dates.
Requirement: Display Year, Retailer, Order Method, Revenue, Gross Profit for default date of current date - 30 days to current date.
Solution:Step 1: Create basic list report using GO Sales package by dragging the required data items from the package.
Step 2: Create 2 Date prompts called From Date and To Date and name the prompts as FromDate and ToDate for referencing in JavaScript and include a Finish Prompt button.
Step 3: Create a filter in your query as [Date] between ?From Date? and ?To Date? and set it to required.
Step 4: Let us call this list and query as "Date Prompts Selected".
Step 5: Now to handle the default scenario. Copy paste the "Date Prompts Selected" list and query and name them as "Date prompts Default".
Step 6: Create a String Conditional Variable called "Date Prompt Values" with the following condition:
case when paramcount('From Date') = 0 then 'Date prompts Default' else 'Date prompts Selected' end
Step 7: Apply the conditional variable to the render variable property of the "Date Prompts Selected" list and "Date prompts Default" list.
Step 8: Now to implement the Current Date - 30 to Current Date scenario in the "Date prompts Default" query, modify the filter to:
[Date] between _add_days(current_date, - 30) and current_date
Step 9: To implement the Current Date - 30 to Current Date scenario in the Date Prompts using JavaScript:
Insert a text box prompt to identify the default run of the report. Name this as RunValue. Set the default selection to 1 to identify this as Run 1. Set the Visible property of this object to No.
Now insert an HTML Item below the prompts and insert the following script:
<script type="text/javascript">
function DefaultDateSelection()
{
var fW = (typeof getFormWarpRequest == "function" ?getFormWarpRequest() : document.forms["formWarpRequest"]);
if ( !fW || fW == undefined)
{ fW = ( formWarpRequest_THIS_ ?formWarpRequest_THIS_ : formWarpRequest_NS_ );
}
var months = new Array('Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec');
var RunValueObj = fW._textEditBoxRunValue;
if(RunValueObj.value==1)
{
var ToTime=new Date();
var ToMonth=months[ToTime.getMonth()];
var ToDate= ToTime.getDate();
var ToYear= ToTime.getUTCFullYear();
var ToDate = ToMonth + ' ' + ToDate + ', ' + ToYear;
var FromTime = ToTime;
FromTime.setDate(FromTime.getDate()-30);
var FromMonth=months[FromTime.getMonth()];
var FromDate= FromTime.getDate();
var FromYear= FromTime.getUTCFullYear();
var FromDate = FromMonth + ' ' + FromDate + ', ' + FromYear;
pickerControlFromDate.setValue(FromDate);
pickerControlToDate.setValue(ToDate);
RunValueObj.value=2;
}
}
DefaultDateSelection();
</script>
Note: This technique uses JavaScript against underlying report objects in a IBM Cognos 8 BI report. For this reason, there is no guarantee that reports created using this technique will migrate or upgrade successfully to future versions without requiring modifications. Any such modifications are the responsibility of the report designer.
Posts
