Rewrite correlated subqueries in cognos compatible way. Decorrelating subqueries

Correlated subquery scenario 1

SELECT c.LastName, c.FirstName,
(SELECT CompanyName FROM Company WHERE ID = c.CompanyID) AS CompanyName
FROM Customer c

Rewrite to

SELECT c.LastName, c.FirstName, cm.CompanyName
FROM Customer c LEFT OUTER JOIN Company cm
ON c.CompanyID = cm.CompanyID



Correlated subquery scenario 2

select *

from T1

where T1.a > (select max(T2.a) from T2 where T2.b = T1.b)

Rewrite to

select T1.*

from T1, (select T2.b, max(T2.a) max_a from T2 group by T2.b) S

where T1.b = S.b and T1.a > S.max_a

xming can't connect to x11 window server using ---- xming error putty

Problem :

xming can't connect to x11 window server using


In run command, enter command in following format:

"C:\Program Files (x86)\Xming\Xming.exe" :0 -clipboard -multiwindow -ac

Providing option -ac has resolved my error.
Note: Installation path might be different in your machine.

Sub query subquery in Cognos Framework Manager

The only way to implement subquery in Cognos Framework Manager can be done by directly writing in Query Subject Definition or writin in Parameter map and then use it in Query Subject Definition (for reusability)

How to deal with queries with EXISTS clause in Cognos?

• How to deal with queries with EXISTS clause in Cognos?
  Here describes how to rewrite SQL queries containing EXISTS clause to Cognos compatible way using MINUS & INTERSECT.

  
  

  
  

  Original query:

  SELECT tc_lpn_id

    FROM lpn LP

    WHERE lpn_facility_status IN (45, 50, 55, 64)

    AND NOT EXISTS

      (SELECT 1

      FROM task_dtl TD

      WHERE TD.cntr_nbr = lp.tc_lpn_id

      AND TD.stat_code  < 90

      )

    AND EXISTS

      (SELECT 1

      FROM alloc_invn_dtl AD

      WHERE AD.cntr_nbr = lp.tc_lpn_id

      AND AD.stat_code  < 90

      )

  
  

  Modified for Cognos:

  SELECT tc_lpn_id FROM lpn LP WHERE lpn_facility_status IN (45, 50, 55, 64)

  minus

  select tc_lpn_id from lpn l, task_dtl t where t.cntr_nbr = l.tc_lpn_id and t.stat_code < 90

  intersect

  select tc_lpn_id from lpn l, alloc_invn_dtl a where a.cntr_nbr = l.tc_lpn_id and a.stat_code < 90

  
  

  
  

  Note:

  While rewriting queries, Intersect might eliminates duplicates. To avoid that ensure that correlated join condition column of parent is selected in select clause. This will overcome the problem.

  
  

  
  

  
  

  
  

Over (partition by ) query in Cognos Report Studio

If we need cognos to generate analytical function over(partiontion by):

Type	Expression	Cognos query generated
Over() – without partition	total([Planning & Execution].[Shipment].[Shipment ID] for  report)	Fig1
Over (partition by column1,column2,..)	total([Planning & Execution].[Shipment].[Shipment ID] for [Shipment Distance UOM], [Shipment Created Source],[Shipment Last Updated Source])	Fig2

Fig1

Fig2

Note: Set query Auto Group & Summarize option to No

Cognos split crosstab corner cell Report Studio

Below steps will show you how to split Crosstab corner cell (to add title).

1. Drag a text item from toolbox to the columns list, place it as left most column - Type required name

2. Same way drag text items as many as required

3. Select crosstab corner cell and change box type property as "None".

4. Select Column Body of all newly added text items and then change box type property as "None"

Finally you got the titles as you wanted!!! 

Cognos Frame work manager parameter map cache

If Parameter map is based on a query subject, where and when parameter map is written, underlying query will be executed

Cognos Framework manager parameter map maximum size limit

32768 characters is the maximum size limit of paramater map value for a single key

Cognos Report Studio not opening showing blank or report output not showing

In IE 10, Cognos might not work properly because of compatibility issue.

Solution:

Just click Compatibility View button which will be displayed in these cases just between URL bar and Refresh button.

Eclipse window builder installation guide set up tutorial sample simple program

