As you can probably imagine, I am a BI developer working with Cognos. Before Cognos I worked mostly with Hyperion Brio.

I have about 6 years of experience working with Cognos. In total I’ve spent almost a decade so far in the BI business.

I work for an outsourcing company, and have had the pleasure of working with a large variety of companies including  a large bank, a pharmaceutical company, and various government departments. This has given me the opportunity to see a wide range of implementations and methodologies.


68 Responses to About

  1. Gary says:

    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?

    • cognospaul says:

      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. Number one cognos fan says:

    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

    • cognospaul says:

      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. Arik says:

    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)

    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. cognospaul says:

    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!

  5. David says:

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

    David F.

  6. Rebecca says:


    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.

  7. Maurizio says:

    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…

    • CognosPaul says:

      Hi Maurizio,

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

      <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;


      • Maurizio says:

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


        • Maurizio says:

          This code works fine…

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

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

          • CognosPaul says:

            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;

  8. Reddy says:

    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) {

    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);
    } catch (exception) {
    alert(“Invalid file name” + fileName);

    Hope to here from you

    • CognosPaul says:

      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. baskar says:

    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


    • CognosPaul says:

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

      restrict that to your developers.

      • bhaskar says:

        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.

        • CognosPaul says:

          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. Arik says:

    Hi Paul…

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

    on COGNOS 10, report have 2 input parameters :

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

    • CognosPaul says:

      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. Arik says:

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

    • CognosPaul says:

      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. Meir Raz says:

    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.


    • CognosPaul says:

      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. rmabjish says:

    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


    • CognosPaul says:

      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. Jane Shen says:

    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,

  15. rm says:

    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

    • CognosPaul says:

      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. Donna says:

    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?

    • CognosPaul says:

      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. Heavy debugger says:

    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.
    אני לבד בשדה הקרב

    • CognosPaul says:

      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. Heavy debugger says:

    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. tzipora says:

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

    • CognosPaul says:

      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. Heavy debugger says:

    what is the input and output of this function?

  21. Sweer says:

    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

    • CognosPaul says:

      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. Heavy debugger says:

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

  23. Heavy debugger says:

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

    • CognosPaul says:

      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;
        insert into comments
          (comment, user_id, last_updated_dt)
          (i_comment, i_user_id, sysdate);
        open o_cur1 for
          select 1 from dual;
        when others then
                                  'an error was encountered - ' || sqlcode ||
                                  ' -error- ' || sqlerrm);

      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. Sam says:

    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
    EBT Gross Margin
    Total Overhead

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


  25. Sam says:

    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. Scott says:

    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.

    • CognosPaul says:

      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]))

      • Vijay P says:

        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.


  27. KSR says:

    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…


  28. Salman Shahid says:

    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.

    • CognosPaul says:

      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. Valiant says:

    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?


  30. Matt says:

    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)
    var inputs= document.getElementById('checkbox2').getElementsByTagName('input');
    for (var i=0;i<inputs.length;i++)
    if (inputs[i].value == id)


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


  31. Ankita says:

    Hi Paul,
    When working with cube as database, how can we select multiple values in a list box.


  32. Kumar says:

    Hi Paul,

    I need a your help, we have a package with data source pointing to Dev(imported tables from dev), and the table structure is same across all the environments (i.e. dev, test, uat, prod). We have a data source connection with two connections one for Dev and other one for test,
    When i run a report pointing to test connection and it is still pointing to Dev.

    so in my case I need to have the data source dynamically when running the report can you please help me out to fix this thing.
    Below are the details, my schema are not in the same server they are in different servers.


    schema 1
    schema 2


    schema 1
    Schema 2

    Datasoruce Connection : gtos
    connections : Devbox,Test Box

  33. Jessie says:

    Hi Paul, Could you please guide how to trigger a cognos cube to schedule reports on a particular timeframe using Ctrl M scheduling tool.

  34. knag says:

    Hi Paul,

    i ave an issue with javascript if i select company prompt the particular name should display in the textbox prompt.

  35. suresh says:

    Hi All
    I am new to this Cognos portal.
    My question is actually previous we are using hyperion reporting tool.
    1.we develop the reports in hyperion,In diffrent pages but here my question is Transationcode is display in all pages.
    2.Then we migrate Hyperion To Cognos but here my question is Transationcode is displayed first page only but remaining pages Transationcode is Not displaying.
    3.Can u please help me.

  36. suresh says:

    Hi Paul,
    I am new to this Cognos portal.
    My question is actually previous we are using hyperion reporting tool.
    1.we develop the reports in hyperion,In diffrent pages but here my question is Transationcode is display in all pages.
    2.Then we migrate Hyperion To Cognos but here my question is Transationcode is displayed first page only but remaining pages Transationcode is Not displaying.
    3.Can u please help me.

  37. shalini says:

    your blog is really interesting and it is very well useful for me to know more concepts. it is very much expressive.

  38. Daniel says:

    Paul, love your work , please keep it up. Quick question, what is the equivalent of “valuePrompt.options[0].selected == true” for a date prompt selection?

  39. Thulasi says:

    Hi Paul,

    I have a report with custom SQL in cognos 10.2.2.

    I have a filter a on the day key column on a table:
    DAY_CAL_DIM_KEY data type is number,

    DAY_CAL_DIM_KEY=to_char(to_date(#sq(prompt(‘LastDate’,’date’))#, ‘YYYY-MM-DD’ ),’YYYYMMDD’)
    Here my DAY_CAL_DIM_KEY is in yyyymmdd format .

    I want to make this report optional.
    To make this optional i added 3rd parameter to the prompt().
    DAY_CAL_DIM_KEY=to_char(to_date(#sq(prompt(‘LastDate’,’date’,’20170301′))#, ‘YYYY-MM-DD’ ),’YYYYMMDD’)
    The report is running fine when i give a hard coded value, but when i want to run it for ‘ trunc(sysdate-1)’ , the report is throwing error.

    To achieve this i gave the filter as
    to_char(to_date(#sq(prompt(‘LastDate’,’date’,’trunc(sysdate-1,’month’)’))#, ‘YYYY-MM-DD’ ),’YYYYMMDD’)
    But it is throwing error.

  40. David h says:

    I Found This Blog very Informative

  41. Dan says:

    Paul, thanks for sharing your knowledge with the community, I have found your blog very helpful indeed. I do have a query, I understand how to implement javascript print to pdf and excel on regular reports, but the same method does not seem to work when implemented on a portal page, print to pdf works but not print to excel, any ideas?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: