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'

Thursday, June 7, 2012

Explaining Report Studio SQL Object

Keywords: SQL Object, Report Studio, Query, IBM Cognos

SQL Objects are one of the most underused features of report studio. The main reasons for this are that, even advanced users are generally not proficient in SQL or report authors are not familiar enough with the data source to write SQL Queries.

Note:
  • SQL Objects are hard to maintain since analyzing publish impact from framework manager will not catch tables or columns used in SQL objects.
  • Since SQL objects are at the report level, model administrators are usually not aware of what tables the users are using if SQL objects are used. If you need to secure tables, do it at the database level and restrict access to the cognos user (the one used in the data source connection).
That said, SQL objects can come in handy in many scenarios. Some examples:
  • User wants to query database tables that are not included in the package
Not all database tables are necessarily included in the published package. This might be because the tables don't join with the tables in the package or has unrelated information. If the users want to query such tables for one-off reports, then they can simply write a sql query against the underlying data source.
  • User wants to compare data between multiple data sources
If users are creating reconciliation reporting between multiple data sources (dev vs. qa vs. prod), then they can simply multiple sql objects each pointing to a different data source and join these queries to compare the data.
  • User wants to use complex database functions in the report
Although you can use database functions in report studio, not all functions are available. For example, I had to use an oracle function called wm_concat to concatenate values. This was an aggregate function similar to sum or avg. I couldn't find this function in cognos or find an equivalent function, so I used a SQL object and wrote the query myself.
  • User wants to use a different join than what is defined in the package
Users might want to use a different kind of join (inner vs outer) or join tables based on different criteria than what is defined in the package. SQL queries let you control the join conditions.
These are just some examples. Once you start using them, you might find other scenarios where SQL objects may prove useful.

Now let's see how to use them.
  • Mouse over the Query Explorer pane and click on "Queries". In the "Insertable Objects" pane, you can find the object "SQL". You can drag and drop it in the Queries section or double click SQL.
  • You don't have to manually create a query before adding a SQL object. If you directly add a SQL object, Cognos will automatically create a query and place the SQL object as a child of that query.
  • Following are the properties of a SQL object
  • SQL Syntax: You can keep the sql syntax as Native as long as you know the syntax of your database.
  • Data Source: Pick the data source that you want to run the query against. You have to pick the data source before you enter the query, otherwise cognos will not be able to validate the query and will throw an error message
  • SQL: Do not use delimiters like semi-colon at the end of the query.
  • The first time you enter the query, the columns from the SQL are automatically added to the parent query. If you make any subsequent changes to the query like adding or removing columns, you have to manually update the query.

Thursday, April 26, 2012

Sort List Report Dynamically using Javascript


Keywords: Dynamic Sorting OR List Report OR Javascript

Business Case:
User wants to sort the list report output based on list columns dynamically, after the report output has been rendered in HTML format.

IBM Solution:
IBM has an article for doing the same thing. I found that this article was extremely complicated with a lot of steps. So, I wrote this script from scratch, which in my opinion is much simpler to implement.  Here's the link to IBM's article in case you are interested.

http://www.ibm.com/developerworks/data/library/cognos/reporting/scripting_techniques/page515.html?ca=drs-

Solution:
  • Most of the work here is done by the Javascript.
  • The script goes through each row and loads the data into an array.
  • When the column titles are clicked, the array is sorted in memory and the script re-writes the data into the list.
  • In this example, my report has 2 columns.
  • The column names in list report header will be clickable.
  • Initially, the data is unsorted. Clicking the column header will sort the list based on that column in ascending order.
  • Clicking the column header again will sort in descending order.
  • After that the sort order keeps toggling.
Note:
  • If sorting is required before running the report, prompts can be used.
  • The solution provided here is only for simple sorting. Sorting is by one column at a time only. Excel style advanced sorting cannot be done with this method (E.g. Sort by Column 1 Ascending, Column 2 Descending).
  • Only data on the current page will be sorted, so if you have data spanning more than one page, set the "Rows per Page" property of the list report to a high number (e.g. 10000) so that all the data is displayed in one page.
