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.

Sunday, January 1, 2012

Validate Value Prompt using Javascript

Validate Value Prompt using Javascript Business Case:
Value prompts are widely used in Report Studio Reports. Users want to validate the prompt values by limiting the number of values selected at  a time.

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

Example:
Users should be able to select only up to a maximum of 3 values. When the fourth value is selected, an alert message should be displayed. 

Solution:
This requirement can be achieved by using javascript. When the user selects an option, the script will count the number of options selected and display an alert text when the number of selected values exceeds the user defined limit.

The prompt will look like this when user selects 3 values:
When the user selects a fourth option, the "Finish" button will disappear and an alert message will be displayed, like this:
When one of the options is unselected, the "Finish" button will reappear and the alert message will be hidden.

Implementation:
  • Create the prompt page and place an alert text as above.
  • Place three HTML items: first before the alert text, second after the alert text and the third at the end of the page after all elements, as shown below:

  •  Place the following code in the first HTML item:
<span id ="alert_msg">
  •  Place the following code in the second HTML item:
</span>
  •  Place the following code in the third HTML item:
<script>
//Create a handle for the alert message
var alert_msg = document.getElementById("alert_msg");

//Create a handle for Finish button by getting a list of elements on the page with "button" tag
var buttons = document.getElementsByTagName("button");

//Get a list of elements on the page with "select" tag
var value_prompt = document.getElementsByTagName("select");
value_prompt[0].onchange = validateSelection;

//Hide alert message when page loads
alert_msg.style.display='none';

function validateSelection()
{
   var selectionCount = 0;
   for (i = 0; i < value_prompt[0].options.length; i++)
   {
      if (value_prompt[0].options[i].selected)
      {
        selectionCount = selectionCount + 1;
      }
   }
  
   if(selectionCount > 3)
   {
     buttons[1].style.display='none';
     alert_msg.style.display='';
   }
   else
   {
     buttons[1].style.display='';
     alert_msg.style.display='none';
   }
}
</script>
That should do the trick.  Now when the user runs the report, they should see an alert when selecting more than 3 options.

Test it out and post your comments. You can also contact me through email at cognosonsteroids@hotmail.com