56 thoughts on “About”

1. You forgot an insurance company and an immigration agency 🙂

   By the way, how many sites do you plan to have? Is it like a third or have I missed any?

   1. I was only listing the companies that were a pleasure to work with. 😉

      Just kidding! I could also include a vehicle security/towing company, a cellular provider, a call center, a major software developer and an army.

      No plans for a third site. Updating has been sporadic due to a few certain people giving me impossible problems to solve. Not to mention getting to migrate a few thousand reports from 8.2 to 8.4.1.

2. Hi Paul,
   I’ve been fighting with the cognos tools for a couple of years, and blogs like yours are invaluable (coming from a microsoft background, I’m finding the lack of online support very frustrating).

   I’ve recently bought together several posts to create far more interactive ‘master-detail’ type thing based on a cognos list and jQuery. I’d like to share it with the world in general but I’m not the type of person that’d keep a blog up to date. If I tidy it up and explain it in a blog style would you like to host it?

   Merry Christmas
   Paul

   1. Hi Paul!

      I’m thrilled to hear that this blog has been useful. Compared to the Microsoft Suite Cognos has relatively few outlets for unofficial support. My favorite forum is Cognoise.

      Thanks for the offer for the blog post. Normally I’d say no, but with a name like yours I don’t see how I could refuse. Maybe we could get several expert Pauls posting. (Gary, you don’t mind changing your name, right?)

      I’ve also been (mostly unsuccessfully) trying to maintain a Cognos wiki here. I lack the organization and focus to update it on a regular basis though.

3. Hi Paul!
   I also, Do hope that this is your finel Blog…. Kind’a getting exhausting follow your foot pring cross the web….

   arik (Pointer)

   p.s.
   How the “F” can i set deferent color to a “list row’ (full row) by one of its data item value ???
   * data item that hold the value (1=red / 0 =green) is in the Query BUT it is NOT show on the List.

   p.s. 2
   How’s the wife and the boy ? still got tho’s Sleepless Nights ? …..

   (-:

4. Hey Arik! It’s been a long time.

   The boy is doing well, a year old and almost walking. He’s mostly sleeping through the night at long, long last. Wife is back to work, and doing great.

   So you’re list is populated by a query that needs to be conditionally highlighted by an object in the query, but not the list itself. Correct? Click on the list object, and in the properties of the list there is a row called Properties. Click on that and check the data item. The Properties row allows you to force Cognos to include a specific data item in the SQL. By default if the data item isn’t included in the list, Cognos won’t bother including it in the SQL.

   Hope things are going well!
   -Paul

5. Hi St.Paul, great blog!
   Already enlightened me.

   David F.

6. Paul,

   I’ve been searching for someone willing to share some cognos insight with me on the basic “how to’s” (i do realize that it is NOT basic!) of the back end. I’m very familiar with cube development and the front end processes that are possible utilizing data within cognos; however, I want to know what limitations there are for the system and just what is/is not possible. I am interviewing with a company for a role that works along side the IT individual that is solely responsible for the cognos system development, and would need to communicate the executive team’s information needs to this person to get it loaded so i can create/formulate cubes as needed. any suggestions/insight into this situation so I can better understand and play out how I would fit into this picture?

   Thank you for your time – it is much appreciated.
   Rebecca

   1. Hi Rebecca, drop me an email to cognospaul at the mail of G. I’m not entirely sure how to answer this here.

7. Hello Paul,

   your blog is too useful :-)))

   I’use Cognos 8.4 and I’ve a little problem:
   I want select by java a list box.
   I renamed the list box as XYZ
   After it in the page I’ve created a HTML and insert this code:

   var form = getFormWarpRequest();
   var list = form._oLstChoicesXYZ;
   list.options[0].selected = true;

   But it don’t work, I obtait this error…

   Dettagli errore pagina Web

   Agente utente: Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 5.1; Trident/4.0; .NET CLR 2.0.50727; .NET CLR 3.0.4506.2152; .NET CLR 3.5.30729; .NET CLR 1.1.4322)
   Timestamp: Fri, 18 Nov 2011 11:26:40 UTC

   Messaggio: ‘list.options.0’ è nullo o non è un oggetto
   Linea: 127
   Carattere: 1
   Codice: 0

   I do not understand what it depends…

   1. Hi Maurizio,

      The error looks like it might be caused by differences in the JavaScript engine between the Cognos Viewer and Report Viewer.
      Try:

      <script language="javascript">
      var fW = (typeof getFormWarpRequest == "function" ? getFormWarpRequest() : document.forms["formWarpRequest"]);
      if ( !fW || fW == undefined)
      { fW = ( formWarpRequest_THIS_ ? formWarpRequest_THIS_ : formWarpRequest_NS_ );}

      var list = fW._oLstChoicesXYZ;
      list.options[0].selected = true;

      canSubmitPrompt();
      </script>

      1. Ops… excuse me, but I’ve seen youre message only now…
         I’ve tried but I obtain the same error…

         Mystery

         1. This code works fine…

            function init()
            {
            var fW = (typeof getFormWarpRequest == “function” ? getFormWarpRequest() : document.forms[“formWarpRequest”]);
            if (fW)
            fW._oLstChoicesXYZ[2].checked = true;
            canSubmitPrompt();
            }
            init();

            But I don’t find an option for check all!!!

            1. Are you trying to have all of the options checked on load? Do a loop along the lines of
               function init()
               {
               var fW = (typeof getFormWarpRequest == “function” ? getFormWarpRequest() : document.forms[“formWarpRequest”]);
               if (fW)
               for (i=0;i<fW._oLstChoicesXYZ.length;i++)
               {
               fW._oLstChoicesXYZ[i].checked = true;
               }
               canSubmitPrompt();
               }

            2. Thank you so much!!!! Perfect!!!

