Eliminating duplicates from prompt drop down in cognos value prompt

I was facing difficulty in removing duplicates from drop down .And i got the solution also.

I was using use vale and display value as two different values it was causing problem .so i changed both display value and use value from the same column and i got my solution. As shown below.

Drop down values as default value in cognos value prompt

We can select first or second or nth values form the drop down as default selection .

step1:
create a html item with the following script and placed it left of the prompt.

<span id="Period">

Period is my prompt name:
In prompt properties miscellaneous you can get option to set name.

step2:
create another html using below script.and placed it on right of the prompt.

</span>

Step3:

create one more html item with below script and placed it right to the above html.

<script type="text/javascript">

var theSpan = document.getElementById("promptname");

var theSelect = theSpan.getElementsByTagName("select");

theSelect[0].options[2].selected=true;

</script>

This example is selecting first value  as default .

You can change values based on your requirement in option( theSelect[0].options[2].selected=true;)

To select options in cognos search prompt

Name your search prompt.
Example=RClient

Create 2 HTML items

In 1st html paste this javascript

<div id="RClient">

In 2nd HTML paste this js


</div>
<script>
/* Select and search options
 * 2 - Starts with any of these keywords * DEFAULT
 * 3 - Starts with the first keyword and contains all of the remaining keywords
 * 4 - Contains any of these keywords
 * 5 - Contains all of these keywords
 */
document.getElementById('RClient').getElementsByTagName('input')[3].click();
</script>

In my case it will select the  3 - Starts with the first keyword and contains all of the remaining keywords.
You can change it based on your requirement.

Splitting of string from right side or in reverse order

WITH test
AS (
 SELECT Employee_no
  ,Project_code
  ,cast(CASE 
    WHEN charindex(',', reverse(Project_code)) > 0
     THEN substring(Project_code, len(Project_code) - charindex(',', reverse(Project_code)) + 2, CHARINDEX(',', Project_code))
    ELSE Project_code
    END AS VARCHAR(50)) Project_code1
  ,stuff(Project_code, len(Project_code) - charindex(',', reverse(Project_code)) + 1, charindex(',', (Project_code)), '') dta
  ,charindex('', Project_code) chr
 FROM sample_data_emp
 WHERE Employee_no IS NOT NULL
 
 UNION ALL
 
 SELECT Employee_no
  ,Project_code
  ,cast(CASE 
    WHEN charindex(',', reverse(dta)) > 0
     THEN substring(dta, len(dta) - charindex(',', reverse(dta)) + 2, CHARINDEX(',', dta))
    ELSE dta
    END AS VARCHAR(50)) dta
  ,stuff(dta, len(dta) - charindex(',', reverse(dta)) + 1, charindex(',', (dta)) + 1, '') dta
  ,charindex('', dta) chr
 FROM test
 WHERE dta <> ''
 )
SELECT Employee_no
 ,Project_code
 ,Project_code1
FROM test

Split column value using sql

CREATE TABLE split_sample (
 Employee_no INT identity(1, 1)
 ,Project_code VARCHAR(50)
 )

INSERT INTO split_sample (Project_code)
VALUES ('2001,2003,2004')
 ,('')
 ,('2001')
 ,('2001,2002')
 ,('2002,2003,2004')

SELECT * FROM split_sample 

WITH cte_table AS (
  SELECT Employee_no
   ,cast(CASE 
     WHEN charindex(',', Project_code) > 0
      THEN SUBSTRING(Project_code, 1, charindex(',', Project_code) - 1)
     ELSE Project_code
     END AS VARCHAR(50)) Project_code
   ,CASE 
    WHEN charindex(',', Project_code) > 0
     THEN stuff(Project_code, 1, charindex(',', Project_code), '')
    ELSE ''
    END AS cte_str
  FROM split_sample
  
  UNION ALL
  
  SELECT Employee_no
   ,cast(CASE 
     WHEN charindex(',', cte_str) > 0
      THEN SUBSTRING(cte_str, 1, charindex(',', cte_str) - 1)
     ELSE cte_str
     END AS VARCHAR(50)) cte_str
   ,CASE 
    WHEN charindex(',', cte_str) > 0
     THEN stuff(cte_str, 1, charindex(',', cte_str), '')
    ELSE ''
    END AS cte_str
  FROM cte_table
  WHERE cte_str <> ''
  )