In Eclipse, go to
Help -> Install new software
Work with -> Juno - http://download.eclipse.org/releases/juno
It will list available plugins, Navigate to General purpose, select all window builder components and swt, swing designer components and then click install.

Once installed, it will prompt for restart.

Below is the sample program

Create a new class with name One, Right Click on it from Package Explorer -> open with Window builder editor. Then insert below sample code to it and then click design tab in left bottom corner of editor window.

import java.awt.BorderLayout;

import javax.swing.JFrame;

import javax.swing.JTextArea;

public class One {    // Your class name

  public static void main(String[] args) {

    

    JFrame f = new JFrame("A JFrame");

    f.setSize(250, 250);

    f.setLocation(300,200);

    f.getContentPane().add(BorderLayout.CENTER, new JTextArea(10, 40));

    f.setVisible(true);

    

  }

}

Eclipse window builder error - This is not a GUI class and cant be edited graphically

The parser parsed the compilation unit, but can't identify any GUI toolkit.
Possible reasons:

• You open a compilation unit without any GUI, such as an empty class, data bean, etc.
• Classpath of project is not valid, does not include all required toolkit libraries.
• You attempt to open UI for toolkit, support for which is not installed in WindowBuilder.
  Use button Show UI Toolkits to install new toolkit.  

Even after installing Window builder components in eclipse, if you get this error, switch to code and insert below code and then switch to design

import java.awt.BorderLayout;

import javax.swing.JFrame;

import javax.swing.JTextArea;

public class One {    // Your class name

 public static void main(String[] args) {

   

   JFrame f = new JFrame("A JFrame");

   f.setSize(250, 250);

   f.setLocation(300,200);

   f.getContentPane().add(BorderLayout.CENTER, new JTextArea(10, 40));

   f.setVisible(true);

   

 }

}

cognos javascript multi select checkbox default checked all checkbox

Cognos Javascript prompt functions does not work with checkbox prompts. So you have to do using following functions. It is working for me.


<script type="text/javascript">
var fW = (typeof getFormWarpRequest == "function" ? getFormWarpRequest() : document.forms);

if ( !fW || fW == undefined) {
fW = ( formWarpRequest_THIS_ ? formWarpRequest_THIS_ : formWarpRequest_NS_ );
}

if (fW) {
var list = fW._oLstChoicesChkbox1;
if(list.length == null) {             // if there is only one option
list.checked = "checked";
list.onclick();
}
else {
for( i=0; i<list.length;i++) {
list[i].checked = "checked";
list[i].onclick();
}
}
}
</script>

Cognos javascript to format color every numeric digits in page

<script>

window.onload=coloring();

function coloring()

{

    var spans = document.getElementsByTagName('span'),

    obj = {};

    var r = /[(]\d+[)]/;   /////////pattern will color every number of pattern: (1234)

    for (var i = 0, l = spans.length; i < l; i++) {

    obj[spans[i].id] = spans[i].textContent || spans[i].innerText;

        if (spans[i].innerText.match(r))    {

          alert( spans[i].innerText);

          spans[i].style.color="#FF0000";

        }

    }

}

</Script>

JavaScript function to call another link in background applicable to Cognos too

<div id="bgFrame"></div>

<script type="text/javascript">

setTimeout(prepareiFrame,100);

function prepareiFrame() {

bgifrm = document.createElement("IFRAME");

document.getElementById("bgFrame").appendChild(bgifrm );

bgifrm.style.display="none";

bgifrm.setAttribute("src", " http://10.11.11.181:5050/Default.aspx?UserName=Cognos");

}

 </script>

JavaScript function to place div above another object

<!-- DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd" -->

<script type="text/javascript" language="javascript">

function show_hide_functiondf()

{

if(document.all("MyDivdf").style. visibility =="visible")

{

document.all("MyDivdf").style. visibility ="hidden";

}

else

{

document.all("MyDivdf").style. visibility ="visible";

}

}

</Script>

<html>

<head>

<body>

<input id="Button1" type="button" value="Select Date" onclick="show_hide_functiondf()" />

</br>

<Div id="MyDivdf" style="visibility : hidden; position: absolute; border : red 3px solid; background: green">

Above

</Div>

Bottom

</body>

</head>

</html>