Defect - Conditional Variables based on Members + Drill + Render Variable

There are times when you would want to use conditional variables to hide/display drill through links in a crosstab. If your condition is based on levels its easy to achieve this using levelNumber roleValue. If your condition is based on member names then there is a possibility that you may run into errors under the following scenario:

1. Report has a conditional variable to drive the display of links and
2. Report has a ParamDisplayValue layout calculation that has Render Variable set on a completely different conditional variable and
3. You drill up/down on the member and then you select a value in a report page prompt and re-run the report you may run into the error: RSV-RND-0089 Failed to evaluate expression

This does not happen if you remove the render variable property on the layout calculation or you select prompt values and then carry out a drill activity

To avoid this, create a caption() member, set that as a property of the row member and modify the condition to be based on this member.

Implementing Default Date Prompt Scenario in Report Pages using Javascript and Render Variable Concept

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.