Implementation:
  • There are a total of 4 scripts.
  • Script 1:
    • This is a placeholder to identify the list report. The main script will look for this script, and access the data inside it.
    • Unlock the cells and place an HTML item in the first column header.
    • Paste the following script inside this HTML item
    <span id="list1"></span>
  • Scripts 2 & 3 are wrapper scripts for the column headers. This will make the column headers clickable.
  • Script 2:
    • This is the first part of the wrapper script.
    • Place an HTML items before each column header and paste the following code inside it.
    <a href="JavaScript:sortList(1)">
    • Note: The number inside the "sortList()" function should match the column number. So it should be sortList(2) for the second column, sortList(3) for the third column and so on.
  • Script 3:
    • This is the second part of the wrapper script.
    • Place an HTML items after each column header and paste the following code inside it.
    </a>
  • Script 4:
    • This is the main script that does all the sorting.
    • The first section of the report has 3 variable declarations that you have to edit according to your report.
    • "var column_count = 2" indicates the number of columns in your report. If you add more columns, change this value.
    • "var current_sort = new Array("","")" is the initial state of the sorting. If you have 3 columns, put three "" separated by commas.
    • "var column_names = ["Group","Market"]" is an array with column names. If you note in the screenshots below, the column names are different (Market Group instead of Group). This is for script reference only and doesn't have to match with the list column names.
    • The rest of the script should be used as is.
    • You can download this script here.
    • Place an HTML item after the list report (outside the list) and copy/paste the following code:
    • <script type="text/javascript">
      /*******************   Customize this section according to the report   ***********************************/
      var column_count = 2; //Number of columns in the report
      var current_sort = new Array("",""); //This array should contain one empty value per column.
      var column_names = ["Group","Market"];  // List the names of columns which should be sortable
      /**********************************************************************************************************/

      var list_cell = document.getElementById("list1").parentNode; // Lookup the placeholder span item
      var list_row = list_cell.parentNode; // Create Handle for the header row which contains the span item
      var list_table = list_row.parentNode.parentNode; // Create Handle for the table

      var rows = list_table.getElementsByTagName("tr");  //Get List of rows in the table
      var row_count = rows.length; // Get row count

      var list_array = new Array(); // Create parent array. Each element of this array will represent a row in the list report.

      for (i=1; i<row_count;i++) //Loop through the rows. Skipping header row and starting from 1
      {
          columns = rows[i].getElementsByTagName("td");
          var list_row_array = new Array();  // Create child array. Each element of this array represents a column on the row.
          for(j=0;j<column_count;j++) // Loop through the columns for each row
          {
              // Read value from the cell and store it in array
              list_row_array[column_names[j]] = columns[j].getElementsByTagName("span")[0].innerHTML;
          }
           list_array[i-1] = list_row_array;
      }

      // Main Function used for sorting.
      function sortList(col_num)
      {
          if(current_sort[col_num-1] == '' || current_sort[col_num-1] == 'desc'){
              list_array.sort(dynamicSortAsc(column_names[col_num-1]));
              current_sort[col_num-1] = 'asc';
          }
          else{
              list_array.sort(dynamicSortDesc(column_names[col_num-1]));
              current_sort[col_num-1] = 'desc';
          }

          for(i=0; i<list_array.length;i++)
          {
              columns = rows[i+1].getElementsByTagName("td");
              for(j=0; j<column_count; j++)
              {
                  columns[j].getElementsByTagName("span")[0].innerHTML = list_array[i][column_names[j]];
              }
          }
      }

      // Sub function used for sorting. Don't change.
      function dynamicSortAsc(colIndex) {
          return function (a,b) {
              return (a[colIndex] < b[colIndex]) ? -1 : (a[colIndex] > b[colIndex]) ? 1 : 0;
          }
      }

      function dynamicSortDesc(colIndex) {
          return function (a,b) {
              return (a[colIndex] > b[colIndex]) ? -1 : (a[colIndex] < b[colIndex]) ? 1 : 0;
          }
      }
      </script>
  • Once you have placed all the scripts, your report layout should look like this:
  •  When you run the report the initial output will be like this:
  •  Clicking on "Market Group" will sort the list in ascending order by Market Group and the report will look like this:
  •  Clicking on "Market Group" again will sort the list in descending order by Market Group and the report will look like this:
  •   Clicking on "Market" will sort the list in ascending order by Market and the report will look like this:
  • To sort by Market in descending order, click Market again:

Wednesday, March 14, 2012

Create Chart with Rolling Totals Data

Business Case:
User wants to create a chart report that displays rolling 12 months revenue by for current year (In this example 2010). Although 2009 revenues should be used in calculating the rolling totals, the chart should display only 2010 months.

Implementation:
  • Create a base query with data for Prior Year (2009) and and Current Year (2010). My data sample looks like this:

  • Set the aggregate property of Revenue in the Base Query to "Total".
  • Add a data item called [Dummy] with expression 'Dummy'.
  • Create a new query, call it CY and drag the base query as a short cut
  • Add the data items from the base query to CY query.
  • Add a filter in 'CY' query to filter only for current year's data. In this example, I added:
extract(year, [Order Date]) = 2010
  • Create a new query called summary and add a join element.
  • Add Base query and CY to the join element. The queries should look like this:

  • Join the queries based on the data item [Dummy]. We are basically creating a cartesian product, but since cognos needs a join, we are using a dummy column.
  •  Now that we have created a cartesian product, we should limit the data to rolling 12 months. So add this filter to the Summary query:
[Base Data].[Order Date] between _add_months([CY].[Order Date],-11) and [CY].[Order Date]
  • Add the [Date] from 'CY' query and revenue from "Base Data" Query.
  • Create the chart based of the "Summary" query.  The report output will look like this:
  •  If you compare the tabular data and the base data above, you will see that each month's revenue includes that month and the previous 11 months.

Wednesday, March 7, 2012

Hide "Select All / Deselect All" Link in Value Prompts

Keywords: Value Prompt - Show or Hide - Select All / Deselect All - Javascript - Report Studio - Cognos 8.3 - Cognos 8.4

Business Case:
Business users want  the "Select All / Deselect All" link displayed under mulit-select Value Prompts to be hidden.  This is usually requested when the value prompt has hundreds or thousands of values and they shouldn't all be selected at once.

Note: To limit the number of options a user can select at a time, refer to the script in this article: Validate Value Prompt using Javascript

Implementation:
The links displayed in the prompt page are HTML elements. So, we can use Javascript to hide them. There are two different scripts that can be used to achieve this.


Note: The scripts in this article are available in IBM Knowledge Base (Link to IBM Knowledge base Article.)

Script 1: Use IBM's built in Object Names and Methods to hide the link
  • The script works in Cognos 8.3 and 8.4
<script type="text/javascript">
var a = document.getElementsByTagName("A");
for( var i = a.length-1; i >= 0; i-- )
{
    var link = a[i];

    if( typeof(link.id) == "string" && link.id.match(/PRMT_(SV|LIST)_LINK_(DE)?SELECT_/) )
    {
        link.parentNode.removeChild(link);
    }
}
</script>
  • To use this script, place an HTML Item after the value prompt and place the script inside this item.
  • The advantage of this script is that, it's a simple one step approach.
