Customize Value Prompt Names using Javascript

Business Case:
Value prompt is widely used in Report Studio Reports. Users want to display a message in the prompt or display a name different from the parameter name or remove the parameter name.

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

Here's how my value prompt looks like by default:

Example 1:
In the country prompt, the parameter name is "p_country" or "country", but user wants to display "Please select a Country". User also wants to remove the dummy option with "--------".

Code:
<script>
//Get a list of elements on the page with "select" tag
var value_prompt = document.getElementsByTagName("select");

//Loop through the elements
for(var i=0;i<value_prompt.length;i++)
{
  //Check if element is a value prompt
  if(value_prompt[i].className = "clsSelectControl pv")
  {
    value_prompt[i].options[0].text = "Select a Page Type";
  }
}
</script>

Now the prompt looks like this:

Example 2:
User wants to remove both the name and the "-------" options from the value prompt (the first two entries). The prompt should contain only the values.


Code:
<script>
//Get a list of elements on the page with "select" tag
var value_prompt = document.getElementsByTagName("select");

//Loop through the elements
for(var i=0;i<value_prompt.length;i++)
{
  //Check if element is a value prompt
  if(value_prompt[i].className = "clsSelectControl pv")
  {
    // Remove the first two options
    value_prompt[i].remove(0);
    value_prompt[i].remove(0);
  }
}
</script> 
 
This script removes the first two options and makes the prompt look like:

Implementation:

• On the prompt page, create an HTML item at the end of the page after all the prompts.
• Place the code above in the HTML item.
• You can change the value of [i] to customize the prompt of your choice. For example, if there are multiple value prompts on the page, the code for example 2 above will remove the name and '----' for all the prompts.

---Give the world the best you have and you'll get kicked in the teeth. 

Give the world the best you have anyway.---

Calendar Style Report in Report Studio

Business Case: Create a Cognos Report Studio report that displays data in a monthly calendar format.

             I came across this requirement from a fellow developer in IT Toolbox forums. This is a very interesting scenario, which I haven't faced before.  The user wants to display daily stats in a calendar format. Each day's information should be in the corresponding calendar box.

Environment: IBM Cognos 8.4.1 Report Studio, Oracle 11g.

Example 1:
The monthly sales order report should display number of sales orders per day in the form of a monthly calendar with each day containing the corresponding count.

When finished, the report would look like this:

Example 2:
This one is a similar report but with detailed information like list of customer visits or meetings etc. The output will look like this:

Implementation Logic:

• The solution I have come up with needs one row of data per day, so if you have multiple rows per day, they need to be aggregated (if it is a fact) or concatenated (if the data point is a string). 
• In example above, I used oracle function "wm_concat" to concatenate multiple customer names into one name per date.  
• If the data you want to display is a measure, then use the appropriate aggregate functions to display the correct value.  
• Depending on your data structure, you may have to create a separate query that gives you the date and the measure. You can use this query as a reference in the main query.
• In my case, I used a SQL Object to do the groupings.

Query:

• Since this is a report by day, you need to have only two items in the query, Date and whatever metric you want to display in the calendar. In the examples above, I had date and retailer count (or retailer list).
• The second step is to create a data item called "Shift". This data item will specify how many days the dates have to be shifted from the first day of the week (Sunday). This is done to place the dates in the correct weekdays (instead of 1st always falling on the first cell). This depends on which day of the week the first of month falls under. For example, if you take December 2011, Dec 1st falls on a Thursday, which is a shift of 4 days from Sunday. The expression for [Shift] is:

(_day_of_week (_first_of_month(current_date),7)-1)

• Next, you have to create 2 sets of 35 query items, two for each cell of the calendar (total of 70 query items).  I know.. This seems like a lot of query items, but is necessary for the calendar layout to work.
• One of these items is the date and this will be placed in top left corner of each cell starting from top left. In the screenshot above, this is for the numbers 1 through 31 displayed in the corner of each cell. I am calling these Cell 1 Date, Cell 2 Date .... Cell 35 Date. The expression for the "Cell 1 Date" is:

                                          Case When extract(day,[Created Date]) = 1-[Shift]

                                          Then extract(day,[Created Date])

                                          Else null

                                          End