8. Hi Paul,

   Nice to see you are solving many BI developers issues. I hope you solve mine also.

   I have a html code which is working fine in Dev and test but in production it is not able to access the file, the only difference I have seen is that in production we have security with LDAP and there is none in dev and test. Can you please tweek it to work in production also. I am trying to use csv file for the value prompt where in user enters values in csv.

   Here is the code

   var form = getFormWarpRequest();
   var list1 = form._oLstChoicesABC;
   upload(list1, “\\myserver\TestData1.csv”);

   function upload(listPrompt, fileName) {
   var objFSO, objTextFile;
   var sReadLine, exception;
   var ForReading = 1, ForWriting = 2, ForAppending = 8;

   // clear the list
   while (listPrompt.options.length != 0) {
   listPrompt.options.remove(0);
   }

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

   Hope to here from you

   1. Hi Reddy,,

      Helping people here is difficult, as it’s just me and I’ll won’t admit to knowing everything.

      Let’s move this to the Cognoise forum where other people would be able to chime in. I’m not as active as I was previously, but I check the forums every day.

      In the meantime a few questions, are you using the report inside a portal or directly from a drillthrough or portal link? Is the prod domain in the trusted sites? Is ActiveX allowed for that domain?

9. Generally in the cognos once run the drill through report we will get hyperlinks.. if you click on that hyperlinks
   we will get “Go To” page . in that page we will get Available links: like a Name,Target and View passed source values .
   but i need to hide the Target and View Passed source vales paths.
   How we can hide this any idea please give suggestion .
   advanced thanks

   bhaskar

   1. Go to launch->administration->security->capabilities->Drill Through Assistant

      restrict that to your developers.

      1. Hi Paul,

         Thank you for fast response.
         I was following same steps what you given . but still i am viewing Target and View Passed Source values paths when i try to open from drill through link . we have LDAP in our environment and we are using dev server.
         please advice me any thing i missed.

         1. The JavaScript file that controls the rightclick menu is viewer.standalone.core.js. It looks like there is some sort of blacklist that controls what appears. I haven’t had time to go into details so I’d suggest opening a ticket with IBM or your support vendor to get details on how to block it

10. Hi Paul…

    what’s up dud….
    need your advice (if i may….)…..

    on COGNOS 10, report have 2 input parameters :
    pFromDate
    pTodate

    report is Scheduled to run every 7th day of the month and show result of previous month.

    question is….

    how do i set default values of input parameters ,
    pFromDate = first day of previous month
    pTodate = last day of previous month

    Please Help…… TNX….
    Arik…

    1. Hey Arik! All is good.

       So the report is being used as both an interactive, where the user selects the dates, and as a scheduled report?

       So lets assume that the following is your filter:

       [Date]>=?pFromDate? and [Date]<=?pToDate?

       Change that to use prompt macros, with date calculations in the default parameter:

       [Date]>=#prompt('pFromDate','date','_first_of_month(_add_months(current_date,-1))')# and [Date]<=#prompt('pToDate','date','_first_of_month(_add_months(current_date,-1))')# 

       When the schedule runs, it will trigger the default value in the prompt macro, since pFromDate and pToDate will be empty.

11. Hey….
    thanx for the help… brilliant as always…..

    Just wonder, is there a way i can set the Date Prompts to show default dates (pFromDate = first day of previous month, pTodate = last day of previous month) so that the dates will be shown inside the report as ParamDisplayValue ???
    when i run the report with the filters you suggest, ParamDisplayValue of dates are empty.

    TNX in advance..

    1. If you set the default value inside the macro itself, the parameter isn’t used so ParamDisplayValue will be null. What you could do is to change the report expression to be something like

       case when paramValue('pFromDate') is null then _first_of_month(_add_months (date2timestamp(Today ()),-1)) else paramDisplayValue('pFromDate')
       

12. Hi Paul,

    We Experience a problem only in our cognos 10 environment – randomly value prompt shirks and we can’t select values.

    Setting the “size and overflow” property of the value prompt does not help. First it runs OK – with the high defined, and randomly, as I mentioned, the value prompt shrinks.

    The problem occurs sporadically and only in IE (8,9). Running the same report in FireFox or locally on cognos server the problem never occurs.
    Same report in cognos 8.4. works fine.

    Thanks,

    1. I’ve never seen that behavior before. Are you using any JavaScript to interact with the prompt? If you inspect the prompt with IE Dev Toolbar, do you notice anything out of the usual? What prompt type are you using?

13. Hi Paul,

    Thank you for providing this useful cognos site.

    I edited the system.xml file to limit users to a specific root folder that exists in the Public Folder. However, this setting in not being inherited by Business Insight. Once a use launches Bus. Insight it allows them to see all the folders under Public Folder (they can see them from the Bus Insight Explorer).

    Is there a system file I can edit so I can direct users to a specifit root forlder for Bus. Insight? (like is done with Cognos Mobile and BI Server)

    Thanks in advance

    Richard

    1. I haven’t had a chance to dig around Business Insight, but that will be one of the things I look for as soon as I do.

       Personally I think this is just an oversight on IBM’s part, you should open an ER with them to get this fixed.

14. Hi Paul,

    I’m pretty new to Cognos. I work in public sector. Now we have many jobs cubes to run at the weekend. My boss would like me to research the possibility to use Ctrl M to automate the processes. There’s not much post of this online. Do you have any suggestion where I can start? Our server is on Windows.

    Thank you,
    Jane

15. Paul,
    I so need your help. I have a Cognos cube setup and have built a
    dashboard off of it. On one of my dashboard graphs (which is based on the OLAP cube) I want to be able to drill to a regular Cognos report (i.e. not dimensional). I am able
    to pass all of the parameter values; however, I am having trouble
    passing the date one. I want the date to pass its member caption to
    the target drill report; however, no matter what I try I cannot get
    this to happen. I have tried changing this on the parameter listing and I
    have also tried the caption and rolevalue formulas, but it still passes the
    date as 2012-03-05 instead of Mar/2012. Any help would be greatly
    appreciated.

    1. View the SQL/MDX. It may be possible that Cognos is processing the data locally, thus removing some dimensional functionality. If you see Cognos SQL, you know this is happening.

       Can you not use the 2012-03-05 output? If you’re using Oracle you could do something like to_char(to_date(#prompt(‘date’,’date’)#,’yyyy-mm-dd’),’mon/yyyy’)

16. Hi Paul –
    I’m trying to create a default date for a prompt in 8.4. The start default should be [Date of Activity] > _add_days (current_date, – 540) but I am unable to figure it out.
    Can you help?
    Thanks,
    Donna

    1. Hi Donna,

       There are a few ways to do this. The easiest way would probably be:

       [Date of Activity] > #prompt('Start Date','date','_add_days(current_date,-540)')#
       

       You’ll need to have a javascript that sets the date in the date prompt object on the page when the Start Date param is null.

17. Hi, Paul
    What are you think about compare BI Cognos vs. Microsoft Excel? On my work exists hard discussion between two sides. I’m from side of Cognos vs. supporters Excel.
    From side my opponents : the excel+(vba macroses & oracle.ocx) more intuitive & flexible in all themes related to GUI, and persons have more skill in excel.
    אני לבד בשדה הקרב

    1. Excel is a nice tool for presenting data, but aside from that there’s no comparison. Having worked in a few places that used Excel exclusively as their “BI” tool, I can list some of the problems they’ve run into.

       1. Differing versions. Even when an Excel workbook is saved on a network share, differences will start creeping in between analyst reports. Two analysts working on the same data, on the same workbook, might return with drastically different reports due to changed one made in their own version.

       2. InfoSec. Cognos handles security on multiple levels. Employees might have a report that shows their own payroll information. Their managers would have the same report, but see the information for all of their department. Contractors and consultants could have access to the same report, but not see half of the fields. The report could be scheduled to be mailed to everyone in the company, but the security would still be obeyed for each and every employee. To the best of my knowledge, the most you could do with Excel is have security applied on the database level, with username and password. Even then that wouldn’t always meet requirements. The same report I mentioned before had a requirement where the sales team would see only their own commission breakdowns, but see the commission totals for the entire team.

       3. Report History. Need to have the report output saved forever and ever? Set that in the scheduler in Cognos with a single click. Cognos will handle all the work, including security, and make it very easy to retrieve.

       4. Events and scheduling. This should be a no brainer. Yes, you can write a dozens of VBA scripts to check events, and you can use various scheduling technologies to run them. Ultimately it would be faster and easier to rely on Cognos. Need to send out an SMS when your customer service has 30% abandon rate? Have event studio trigger a webservice.

       5. Auditing. Who is accessing what data? What reports are run most often? How are the reports being used? Is there a correlation between a particular sales teams performance and the types of reports that are being run for that team?

       6. Data exploration. Excel can connect to a cube, but does it have any modelling tools (relational or dimensional) against a relational database?

       Ultimately Cognos is a BI platform. It gives the developers an easy (but powerful) way of controlling how the database is consumed by the end user. Excel is a tool that end users use to consume data. Cognos feeds Excel, but Excel can never replace Cognos.

18. Thanks, Paul.It’s the good answer to my opponents. In this moment them see only present data problems (show kinematic data on the graph with not constant X axis(time) data. May be use of statistic module of Cognos 11 can to solve this problem

19. How can I import function ‘reverse’ to my framework?

    1. Hi Tzipora!

       For everyone else, Tzipora and I occasionally work together at a major insurance company here in Israel. The reverse function in question is a UDF that I added to their DB2 to correct a bug that returns Hebrew returning (left to right).

       In order to import reverse (and any other UDF), run the metadata wizard, select the database and schema, and open the functions list. You should see that and any other UDFs that exist in the db.

20. what is the input and output of this function?

21. Hi, Paul

    I edited the system.xml file to limit users to a specific root folder that exists in the Public Folder. However, this setting in not being inherited by Business Insight. Once a use launches Bus. Insight it allows them to see all the folders under Public Folder (they can see them from the Bus Insight Explorer).

    Is there a system file I can edit so I can direct users to a specifit root forlder for Bus. Insight? (like is done with Cognos Mobile and BI Server)

    Thanks in advance

    1. Hi Sweer, I think this is a bug in Business Insight. It also doesn’t inherit object hide settings. These problems have actually led to a few projects abandoning Business Insight altogether. Short of hacking it, I think the only thing you can do is raise an issue with IBM.

22. Hi, Paul. What you can say about UDF/Store Procedure (Oracle) returned record ?. I can’t imagine how this function/SP look.

    1. Can you rephrase that? I’m not sure I understand…

23. If you use any stored procedure in Framework? What is interface of this SP? Example of this SP?

    1. I’ve used a few stored procedures in FM. Most commonly it’s for inserting records in a db. Something like:

       procedure ins_comment(i_comment in varchar(255),
       			i_user_id in integer,
       			o_cur1 out sys_refcursor) is
         cnumber number;
       begin
         insert into comments
           (comment, user_id, last_updated_dt)
         values
           (i_comment, i_user_id, sysdate);
         commit;
        
         open o_cur1 for
           select 1 from dual;
       exception
         when others then
           raise_application_error(-20001,
                                   'an error was encountered - ' || sqlcode ||
                                   ' -error- ' || sqlerrm);
       end;

       To use it I’d import the SP into the Framework, and set the comment as a prompt, and the user id as a session parameter (something like #$account.personalInfo.userID#). The report would have the SP inside a singleton, with the value in the properties. The singleton would only render when the comment parameter isn’t null.

24. Hi Everyone,
    I am new to this blog and I wonder if I can post a quick question related to Framework modelling.
    I am using Cognos 10.0 in a Finance envirnoment and I was trying to build and account hierarchy to reflect accounting KPI such as Gross margin and EBT(earning before tax), etc..
    I am trying to stay away from calculatin these values at the back end and allow Cognos to do the rollup in FM but it is not working because the default rollup is aggregation sum and I am not able to force the aggregation to do subtraction something like revenue – cost.
    I wonder if anyone ran into a similar scenario and what was the solution.

    To illustrate from the account hierarchy:
    accountParent accountchild
    Gross margin Revenue
    Cost
    EBT Gross Margin
    Total Overhead

    All the values are positive in the backend and I wanted to force the rollup calculation to do:
    GM=Revenue-Cost
    EBT=Gross Margin-Total Overhead
    and so on

    Thanks

25. Is there a way to hard code (or static) data item in a query. I have some specific data that is not found on database, trying to put that in data item so i can use that.

26. Paul – I’m trying to use ‘periodsToDate’ with a dynamic YTD time dimension but it gives me an error about not working with different hierarchies. Is there a way to use ‘periodsToDate’ with YTD time dimension? Here is what I was using: periodsToDate ( [Datacube].[All_Dates].[All_Dates].[Year] , lastChild ([Datacube].[All_Dates].[YTD].[Month]) ) now I’m receiving this error: Invalid coercion from ‘level’ to ‘member’. Or if I use YTD member level I receive an error about periodsToDate requiring operands from the same hyerarchy. I don’t want to use a prompt, my YTD doesn’t have a Year level, and my time dimension has months past the current period due to full year budget information.

    I’m using Cognos 10.1.1 and OLAP and your help is greatly appreciated.

    1. Wow, I am running a bit behind with answers. I’m not sure if it’s still relevant, but here it is. periodsToDate will only work within the same hierarchy – so your solution as it stands is out. However, you can use the linkMember function to find the same member in your primary time dimension.: periodsToDate ( [Datacube].[All_Dates].[All_Dates].[Year] , linkMember(lastChild ([Datacube].[All_Dates].[YTD].[Month]) ,[Datacube].[All_Dates].[All_Dates]))

       1. Hi Paul,

          Mine is same requirement. But I have a text box prompt, with default value set ‘MM/YYYY'(Static choice). If the user runs the report by default , It should show all Months from starting 01/2012 to till date. If he enters 06/2012 instead of default it should show from 01/2012 to 06/2012. It was easy in relational reporting but i’m new to olap reporting. Please guide me on this issue.

          Thanks,
          Vijay.P

27. Hi paul,

    I have a value prompt with static values “lastmonth”,’customdate”.when we select customdate and clicked on finish button the prompt page is running instead of that it should go to next prompt page.

    I had two prompt pages. prompt page1 is having value prompt with static values and prompt page2 is having “date prompts”.

    could you please help on this…

    Regards,
    ksr

28. Hi CognosPaul,

    I need a little help. I have a report with a Date Range Prompt. The user selects lets say from 01-Jun-2012 till 31-Aug-2012. My report consists of three sub queries joined together to become the query for my crosstab. The first and third query should run for the selected time period, i.e. between 2012-06-01 and 2012-08-31. However the second query needs to run for 01-May-2012 till 31-August-2021 (should start from 1 month prior to user selected start date). The join between query 1 and query 2 is like the analogy join current month with current_month-1.

    I need a solution to use the selected prompt as selected by user for query 1 and 3, where are use a selected start -1 Month for query 2.

    1. This is one of the reasons I dislike range prompts – it’s impossible to access the to and from parts from a prompt macro. Range prompts can only be accomplished through prompt aliases.

       Instead, I’d recommend using two separate date prompts, in Query2, use the prompt:

       [Date]>= #_add_months(prompt('Start Date','date') + 'T00:00:00.000Z',-1)#

29. Hi CognosPaul,

    I think I need you help. I am using cognos 8.4 to design report. In my prompt page, there is one tree prompt that showed data from my package ( it was created from Framework Manager tool ). I want to pass value that I selected from Tree prompt to my store procedure to generate data in detail page (MS SQL 2005). How can I do it?

    Thanks

30. Hi Paul

    I great fully used you checkbox java script prompting and try to used it for multiply lists. Unfortunately it seems not be holding the second list box selection upon hitting the finished button. Here is the code I used

    function selectInCheckbox(id)
    {
    var inputs= document.getElementById(‘checkbox1’).getElementsByTagName(‘input’);
    for (var i=0;i<inputs.length;i++)
    {
    if (inputs[i].value == id)
    {
    inputs[i].click();
    }
    }
    var inputs= document.getElementById('checkbox2').getElementsByTagName('input');
    for (var i=0;i<inputs.length;i++)
    {
    if (inputs[i].value == id)
    {
    inputs[i].click();
    }
    }

    }

    function selectInCheckboxRow(id)
    {
    var inputs= document.getElementById('checkbox1').getElementsByTagName('input');
    inputs[id].click();
    var inputs= document.getElementById('checkbox2').getElementsByTagName('input');
    inputs[id].click();

    }

Comments are closed.