Script 2 - Alternate more generic approach (not dependent on internal prompt names)
  • Add an HTML item to the left and right of the value prompt in the Report Studio
  • For the first HTML item (left) add the following script:
<span id = "A1">
  • For the second HTML item (right) add the following script:
</span>
  • Add an HTML item at the bottom of the prompt page next to "Run" button and add the following script for the third HTML item:
<script>
var theSpan = document.getElementById("A1");
var a = theSpan.getElementsByTagName("A");

for( var i = a.length-1; i >= 0; i-- )
{
    var link = a[i];
    link.parentNode.removeChild(link);
}
</script>

Thursday, March 1, 2012

Show or Hide Date Prompts based on 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. E.g. "Yesterday" and "Custom"
  • Set the "Required" property to "No". This is very important. If this is not done, the "Finish" button will not be enabled.
  • Create a Date Prompt and set the "Required" property to "No".
  • We will need 3 HTML Items.
  • Place the first HTML item immediately before the date prompt and the second HTML item immediately after the date prompt.
  • These two HTML items will create a handle for the date prompt.
  • Place the third 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="date_prompt">
  •  Place the following script in HTML Item 2:
</span>
  •  Place the following script in HTML Item 3, the main script:
<script type="text/javascript">
var inputs = document.getElementsByTagName("input");
var list_box = new Array();

/****Identify Radio Buttons and load them into an 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++;
    }  
}

/****Set the onclick event of each radio button option to trigger our custom function****/
radio_buttons[0].setAttribute("onclick",function(){ToggleDate();});
radio_buttons[1].setAttribute("onclick",function(){ToggleDate();});

/****Create a handle for date prompt****/
var prompt = document.getElementById("date_prompt").childNodes[0];

function ToggleDate()
{
    if(radio_buttons[0].checked)
    {
        /**** First options (in our case "Custom") is selected.****
         **** Date Prompt will be Shown or Enabled.            ****/

        //document.getElementById("date_prompt").style.display = '';
        prompt.disabled = '';
    }
    else
    {
        /**** Second options (in our case "Yesterday") is selected.****
         **** Date Prompt will be Hidden or Disabled.              ****/

        //document.getElementById("date_prompt").style.display = 'none';
        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
prompt.disabled = '';

prompt.disabled = 'true';
  • and uncomment (remove // from front) the following lines:
//document.getElementById("date_prompt").style.display = '';

//document.getElementById("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 report_date (date prompt). The filter expression will be:
(?date_type? = 'custom' and [Order Date] = cast(?report_date?,date))
OR
?date_type? = 'yesterday' and [Order Date] = trunc(_add_days(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:

  • When the "Custom" option is selected the date prompt will still be enabled. See below:
  • When the "Yesterday" 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 a data item in the report query: Report Date
  • The expression for Report Date would be something like this:
Case
   When ?date_type? = 'custom' Then cast(?report_date?,date))
   When ?date_type? = 'yesterday' Then trunc(_add_days(current_date,-1))
End
  • You can now create a singleton item on the report page and drag this data item to the page.
In the next article, I will post the script to do the same thing using a drop down value prompt instead of Radio Buttons.

Friday, February 17, 2012

Create a Report with Collapsible Hierarchy


Keywords: Collapsible Hierarchies OR Expandable List Report OR Tree Structured Report

Business Case:
Collapsible hierarchies are useful when you have multiple levels of grouping and subtotals in a report and you don't want to display all the data at once.  When the report loads, it should display only the top level data. Users should be able to expand the group value that they are interested in and the sub groups for the expanded groups should display. If there is another level within the subgroup, then the users should be able to expand the subgroups as well.

In Essence, the Report Studio list report should behave in a fashion similar to, but not the same as, an Analysis Studio report.  This is an indirect way of achieving drill down functionality for relational data. This can be achieved using a drill through report with itself as target, but the advantage of this approach is that the uses will see the results instantly without having to rerun the report.

Environment: Cognos 8.4.1, Report Studio, Javascript, HTML

Example:
To understand the business case better, look at the following example. Here's how the tabular data looks like:
We have three levels: Group A, Group B and Group C. The report should be grouped by Groups A and B. A traditional grouped report will look like this:
This is fine if the number of rows is less. As the data set grows, there might be too much data crammed in the report that the users can't analyze the data better.

Once the collapsible hierarchy solution is implemented, here's how the report will look like when it loads:
The user will see only the high level information. Looking at this, it is evident which group is performing well and which one is not. Now the users can drill down into the group they are interested in by just clicking the + icon next to the group.  When the groups are expanded, the report will look like this:
Now the users can see the subgroups in the respective groups. Since this is a two level grouping, the user can drill down one more level deeper to see the detailed data, which will look like this:
Users can collapse the groups by clicking on the - icon next to the parent.

Solution:
The collapsible hierarchies are build using Javascript on traditional list report. Since this is javascript based, it can work only when the user executes the report in HTML format.  See how the report looks when the same report is executed in PDF format:
It looks very similar to the report in HTML format when all the groups are expanded, but the +/- icons are missing.

Note:
You need the image files for the "plus" and "minus" signs. The javascript code used here references expand_plus.gif and expand_plus.gif in the <c8_installation_directory>/webcontent/pat/images folder.  If your environment doesn't have these files you download them from the links below and place it in the correct folder on your server:

Implementation:
We start by preparing the layout for a collapsible structure. Even though the columns Group A and Group B are grouped in this report, you will notice that the detail section of this report has individual cells instead of a merged cell. This can be achieved by following these steps:
  • Create a simple list report with all the required columns. (I am going to refer to the grouped columns as A and B. Substitute accordingly in your case.)
  • Group columns A and B.
  • From the "Structure" menu, go to "Headers & Footers --> List Headers & Footers". Alternatively, you can use the "Headers & Footers" icon in the toolbar.
  • In "List Headers & Footers", check the following items:
    • List Header
    • List Footer
    • Group A Header
    • Group B Header
  • So far, the list report layout should look like this:
  • Now, CUT (don't delete) the details section of Group A and Group B (see selected items in the above image).
  • Unlock the items (using the icon in the toolbar or from Structure menu) and delete the texts "List Page Header" and "List Page Footer". The layout should now look like this:
  •  Next lock the items and from the Insertable Objects Pane --> Data Items tab, add back Group A and Group B to the beginning of the list report.
  • Once again Unlock the items and CUT "Group A" and "Group B".
  • Then, one after the other, select Group A Header and Group B Header and click "Split Cell". After this, all rows of the list report, detail and summary will be evenly spaced without any merged cells and the report should look like this:
  • Next, unlock the cells and drag "Group B" to the second column. 
  • Now the first cell of the rows "Group B" and "Group C" are empty. We need to add a blank text item, so that the javascript can recognize it as a child row and collapse it.
  • Drag a text item to first cells of Group B and Group C rows. You don't have to enter any text, just put a few white space characters (I put 2 spaces).  See screenshot below:
  • Now comes the Javascript code. We will be using 3 scripts.
  • The first script, which is also the main script will be placed in the "List Header". In the image above, it's the first blank row below the titles. Let's call this "Script 1".
  • Unlock the cells, drag an HTML item to the List Header and place the following script inside:
<!-- Script 1: Master Script -->
<span id="ExpandedTable"> </span>
<script>

var displayStyle = "";
var noDisplayStyle = "none";

var currentRow = 0;

// This function expands/collapses a table where the first columns have +/- icons
function ExpandCollapse(el,loc) {
        // change icon
       el.src = "../pat/images/expand_" + (isPlus(el) ? "minus" : "plus") + ".gif";
       var tr = el.parentNode.parentNode; // the current row

       currentRow = tr.rowIndex;
       showLines(el);
}

function showLines(el) {
       var show = true;  // to show lines or not

       // Grab the ROW that was clicked and the TABLE that contains it
       var tr = el.parentNode.parentNode; // the current row
       var tbl = tr.parentNode.parentNode; // the table
       var cid = el.parentNode.cellIndex; // the index of the column

       if (isPlus(el)) {
              show = false;
       } else {
              show = true;
       }

       while (currentRow < tbl.rows.length-1) {
              currentRow++;
              var trCurrent = tbl.rows[currentRow];
              var index = findIconInRow(trCurrent, 0); // return the index of the first img
              if (index >= 0 && index <= cid) {
       trCurrent.style.display = displayStyle;
currentRow--;
return; // found the next line with icon in the same column; return
              }

              if (!show) {
trCurrent.style.display = noDisplayStyle;
              } else {
trCurrent.style.display = displayStyle;
var iconIndex = findIconInRow(trCurrent, cid + 1);
if (Number(iconIndex) > -1) {
       var icon = trCurrent.cells[iconIndex].firstChild;
       showLines(icon);
}
              }
       }
}

function isPlus(el) {
return el.src.indexOf("minus") == -1;
}

function hasIcon(cell) {
// return true if this cell has an img
if ((Number(cell.childNodes.length)) == 0) {
return false;
}
var c = cell.firstChild;
if (c != null) {
return (c.tagName == "IMG");
}
return false;
}

function findIconInRow(trCurrent, cid) {
for ( var i = cid; i < trCurrent.cells.length; i++) {
if (hasIcon(trCurrent.cells[i])) {
return i;
}
}
return -1;
}

function StartHidden() {
       var q=document.getElementById("ExpandedTable");

       // get the table
       tbl = q.parentNode.parentNode.parentNode.parentNode;
       var isFirstRow=true;

       for ( var i = 0; i < tbl.rows.length; i++) {
              var trCurrent = tbl.rows[i]; // the current row
              var d = trCurrent.cells[0]; // first cell
              var c = d.firstChild;

              if (i < 2 || c.tagName.indexOf("IMG") != -1) {
                     c = c.src; // leave it visible, since it has + icon
for ( var j = 1; j < trCurrent.cells.length; j++) {
       trCurrent.cells[j].width="1";
}
              }
              else {
                     trCurrent.style.display = "none"; // hide the row
              }
       }
}

</script>
  • With the first script in place, the report layout will look like this:
  •  The second script is a wrapper script that will be placed around "Group A" and "Group B". This script will make the entries clickable. Let's call them Scripts 2a and 2b.
  • With the cells unlocked, drag two HTML items, one before the data item and one after. See screenshot below for better understanding:
  •  Place the following code in Script 2a:
<!-- Script 2a: Wrapper Script -->
<img onclick='ExpandCollapse(this,"A_img")' src='../pat/images/expand_plus.gif' style='cursor:pointer;vertical-align:middle; margin-right:2px'/>
<span onclick='ExpandCollapse(this.parentNode.firstChild,"GroupA")' style='cursor:pointer'>
  •  Place the following code in Script 2b which completes the wrapper script:
<!-- Script 2b: Wrapper Script -->
</span>
  • The last script (Script 3) will be placed in the list footer. This script will collapse all the headers when the report loads.  All it does is to call the StartHidden() function defined in the Mater Script.
<!-- Script 3: Start Hidden -->
<script>
StartHidden();
</script>
 Now, if you run the report, it will load with all the groups collapsed (as shown in the 3rd image from top).


Tuesday, February 7, 2012

Update Report Specification for Multiple Reports without SDK

Business Case:
The business users decided to change the name of a data item in the Cognos package. Unfortunately there were about 20 reports that used this data item, which means the report author has to open each of these reports and repoint the query item to the correct one.

Background:
If you are not familiar about what a Report Specification is and would like to get a better understanding, read this article first: Explaining Report Specification

You may also be interested in learning how to quickly update a single report with multiple queries for data item name changes. Check out this article: Update Report Specification without SDK

Example:
Let's take the first business case I mentioned above.  Suppose that you have 20 reports have the query item, [Billing Address]. On top of this the term "Billing Address" is used in multiple pages and multiple places in the reports (header, column title, foot notes etc). The report user comes to you, the author, and requests you to change all references to Billing Address to Shipping Address, which already exists in the package.

Solution:
We will be using the Report Deployment option in Cognos to do the bulk update. Note that you will need access to Content Administration to be able to use this approach.

Implementation:
In this case, editing the report in report studio to find every instance of [Billing Address] and changing it to [Shipping Address] is a tedious process. It is highly inefficient too. Here is a quick way to change the report.
  • Prepare the Deployment Content:
    • In the "Public Folders", create a folder called "Deployment".
    • Copy all the reports that need to be updated into this folder.
  • Create Export Job:
    • From the "Launch" menu in Cognos Connection, click "IBM Cognos Administration".
    • Select the "Configuration" tab and click on "Content Administration" on the left pane.
    • Create a new "Export" and specify a name of your choice. For our example, let's call it, "Report Spec Update"
    • In the next screen, under "Deployment Method", select the option "Select public folders and directory content" and click "Next".
    • Under "Public folders content" section, click "Add" and add the "Deployment" folder.
    • Select the appropriate options from the "Options" section.
    • Go thorough the rest of the screens like you normally would and enter a name for the archive in the last screen (E.g. report_spec_update).
    • Save and Run the export job once.
  • Update the Specification:
    • Login to the Cognos server (Use remote desktop connection if Windows, SSH if Linux).
    • Navigate to the deployment folder: <cognos_install_dir>/deployment
    • You will find a zip file with the archive name you specified when creating the export (report_spec_update.zip).
    • Copy the zip file to a different folder.
    • Unzip the contents of the zip file. It will produce 3 files: content.xml, exportRecord.xml and package1.xml.
    • The file we are interested is the package1.xml. Open this file in a text editor. 
    • Note: This is the most important step of the entire process. If your cognos server is running on a linux environment, then the editing must be done in the linux server itself, in vi editor. Do not copy the zip file or the xml files to your windows desktop for editing since this screws up the file encoding.
    • Once you have the file open in the text editor,  do a find and replace for the data item name that you are trying to update. For example, replace all occurrences of [Business View].[Customer].[Billing Address] with [Business View].[Customer].[Shipping Address]
    • Save the file package1.xml.
    • Zip the 3 files to a new archive.
    • Copy the zip file back to the Deployment folder in the Cognos server.
  • Create Import Job:
    • Log in to Cognos Connection and navigate to Content Administration.
    • Create a new Import and from the list of archives, select the new zip file that you just created.
    • Run the Import.
    • The reports in your "Deployment" folder will be updated with the changes you made in the XML file.
That's it !!! The same task, if done manually would take hours to complete and you would not be sure if you updated all the places. The steps above might seem like a long process but when I tested this, the entire process took me about 5 minutes.

If you are in a daring mood, you can expand the approach to the entire content store instead of exporting only the Deployment folder. As long as you keep a copy of the zip file you exported, you are fine.

If you have made a mistake during the update, or if you find out that you have changed more reports than necessary, you can always import the original export back again and you will be right where you started.

Remember.. There is more than one way to skin a cat.. Let's do it the easy way.

Monday, January 30, 2012

Hide Toolbar Buttons in Cognos Viewer

Business Case:
Report Author wants to hide the individual toolbar buttons found in Cognos Viewer or the entire toolbar.

Environment: IBM Cognos 8, Report Studio, Javascript, HTML Format, Internet Explorer/Mozilla Firefox.

Examples:
There are many reasons why a report author might want to hide the buttons or the toolbar. For example, if a report is a drill through target report, then the users shouldn't be able to run the report again from within the cognos viewer as parameters will not be passed.

Another example is, if the drill through report can be viewed only in one format, then the author might want to hide the Report Formats button.

Implementation:
The toolbar inside the Cognos Viewer and it's buttons are nothing but HTML elements. So, we can use Javascript to hide them. There are two different scripts that can be used to achieve this.

Script 1 - Use IBM's built in Object Names and Methods to hide individual elements:
IBM provides this script in their knowledge base. I am using a shorter and slightly modified version of this script here:
<script language="JavaScript">
// Licensed Material - Property of IBM
// © Copyright IBM Corporation 2003, 2009

function hideKeepVersion(){
  document.getElementById("_NS_keepThisVersion").style.display = "none";
}

var tbCurrent = null;
var hideTheKeepVersionButton = true;
var y=getFormWarpRequest().elements["cv.id"];

// RS means it has been launched from Report Studio
// _NS_ means it has been launched from Cognos Connection
if(y.value == "RS")
  tbCurrent = oCVRS.rvMainWnd.getToolbar();
else
  tbCurrent = oCV_NS_.rvMainWnd.getToolbar();

if(y.value == "_NS_" && hideTheKeepVersionButton)
  setTimeout("hideKeepVersion()","50");

var tbItems = tbCurrent.m_items;

for (var item in tbItems){
  // Hide Drill Up and Drill Down Buttons
  if (tbItems[item].m_id == y.value + 'drillUp' || tbItems[item].m_id == y.value + 'drillDown')
  {
    tbItems[item].hide();
  }

  // Hide Open With Button
  if (tbItems[item].m_id == y.value + 'openWith')
  {
    tbItems[item].hide();
  }

  // Hide Run Report Button
  if (tbItems[item].m_id == y.value + 'runReport'){
    tbItems[item].hide();
  }

}
</script>
  • The advantage of this script is that it provides a handle for each of the toolbar buttons individually.
  • On the flip side, since these are built in objects, it doesn't give much room for tweaking or manipulating and it's more of a use as is approach.
  • Link to IBM Knowledge base Article.
Script 2 - Use HTML DOM objects to hide the toolbar:
This is a much simpler script. The script scans the HTML page and looks for the toolbar. It then hides the toolbar completely.
<script type="text/javascript">
var tables = document.getElementsByTagName("table");
var tds = document.getElementsByTagName("td");

for (var i=0;i<tds.length;i++){
  if(tds[i].id.indexOf("CVToolbar") == 0){
    alert("Found ToolBar: " + tds[i].id + " - " + tds[i].id.indexOf("CVToolbar"));
    tds[i].style.display = 'none';
  }
}
</script>

How to use the scripts:
  • Create an HTML element at the end of the page and place the script in there.
  • The script will work in both Firefox and Internet Explorer.

Sunday, January 29, 2012

Explaining Report Specification

Report Specification:

From Cognos point of view, when we say "Report", we are referring to a "Report Specification". When you create a report in Cognos, you are not storing the report output or data or even the query. 

Let me be clear; you can save the report output in cognos, but this is not what you typically do when you create a report. When you create and save a report what you are saving in your "Public Folder" or "My Folder" is an XML specification that contains details about the report.

The details include things like:
  • the package your report is associated with
  • the location where the report is stored
  • the location of the package
  • list of queries, query items, filter expressions etc.
These details are stored in plain text as an XML file.

For example, a blank report you save in the cognos portal will have the following specification:

<report xmlns="http://developer.cognos.com/schemas/report/6.0/" expressionLocale="en-us">
<modelPath>/content/folder[@name='Packages']/package[@name='Enterprise Data Warehouse']/model[@name='model']</modelPath>
<drillBehavior modelBasedDrillThru="true"/>
<layouts>
<layout>
<reportPages>
<page name="Page1">
<style>
<defaultStyles>
<defaultStyle refStyle="pg"/>
</defaultStyles>
</style>
<pageBody>
<style>
<defaultStyles>
<defaultStyle refStyle="pb"/>
</defaultStyles>
</style>
<contents/>
</pageBody>
</page>
</reportPages>
</layout>
</layouts>
<XMLAttributes><XMLAttribute name="RS_CreateExtendedDataItems" value="true" output="no"/><XMLAttribute name="listSeparator" value="," output="no"/></XMLAttributes></report>

You can see some of the details that I mentioned about above.  The report specification can be viewed in Report Studio by clicking the "XML" icon on the toolbar or by selecting "Copy Report to Clipboard" option from the Tools menu.

Any change you make inside report studio like adding a query item, changing the font to bold or adding a new table in the report page will be reflected in the report XML.  When you save the report, Cognos saves this specification in the content store.

Report Execution:
When a report is "Run" or executed, cognos generates the SQL from each of the queries in the report, passes the queries to the source database and formats the output according to your report layout. Note that all these happen when you execute the report and not when you save the report.

Tuesday, January 24, 2012

Update Report Specification Without SDK

Business Case:
There were many times, when I wanted to do update the specification of one or more reports. In one instance, a complex report with close to 10 queries needed to be modified because it was using the incorrect revenue field.

In another case, the business users decided to change the name of a data item in the Cognos package. Unfortunately there were about 20 reports that used this data item, which meant I have to edit each of these reports and repoint the query item to the correct one.

In yet another case, a filter expression in a report with a large number of queries had to be modified.

Report Specification:
In order to keep this article short, I have moved the explanation of Report Specification to a separate post. If you would like to understand what a report specification is, read this article first: Explaining Report Specification

Modifying the Report Specification:
When a report needs to be modified, we usually edit the query or page in Report Studio and make the necessary changes to the layout or report expression. This is fine as long as you have to change just one expression or formatting in one place. But this is not always the easiest way.

Example:
Let's take the first business case I mentioned above.  Suppose that you have a report with 10 queries that have [Billing Address]. On top of this the term "Billing Address" is used in multiple pages and multiple places in the report (header, column title, foot notes etc). The report user comes to you, the author, and requests you to change all references to Billing Address to Shipping Address, which already exists in the package.

In this case, editing the report in report studio to find every instance of [Billing Address] and changing it to [Shipping Address] is a tedious process. It is highly inefficient too. Here is a quick way to change the report.
  • Open the report in Report Studio
  • From "Tools" menu, click "Copy Report to Clipboard". This will copy the report specification (read, Report XML) to the clipboard.
  • Open a text editor like Notepad or Textpad and paste the XML code.
  • Do a simple "Find and Replace" in your text editor. Find all instances of "Billing Address" and change to "Shipping Address" (don't use the double quotes).
  • Note that since we are looking for the text "Billing Address", you will be changing everything that has "Billing Address" including column titles, report header, query item names etc.
  • If you want to change only the report expressions, add square brackets when you find and replace, "[Billing Address]" to "[Shipping Address]".
  • Now "Select All" in your text editor and "Copy" (CTRL+C).
  • Open a new report studio window and from the Tools menu, select "Open Report from Clipboard".  This will open a new report.
  • You can now validate the report and save it (either overwrite the existing report or save it as a new report).
The entire process will take you about 2 minutes. Remember.. There is more than one way to skin a cat.. Let's do it the easy way.

In the next article, I will talk about how to update the report specifications of multiple reports by altering the report specifications (without the use of SDK).

Wednesday, January 18, 2012

Show or Hide List Report Columns using Javascript

Business Case:
Report author wants to create a list report in, which, any of the columns can be shown/hidden after the report execution is complete.

Environment: IBM Cognos 8, Internet Explorer/Mozilla Firefox, Javascript, HTML format reports.

Example:
A report is used by many users. This report has many columns, and depending on which user is viewing the report, users might want to temporarily hide one or more columns and later unhide them.

Note:
  • This requirement is different from the case where users know before executing the report, which columns need to be displayed. If the users know beforehand, Render Variable can be used to hide the columns. But in this case, the columns cannot be shown again without re-running the report.
  • Since this solution is implemented using javascript, it goes without saying that it works only when the report is run in HTML format.
  • I have tried this with a list report, but the script should work for crosstabs as well. If you have trouble making this work in crosstabs, please post in the comments section.
Implementation:
  • Implementing this requirement needs some placeholder HTML items on the page in addition to the main script.
  • The list report in cognos is rendered as a table in the cognos viewer, consisting of rows and columns. Unfortunately, the entire page in Cognos viewer consists of rows and columns.
  • In order to identify the list report, we will be unlocking the cells and placing an HTML item in the report title section in one of the list columns (it doesn't matter which column). The HTML item will have the following script:
<span id="list"></span>
  • Now we need a bunch of links outside the list report that can be used to toggle (show/hide) the columns. 
  • You can create this just before the list report, but it won't work if your report spans multiple pages. I placed this in the page header, since the header spans across all the pages.
  • I created a table with one row and 5 columns one for each column in my list report. I then placed, one HTML item in each column. The HTML items will have the following scripts.
Script in 1st HTML item: <a href="javascript:hideColumn(1)">String</a>
Script in 2nd HTML item: <a href="javascript:hideColumn(2)">ID</a>
Script in 3rd HTML item: <a href="javascript:hideColumn(3)">Name</a>
Script in 4th HTML item: <a href="javascript:hideColumn(4)">Type</a>
Script in 5th HTML item: <a href="javascript:hideColumn(5)">Flag</a>
  • If you notice above you have match the column number and name to your list report. Now the report layout should look like this
  •  The last piece is the HTML item for the main script. You can place this just after the list, but to support reports that span multiple pages, I placed it in the page footer. The main script is as follows:
<script>

function hideColumn(colNum)
{
  var listHeader = document.getElementById("market");
  var table = listHeader.parentNode.parentNode.parentNode.parentNode;
  var rows = table.getElementsByTagName("tr");

  for(var i=0;i<rows.length;i++)
  {
    var cells = rows[i].getElementsByTagName("td");

    if(cells[colNum-1].style.display == 'none')
    {
      cells[colNum-1].style.display = '';
    }
    else
    {
      cells[colNum-1].style.display = 'none';
    }
  }
}

</script>
  • When the report is executed, it should look like this:
  •  You can see that, my report spans multiple pages. When I click the link "String" the first column will be hidden and the report will look like:
  • You can see that even though the column is hidden, the link "String" is still visible. You can click on this link again to show the "String" column.
  • I clicked on the "Type" link and now the report looks like this:
  • Even though the approach may seem long, it is pretty simple. 
  • The advantage of this script is that, since we are not manipulating any cognos objects like prompts, this script should work for most versions of Cognos.
  • The script has been tested in both Internet Explorer and Firefox.
This should score you some points with your users.. Have fun...



Friday, January 13, 2012

Clear Value Prompt Selections Using Javascript

Business Case:
User wants a clickable link on the prompt page to clear all prompt selections.

Environment: Cognos 8.4, Report Studio, Javascript, Mozilla Firefox / Internet Explorer


Implementation:
This can be achieved using Javascript.
  • Place an HTML item at the end of the prompt page after all the prompts.
  • Place the code from the next section into the HTML item.
  • You can use a table to position the link on the page.
  • When the report is executed, user will see a link "Clear All Selections", which will reset all the prompt selections.
Code:
<script>
var list_prompts = document.getElementsByTagName("select");

function ClearSelections(){
  for (var i=0;i<list_prompts.length;i++)
  {
    list_prompts[i].selectedIndex = -1;
  }
}
</script>

<a href="JavaScript:ClearSelections()">Clear All Selections</a>

Wednesday, January 11, 2012

Crosstab with Non-Numeric Fact

Business Case:
The user wants to display the data in a crosstab format but the values in the intersection are not numeric.

Example 1:
User wants a matrix of product reviews. The crosstab rows should have Orders (Order ID)and columns should have products (Product Name). The crosstab cells (intersection of product and order) should contain the text "Positive" or "Negative" depending on what review the customer gave for each product.

Solution:
Crosstab reports in Cognos are used to display summary values. For example, Order Quantity for each product/order combination would be easy to achieve. If you have a fact that is non numeric, the report will be blank.

However, there is a work around for the kind of requirement explained in Example 1 above. Let's look at the implementation below.

Report Input and Ouput:
Let's look at the data structure first. In the example above, we have 3 query items: Order ID, Product Name and Feedback. The data will look like this:


The expected report output is like this:
Implementation:
  • First, we need to create a numeric fact equivalent for the non-numeric fact. In this case, I created a data item Feedback_Numeric with this expression:
Case When [SQL1].[FEEDBACK] = 'Positive' Then 1 When [SQL1].[FEEDBACK] = 'Negative' Then 1 Else Null End
  • Set the "Aggregate Function" and "Rollup Aggregate Function" properties of this query item to "Total"
  • Now, create a crosstab report in the report page, drag [Order ID] to the rows, [Product Name] to the columns and [Feedback Numeric] to the measures.
  • Create a new String Variable "Feedback Variable" with the following expression:
Case [Query1].[Feedback Numeric]
  When  1 Then 'Positive'
  When  2 Then 'Negative'
  Else 'Null'
End
  • Your condition explorer should look like this:

  • Next, unlock the cells by clicking the lock icon on the toolbar. 
  • Select the text of the query item in the crosstab. Make sure, you are selecting only the text portion of the cell, not the entire cell (see image below).
  •  In the properties pane, edit the "Text Source Variable" property and set it to "Feedback Variable"
  • Now expand the "Conditional Explorer" and select "Positive"
  • Click the text part of "Feedback Numeric" measure in the crosstab and in the properties pane, change the "Source Type" property to "Text".
  • Edit the text item in the crosstab cell and type "Positive"
  • Do the same thing for "Negative" case in the Conditional Explorer.
  • For "Null" condition, edit the text to put "No Feedback" or you can put a white space as I did.
 Now, if you run the report, the output should be like the second image above.

Have fun..

Tuesday, January 10, 2012

Use the Same Report for Scheduling and Manual Run

Business Case:
A scheduled report with a date filter should be customized to be used with date prompts.

Environment: IBM Cognos 8.4.1, Report Studio

Example:
I had built a scheduled report for my users that had a date filter for the past 7 days.  This report was scheduled to run every Monday and email the results to the users. The users wanted to be able to run this report for custom dates as well.

Solution:
This is a pretty standard request that I had come across many times.  When the report has a date filter using 'current_date' as the time of reference, then the report can be scheduled without a date prompt. For a report that should filter for the past 7 days, the filter will look like this:
[Order Date] between _add_days(current_date,-7) and _add_days(current_date,-1)
When the same report has to take date as input, users generally tend to take the easiest approach of making a copy of the report and change the filter to use parameters that get values from date prompts. The new filter will look like this:
[Order Date] between cast(?start_date?,date) and cast(?start_date?,date)

There is nothing wrong with this approach, except that, now you have two reports that have the same business logic and layout, but have to be maintained separately. If there's a change in the logic or layout, it has to be done in two places. This becomes cumbersome when the report is complex and has many queries.

As a best practice, I always like to merge the two requirements into a single report by adding a value prompt to the report. Let's see how this is done. This solution can be adopted for any scenario that requires a separate filter for manual vs. scheduled run.

Implementation:
  • Create a value prompt with two static choices: "Last Week" and "Custom". Let's call this parameter date_range.
  • Create two date prompts, one for Start Date and one for End Date. Let's call these parameters start_date and end_date.
  • Use the following expression in your query filter:
(?date_range? = 'Last Week' and
[Order Date] between _add_days(current_date,-7) and _add_days(current_date,-1))

OR

(?date_range? = 'Custom' and
[Order Date] between cast(?start_date?,date) and cast(?start_date?,date))
  • The expression above will make sure that the corresponding date filter is applied depending on the selection of "Date Range".
  • For example, when the users select "Last Week" from the value prompt, the filter will ignore what is selected in the date prompts.
  • Now, if you want to schedule the report to run on every Monday for the past week, schedule the report and set the "Date Range" prompt value in the schedule to "Last Week"
  • You can give any date values in the date prompts, since they will be ignored anyway.
Conclusion:
You can use this technique to many other cases as well. For example you can create custom groups of filter values and define your own group names. By hard coding the group members in the filter, the users will be able to select a group and get the report that has the individual members.

Tuesday, January 3, 2012

Show or Hide any Element in the Report using Javascript

Business Case:
Users want to have a list or a chart hidden in the page, which can be optionally shown/hidden.

Environment: IBM Cognos 8.4.1, Internet Explorer/Mozilla Firefox, Javascript, HTML format reports.

Example:
I built a dashboard for a client who wanted summary level information displayed in the page with the detailed data below it, but wanted the users to be able to see detailed data only when needed.

Implementation:
This is a very simple task that requires only a few steps. You will need 4 HTML items for this:
  • The first HTML item is to create a link on the page that says "Click Here to Show/Hide Item". Place this HTML item wherever you want the link
  • Pick the element you want to show/hide and place 2 HTML items around it, one before and one after. In this example, the element I chose to hide is a value prompt.
  • Place a fourth HTML item at the end of the page after all the elements. This will contain the main script.
  • Your report layout should look like this:
  • Insert this code within the first HTML item:
    <a href="JavaScript:ShowHide()">Click here to Show/Hide Item</a>
  • The second HTML item will have the following code:
  • <span id = "value_prompt">
  • Place the following code in the third HTML item:
  • </span>
  • The <span> tag is used to create a handle for the value prompt and assign an ID to it called "value_prompt". The main script will use this handle to change the display status of the item.
  • The last HTML item will have the main script as below:
  • <script language="javascript">
    function ShowHide(){
    var el = document.getElementById("value_prompt");
    if ( el.style.display != 'none' ) {
    el.style.display = 'none';
    }
    else {
    el.style.display = '';
                         }
    }

    function startHidden(){
    var el = document.getElementById("details");
    el.style.display = 'none';
    }
    </script>
  • Note that in the code we are using the ID of the span to call the item.
  • That's it.. now when you run the report, the output will look like this:
  • Clicking the link will hide the value prompt. Clicking it again will show the prompt.
  • To hide the elements when loading the page, add the following line after the startHidden() function:
window.onload=startHiden();
You can use this technique for toggling any element in your report. You can even use it to toggle multiple elements or an entire table. Give it a try and post your comments below. As always, I will be happy to help with any issues you have.