Local File Access - Load Prompt Values from a File

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".