• Change the number in the Case statement to match the cell number. For example, expression for "Cell 35 Date" will have "35-[Shift]" in the Case statement.
• The second item will be for the actual data in each cell. Example, the order count in the first example and list of customers in the second example. I am naming these as Cell 1 Data, Cell 2 Data ... Cell 35 Data. The expression for the "Cell 1 Data" is:

                                          Case When extract(day,[Created Date]) = 1-[Shift]

                                          Then [Customers]

                                          Else null

                                          End

• The data item [Customers] should either have an aggregated measure like customer count or a concatenated string like list of customers separated by commas.  This is very critical for this report to work. There can be only one row per date.
• Once all the data items are created, set the "Aggregate Function" and "Rollup Aggregate Function" properties to "Maximum". This is done to consolidate the data set into one row. Otherwise, you will see the entire calendar repeated multiple times.

Layout:

• Create a List report on the page. Hide the list titles.
• Insert a table with 6 Rows and 7 Columns. I went one step further and put another 3x3 table inside each table cell to get the small box in the top left corner (except for the first row which has the weekdays displayed as text.
• Set the cell heights and width to your needs (I used 100px x 100px in the example).
• You can download the XML for the table I created from here: Link to Table XML.
• Copy the contents of the file to clipboard and paste inside the list report. The table will appear inside the list.  Note that this is just the empty table. You will have to drag the data items individually into each cell.
• Once the table is in place, place items "Cell 1 Date" thru "Cell 35 date" into the top left corner cell of each day.
• Next, place items "Cell 1 Data" thru "Cell 35 data" into the middle cell of each day.
• When you are done, your report design page should like this:

Voilà.. You are done.. When you run the report, you should see the report like the second screenshot above.

The report may sound complex, but once I had the expressions figured out, the entire report took me about 2 hours to create. Go ahead and try it out and post your comments here...

Auto Refresh Report Studio Reports

Business Case:
Our executive team wanted a performance dashboard that will displayed on a big screen TV in the main conference room. They wanted to make sure that the report displays near real-time data without someone having to manually reload the report.

Environment: IBM Cognos 8.4.1 Report Studio.

Solution:
Our ETL process updates the data warehouse tables every 30 minutes.  I used Javascript to make the reports reload the data every 5 minutes. So the report can be left unattended, and since the report had "Report as of: " right on top, the executives will know when the report was last refreshed.

Code:
Note: The code given below has been tested for IBM Cognos 8.4.1 Report Studio. This code may not work with older versions of Cognos as is. Script was adopted from IBM Knowledge base document  

<script type="text/javascript">
// Licensed Material - Property of IBM
// © Copyright IBM Corp. 2003, 2011
var intval;
var fW = (typeof getFormWarpRequest == "function" ? getFormWarpRequest() : document.forms["formWarpRequest"]);
if ( !fW || fW == undefined)
{

    fW = ( formWarpRequest_THIS_ ? formWarpRequest_THIS_ : formWarpRequest_NS_ );

}
var preFix = "";
if (fW.elements["cv.id"])
{

    preFix = fW.elements["cv.id"].value;

}
var nameSpace = "oCV" + preFix;
if(intval!="")
{

    self.clearInterval(intval);
    intval="";

}

self["RunReportInterval"] = self.setInterval( nameSpace + ".getRV().RunReport()",'600000' );
intval = self["RunReportInterval"];
</script>  

Implementation:

• Create an HTML item at the end of the page
• Place the above script inside the HTML item.
• Report interval (300000 in the code above) is in milliseconds, so 300000 represents 300 seconds or 5 minutes.
• In the report header or footer, place a data item with report expression "AsOfDate()" 

Local File Access - Load Prompt Values from a File

Business Case:
User wants to enter multiple values for a value prompt in a report. The set of values change once every few weeks.

Environment: IBM Cognos 8.4.1 Report Studio, Javascript, Internet Explorer.

Example:
I had two different users who had this kind of a request.

The first user had a report that took sales order numbers as input through a value prompt. Every month, the user tracked a list of 10-15 orders on a daily basis in the report which showed order status shipping information etc.  The next month, the list of orders would be different. The user found it difficult to select the same set of 10-15 order numbers in the value prompt every time the report was run.

The second user received a list of customer ids from corporate office for which the customer behavior report has to be run. The user will get a new set of customer ids each month. The number of values in the list would range from 100 to 500. The customer table has hundreds of thousands of customers and user cannot pick the values from the value prompt.

Solution:

• I had the users create a text file in their desktop in a folder of their choice. 
• The text file will contain the list of order numbers or customer ids, placed one per line. 
• When the report is run, the user will see a browse button (HTML file input element), similar to attachment buttons found in email clients. 
• The user will browse for the file containing the prompt values and select it.
• The user will then click another button on the prompt page named, say, "Load Values"
• The list of prompt values from the file will be loaded into the value prompt.
• User will then "Select All" values and run the report.
• When the list changes, the user has to update the values in the file or overwrite it with the updated file.

Screenshots:
Here's what the prompt page looks like when the report is run:

Browse for the file...

 Select the file and click "Load Values" button...

Code:
Note:

• The code works only in Internet Explorer. I am working on a firefox equivalent.
• Also, this code is for Cognos 8.4.1. If you use an earlier version of Cognos, the code has to be modified.
• You should enable ActiveX scripts in Internet Explorer. 
• SQL Queries have a limit of 1000 lines. So, if you have a large data set, split them into chunks of, say, 900 to avoid SQL errors.

 
<input type=file name="inputfile">
<input type='button' value='Load Values' onclick='uploaddata()'>

<script language="javascript">

function uploaddata(){
    var fn=document.getElementsByName("inputfile");
    var x = document.getElementsByTagName('select');

    var RN_SelectName = "_oLstChoicesT1";
    var RN_SelectClass = "clsSelectControl pv";

    var is_Required;
    var fileName;

    fileName = fn[0].value;

    for (var i=0;i<x.length;i++)
    {
        if (x[i].className == RN_SelectClass)
    {
        var objFSO, objTextFile;
        var sRead, sReadLine, sReadAll,exception;
        var ForReading = 1, ForWriting = 2, ForAppending = 8;

        while(x[i].options.length!=0)
        { x[i].options.remove(0); }

        try{
        objFSO = new ActiveXObject("Scripting.FileSystemObject");
        objTextFile = objFSO.OpenTextFile(fileName, ForReading);
            while(!objTextFile.AtEndOfStream){
                sReadLine = objTextFile.ReadLine();
                      x[i].options[x[i].options.length]=new Option(sReadLine,sReadLine);
            }
        objTextFile.close();
        } catch(exception) { alert("Invalid file name");
          alert(exception.description + "---"+ exception.number);}

    }
    }
}
</script>

Implementation:

• Create a value prompt in the prompt page. Do not specify use or display values, so that the prompt is empty when the page loads.
• Create an HTML item after the prompt and place the code in it.
• That's it. When you run the report, you will see the file browse element.
• Browse for the file you created in your computer and click "Load Values".

Give me six hours to chop down a tree and I will spend the first four sharpening the axe. -- Abraham Lincoln

Introduction

Welcome to Cognos on Steroids.


This site is about how IBM's Business Intelligence Reporting tool, Cognos, can be used in unconventional ways to achieve business requirements or improve user experience.  Many of the these were accomplished using Javascript. However, I will also discuss features of Cognos, that are not commonly known, things that are usually buried deep in the documentation.


I will be publishing business cases, code samples and methodologies. I welcome you to post your comments and also challenging business requirements you faced/currently face in your environment.


Following topics were discussed recently:

Create a Crosstab Report with a Non Numeric Fact

Using a single report for both scheduling and manual execution
Limit Value Prompt Selections using Javascript
Show or Hide any Element in the Report using Javascript
Customize Value Prompt Names using Javascript
Create a Calendar Style Report in Report Studio
Make Report Studio Reports Refresh Automatically at Set Time Intervals
How to Load Prompt Values from a File