Friday, December 16, 2011

Local File Access - Load Prompt Values from a File

Load Prompt Values from a File on your Computer
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


15 comments:

  1. It's a great way around espacially when you have lots of OrderID.
    However, I am getting an error invalid file name.
    Could you please explain how to change the RN_SelectClass to what?

    ReplyDelete
  2. @CognosTech: If you are getting "Invalid File Name" error, then Javascript is having trouble accessing the file. Note that the script works only with Internet Explorer and you must enable ActiveX scripting for it to work.

    ReplyDelete
  3. I am confused as to what should I change RN_SelectClass to ??
    I have tried in Internet Explorer with ActiveX Scripting enable but it still doesn't work.
    Please help....

    ReplyDelete
  4. @CognosTech: What version of Cognos are you using. You have to change RN_SelectClass value only if you use Cognos 8.2 or older.

    There are multiple values for ActiveX scripts in IE, make sure to enable all of them, not just one.

    If you still have issues, email the report xml to cognosonsteroids@hotmail.com and I will take a look.

    ReplyDelete
  5. I am using cognos 8.4.1.
    I will email you the report xml...
    Thanks Sriram,,,

    ReplyDelete
  6. This would be so great if i could get it to work! I am having the same issue (illegal file name) and will watch here for a resolution.

    Thank you!

    ReplyDelete
  7. @MarthaC: Can you post the complete error message with the error code? If you can email a screenshot of the error that would be great.. You can send it to cognosonsteroids@hotmail.com

    ReplyDelete
  8. Emailed a set of screenshots. For the benefit of others reading this thread, there are two "Message from webpage" boxes that come up:

    ! Invalid file name [OK]
    and
    ! Path not found----2146828212 [OK]

    ReplyDelete
  9. Sriram asked me to do the following:

    In the javascript, after the code
    fileName = fn[0].value;

    try adding this line
    alert("File Name: " + fileName);

    This allowed him to see what file the javascript could not locate. It was looking for my file in a location that did not exist:
    C:\fakepath

    I added C:\fakepath to my directories and moved my local input files to that directory, and everything then worked fine!

    Sriram noted that if you are using a directory name that has spaces in it, you might run into this problem because IE does not play well with spaces in directory names. In my case, my file was originally on the desktop, and the PC name has spaces in it. Perhaps this was the problem.

    This will be very helpful for reports having fewer than about 1000 filter values (not Sriram's limitation but the usual SQL limitation).

    In any event, many thanks to Sriram!

    ReplyDelete
  10. Hi Folks

    I am trying to get this to work as well. I just wanted to make sure, is that a Value prompt? If so, what do the properties of the Value prompt look like? We don't want the values to load in there, so do we set up the 'Query' and 'Use Value' settings? If someone ccan confirm, that would be great.

    ReplyDelete
    Replies
    1. Hi Sriram,
      What is the text file format?
      ex: 17,20,21,22
      saved as orders.txt.
      But I am getting an error.I enabled ActiveEx controls.
      Please provide the liitle more explanation or send the xml if possible.
      I am using 10.1.1

      Thanks,
      Srinivas
      srini1626@gmail.com

      Delete
    2. Srinivas,

      As I have mentioned in the post, the IDs should be placed one per line without any commas. If you still get an error, please post the error message.

      Sriram.

      Delete
  11. Sorry just saw the information on the Use\Display values. Please ignore above comment

    ReplyDelete
  12. Thanks Sriram.

    This is excellent.

    This helped in achieving our task.

    Thanks,
    Sunil

    ReplyDelete
  13. That's excellent solution and very usefully!
    Great work!
    Thanks a lot!

    ReplyDelete