Multiple rows to single column with comma separator using DB2 SQL

With the help of Repeater we can display multiple columns to single row in Cognos. The same can be achieved in SQL also.

This article helps you to align multiple rows to single column with the help of primary key/unique id.

For Example:
Data format in table:

DEPT_ID        EMP_NAME
11                    Alan
11                    Balan
11                    Mary
12                    Steve
12                    Rosy
13                    Mike
13                    Santa

Requirement is given below:

DEPT_ID        EMP_NAME
11                    Alan, Balan, Mary
12                    Steve, Rosy
13                    Mike, Santa

To achieve the above requirement, we can use recursive function. Below recursive SQL may helps you to get multiple rows to single column with comma as separator.

WITH L1(DEPT_ID,EMP_NAME,RN_SEQ)  AS
 (
SELECT   C.DEPT_ID,
         (C.EMP_NAME),
         C.RN_SEQ
FROM     (
 SELECT   DEPT_ID,
          CAST(EMP_NAME AS VARCHAR(10000))EMP_NAME,
          ROW_NUMBER() OVER(PARTITION BY DEPT_ID ORDER BY EMP_NAME) RN_SEQ
 FROM     <SCHEMA>.<TABLENAME>) C
 WHERE    C.RN_SEQ = 1          
 UNION ALL   
           
 SELECT   L2.DEPT_ID,
          L2.EMP_NAME ||','|| C.EMP_NAME,
          C.RN_SEQ
 FROM     (
   SELECT   DEPT_ID,
            CAST(EMP_NAME AS VARCHAR(100))EMP_NAME,
            ROW_NUMBER() OVER(PARTITION BY DEPT_ID) RN_SEQ
   FROM     <SCHEMA>.<TABLENAME>)C,
            L1 AS L2
 WHERE    L2.DEPT_ID = C.DEPT_ID
 AND      L2.EMP_NAME <> C.EMP_NAME
 AND      L2.RN_SEQ+1 = C.RN_SEQ           )
 SELECT   DEPT_ID,
          REPLACE(EMP_NAME,'.','')
 FROM     L1 AS L3
 WHERE    RN_SEQ = (
   SELECT   MAX(L4.RN_SEQ)
   FROM     (
   SELECT   DEPT_ID,
            CAST(EMP_NAME AS VARCHAR(100))EMP_NAME,
            ROW_NUMBER() OVER(PARTITION BY DEPT_ID ORDER BY EMP_NAME) RN_SEQ
   FROM     <SCHEMA>.<TABLENAME>) L4
   WHERE    L4.DEPT_ID = L3.DEPT_ID
   GROUP BY L4.DEPT_ID)          
          

Cognos TextBox Prompt - Numeric Validation

To validate the value given in Text Box Prompt and allow numeric value alone and text  length should be greater than 10.

 

1.       Create Html Item and place it at the top (Refer Figure 1).

2.       Double click the Html Item and include the below code.

<script>

var fW=(typeof getFormWarpRequest == "function" ? getFormWarpRequest() : document.forms["formWarpRequest"]);

function validatePrompt()

{

         str=fW._textEditBoxsPrompt.value;

  if ((str==null||str=="" ))   

                {

                // Show Error Message ...

                alert("Check your value");

                }

              else

                {

                        if ((str.length > 10) &&

                             str.match(/^[-]{0,1}\d*[.]{0,1}\d*$/g))

                       {  return false; }

                        else

                       {

                             if    (((str.length > 0) &&

                                      str.match(/^[-]{0,1}\d*[.]{0,1}\d*$/g)))

                                     { alert('Check your value'); return true;}

                else

                                {alert("Check your value");}

                           }

                    }

            }

function validateTextPrompt()

          {

                str=fW._textEditBoxsPrompt.value;

                if ((str==null||str=="" ))   

                {

                                // Show Error Message ...

                              alert("Check your value");

                }

                else

                {

                                if ((str.length > 10) &&

                              str.match(/^[-]{0,1}\d*[.]{0,1}\d*$/g))

                                {return false; }

                                else

                                {

                                if    (((str.length > 0) &&

                                     str.match(/^[-]{0,1}\d*[.]{0,1}\d*$/g)))                                      {

                                       canSubmitPrompt();

                                       promptButtonFinish();

                                       return true;

                                      }

                                else

                                                {alert("Check your value");}

                                }

                 }             

}

