Wednesday, September 5, 2012

Show or Hide Multiple Date Prompts using Radio Button Selection

Keywords: Date Prompt - Show or Hide - Radio Button - Javascript - Report Studio - Cognos 8

Business Case:
Many a times, you may want to give users the option to run the report for predefined date ranges (prior month, prior year etc) or enter custom dates. When the user selects a predefined option, the date prompt should be disabled. When the user selects "Custom" the date prompt should be enabled. This needs to be done to avoid confusion in the prompt page.

Example:
A daily scheduled report runs for the prior day. Users want to use the same report for custom dates as well. If they want to run the report for a historical data, they should be able to select a date or date range.


Solution:
We will be using Javascript to manipulate the Radio Button and date prompts. We will attach an HTML "On Change" event to the radio buttons, which will trigger a function. Depending on the selection, the function will disable or hide the date prompt.

Implementation:
  • To start with, create a radio button with necessary static values. I have used the following: "Custom", "Yesterday", "Last 30 Days" and "Last Calendar Month". These are the display values. I have set the corresponding use values to: custom, yesterday, 30_days and last_month
  • Set the "Default Selections" property to "custom"
  • Set the "Required" property to "No". This is very important. If this is not done, the "Finish" button will not be enabled.
  • Create two Date Prompts and set the "Required" property to "No". Set "Select UI" option of the prompts to "Edit box". 
  • Let's set the parameter name for these prompts to start_date and end_date
  • We will need 5 HTML Items in the prompt page.
  • Place the first HTML item immediately before the start_date prompt and the second HTML item immediately after the start_date prompt.
  • Do the same thing for the end_date prompt.
  • The pair of HTML items before and after the prompts will create a handle for the prompts.
  • Place the last HTML item at the end of the page. This will contain the main script. 
  • I created a table and placed the prompts inside them. When you have done this, the prompt page layout will look like this:
  •  Place the following script in HTML Item 1:
<span id="start_date_prompt">
  •  Place the following script in HTML Item 2:
</span>
  • Script in HTML Item 3:
<span id="end_date_prompt">
  • Script in HTML Item 4:
</span>
  • Place the following script in HTML Item 5, the main script:
<script type="text/javascript">
var inputs = document.getElementsByTagName("input");
var list_box = new Array();
var radio_buttons = new Array();
j=0;

for(i=0;i<inputs.length;i++)
{
    if(inputs[i].type=='radio')
    {
        radio_buttons[j] = inputs[i];
        j++;
    }  
}

radio_buttons[0].setAttribute("onclick",function(){ToggleDate();});
radio_buttons[1].setAttribute("onclick",function(){ToggleDate();});
radio_buttons[2].setAttribute("onclick",function(){ToggleDate();});
radio_buttons[3].setAttribute("onclick",function(){ToggleDate();});

var start_date_prompt = document.getElementById("start_date_prompt").childNodes[0];
var end_date_prompt = document.getElementById("end_date_prompt").childNodes[0];

function ToggleDate()
{
    if(radio_buttons[0].checked)
    {
     //document.getElementById("start_date_prompt").style.display = '';
     //document.getElementById("end_date_prompt").style.display = '';
     start_date_prompt.disabled = '';
     end_date_prompt.disabled = '';
    }
    else
    {
        //document.getElementById("start_date_prompt").style.display = 'none';
        //document.getElementById("end_date_prompt").style.display = 'none';
        start_date_prompt.disabled = 'true';
        end_date_prompt.disabled = 'true';
    }
}
</script>
  • Now when you run the report depending on the radio button you select, the date prompt will be disabled on enabled.
  • If you want to hide the date prompt instead of disabling it, comment out (add // in front) the following lines
start_date_prompt.disabled = '';
start_date_prompt.disabled = 'true';
  • and uncomment (remove // from front) the following lines:
//document.getElementById("start_date_prompt").style.display = '';
//document.getElementById("
start_date_prompt").style.display = 'none';
  • Note that the code for disabling works only for date prompts, whereas the code for hiding works for any element that you put between the two HTML items. 
  • The last step is to create a filter the combines both the prompt selections.
  • This filter should be set to "Optional".
  • In my example, the prompts are named as date_type (radio button) and start_date (start date prompt) and end_date (end date prompt). The filter expression will be:
(?date_type? = 'custom' and [Order Date] = cast(?report_date?,date))
OR
?date_type? = 'yesterday' and [Order Date] between trunc(_add_days(current_date,-1)) and
trunc(_add_days(current_date,-1))
OR
?date_type? = '30_days' and [Order Date] between trunc(_add_days(current_date,-30)) and
trunc(_add_days(current_date,-1))
OR
?date_type? = 'last_month' and [Order Date] between trunc(_first_of_month(_add_months(current_date,-1))) and trunc(_last_of_month(_add_months(current_date,-1)))
Note: Refer to "Use the Same Report for Scheduling and Manual Run" to see more details about how to structure the filter expressions in these cases.
  • When you run the report, the prompt page will look like this:

  • Since "Custom" is the default selection you can see that the date prompts are enabled. See below:
  • When any other option is selected the date prompt will be disabled, like this:
  • Once the prompt page is done, you would want to add the date selection in the report page, so that users know what range they selected.
  • I do this by creating 2 data items in the report query: Start Date and End Date
  • The expression for Start Date would be like this:
Case
  When ?date_type? = 'custom' Then cast(?start_date?,date))
  When ?date_type? = 'yesterday' Then trunc(_add_days(current_date,-1))
  When ?date_type? = '30_days' Then trunc(_add_days(current_date,-30))
  When ?date_type? = 'last_month' Then trunc(_first_of_month(_add_months(current_date,-1)))
End
  • The expression for End Date would be:
Case
  When ?date_type? = 'custom' Then cast(?end_date?,date))
  When ?date_type? = 'yesterday' Then trunc(_add_days(current_date,-1))
  When ?date_type? = '30_days' Then trunc(_add_days(current_date,-1))
  When ?date_type? = 'last_month' Then trunc(_last_of_month(_add_months(current_date,-1)))
End
  • You can now create two singleton items on the report page and drag these data items to the page.
  • Let's say you want one of the options to say "No Date Filter" (Use Value: "all") for example, add this to the radio button prompt and update the filter expression to say:
    (?date_type? = 'custom' and [Order Date] = cast(?report_date?,date))
    OR
    ?date_type? = 'yesterday' and [Order Date] between trunc(_add_days(current_date,-1)) and
    trunc(_add_days(current_date,-1))
    OR
    ?date_type? = '30_days' and [Order Date] between trunc(_add_days(current_date,-30)) and
    trunc(_add_days(current_date,-1))
    OR
    ?date_type? = 'last_month' and [Order Date] between trunc(_first_of_month(_add_months(current_date,-1))) and trunc(_last_of_month(_add_months(current_date,-1)))
    OR
    ?date_type? = 'all'