SELECT Employee_no
 ,Project_code
FROM cte_table

Example of using Left join,Self join and union all.

CREATE TABLE [dbo].[Folder] (
    [FolderID]         INT NOT NULL PRIMARY KEY IDENTITY(1, 1),
    [FolderName]       VARCHAR(100) NOT NULL,
    [ParentFolderID]   INT NULL REFERENCES [dbo].[Folder]( [FolderID] ) 
) 

CREATE TABLE [dbo].[File] (
    [FileID]           INT NOT NULL PRIMARY KEY IDENTITY(1, 1),
    [FolderID]         INT NOT NULL REFERENCES [dbo].[Folder] ( [FolderID] ),
    [FileName]         VARCHAR(100)
)

INSERT INTO [dbo].[Folder] ( [FolderName] )
VALUES ( 'Inetpub' )

INSERT INTO [dbo].[Folder] ( [FolderName] )
VALUES ( 'Program Files' )

INSERT INTO [dbo].[Folder] ( [FolderName] )
VALUES ( 'Windows' )

INSERT INTO [dbo].[Folder] ( [FolderName], [ParentFolderID] )
VALUES ( 'wwwroot', 1 )

INSERT INTO [dbo].[Folder] ( [FolderName], [ParentFolderID] )
VALUES ( 'Microsoft Office', 2 )

INSERT INTO [dbo].[Folder] ( [FolderName], [ParentFolderID] )
VALUES ( 'Microsoft SQL Server', 2 )

INSERT INTO [dbo].[Folder] ( [FolderName], [ParentFolderID] )
VALUES ( 'assembly', 3 )

INSERT INTO [dbo].[Folder] ( [FolderName], [ParentFolderID] )
VALUES ( 'system32', 3 ) 

INSERT INTO [dbo].[File] ( [FolderID], [FileName] )
VALUES ( 4, 'iisstart.htm')

INSERT INTO [dbo].[File] ( [FolderID], [FileName] )
VALUES ( 8, 'calc.exe')

INSERT INTO [dbo].[File] ( [FolderID], [FileName] )
VALUES ( 8, 'notepad.exe')

 and the reuslt is shown in the image,

If nulls in the parentid means separate and left join with the file table to get file names with the folder names,

select A.FolderID FolderID,'/'+A.FolderName FolderName,'' as ParentFolder,B.FileName FileName from Folder A
left join [File] B
on A.FolderID=B.FolderID
where A.ParentFolderID is null

And separate non null parentid and join with folder table                                      select m.FolderID,'/'+m.FolderName FolderName,'/'+t.FolderName+'/'+m.FolderName as p,m.FileName from Folder t
join (
select A.FolderID FolderID,A.FolderName FolderName,A.ParentFolderID as ParentFolder,B.FileName FileName from Folder A
left join [File] B
on A.FolderID=B.FolderID
where A.ParentFolderID is not null)m
on t.FolderID=m.ParentFolder

and union all both queries to get desired results 

select A.FolderID FolderID,'/'+A.FolderName FolderName,'' as ParentFolder,B.FileName FileName from Folder A
left join [File] B
on A.FolderID=B.FolderID
where A.ParentFolderID is null

union all

select m.FolderID,'/'+m.FolderName FolderName,'/'+t.FolderName+'/'+m.FolderName as p,m.FileName from Folder t
join (
select A.FolderID FolderID,A.FolderName FolderName,A.ParentFolderID as ParentFolder,B.FileName FileName from Folder A
left join [File] B
on A.FolderID=B.FolderID
where A.ParentFolderID is not null)m
on t.FolderID=m.ParentFolder