</script>

Figure 1

3.       Create Text Box Prompt and name it as sPrompt.

4.       Create HTML item and place it after the Text Box Prompt.

5.       Double click on Html Item and paste the below code.

<script>

 fW._textEditBoxsPrompt.onblur=validatePrompt;

</script>

6.        With the above code “validatePrompt” function is attached with the “Onblur” event. And this will be triggered at the time of leaving from Text Box Prompt. “ValidatePrompt” function will be called whenever user left from the Text Box prompt.

7.       Without entering any value in the text box prompt the user can click on the “Ok/Finish” button directly. In this case “onblur” event will not be triggered.

8.       To validate this, a HTML Item is placed at the Report footer.  And write the below code inside that HTML item.

 

<input type="BUTTON" class="clsPromptButton" onClick="validateTextPrompt();"  value="OK" 

style="background-color:FFFFFF ;

 font-size:13px;

font-family:verdana,tahoma;

font-weight:Bold,italic;

   color:#000000;

   width:55px;

   height:24px;

border-style:solid;

border-right-color:000000;

border-bottom-color:000000;

border-top-color:CCCCCC;

border-left-color:CCCCCC

"/>

9.       The above code will trigger the “ValidateTextPrompt” function to validate the text box. This function will open the report only after successful validation. Otherwise it will through error message and stay on the same page.

10.   Remove unnecessary prompt buttons and keep the required buttons. Refer Figure 2.

Figure 2

Hope the above example can help you to validate the value and it's length in Textbox prompt.

 

 

 

Cognos Date Prompt - Yesterday's Date as default date

1. Create a Date Prompt with a name "FromDate".
2. Create a "HTML Item" and place it right side of Date Prompt.
3. Double Click the HTML Item and place the below Code.

<script>
function subtractDay ()
{
  var dtToday = new Date(); // returns current date and time and assign the value to the variable dtToday
  var dtYesterday = new Date( dtToday - 86400000 ); // 86400000 = 24 hours * 60 (minutes per hour) * 60 (seconds per
minute) * 1000 (milliseconds per second)
  var strYesterday = [dtYesterday.getUTCFullYear(), dtYesterday.getMonth()+1, dtYesterday.getDate()].join("-");

  return strYesterday;
}
pickerControlFromDate.setValue( subtractDay() );
</script> 

4. Click Ok and close the HTML Item.
5. Run the report and see yesterday's date appears in Date Prompt by default.
 

Cognos - Split Multiple Value in Prompt

Hope many of us may struggle to find a solution to split the multiple text value given in a textbox prompt.
Below example may help us to understand the functionality of Macro function "CSV and Split", also it explains how these two macro functions works on filter
to seperate the comma(,) as a seperate value from textbox prompt.

#CSV helps to construct a string from the elements of the array where the values are separated by commas. The default separator is a comma ( , ) and the default quote character is a single quote ( ' ).
Split helps to Split a string or string elements of the array into separate elements

Case1:

Imagine Text Box Prompt has Value as "New York,Florida,Texsas,Washington"

Refer the below code to seperate the comma using filter.

([Data Item to be Filtered]) in (#csv( split(',', prompt('PARAM_Chr_State', 'token')))#)

Above code do not filter correctly if users put spaces between the comma-separated values (ie. "New York, Florida,Texsas, Washington" instead of "New York,Florida,Texsas,Washington")

Case2:

Imagine Text Box Prompt has Value as "New York, Florida,Texsas, Washington"

([Data Item to be Filtered]) in (#csv( split(',', substitute(' ','', prompt('PARAM_Chr_State', 'token')) ) )#)
The substitute expression will remove spaces entered by users between the comma-separated values and filter correctly.

Case3:

Imagine Text Box Prompt has Value as "New York, Florida,Texsas, Washington" or default value is "All".

([Data Item to be Filtered]) in (#csv( split(',', substitute(' ','', prompt('PARAM_Chr_State', 'token')) ) )#)

or 'All' in ?PARAM_Chr_State?

Above filter returns all the rows of data in case no values are entered in the Textbox prompt.

Imp: Above samples works only on string value.