Execute process task in ssis

Executable : C:\Program Files\7-Zip\7z.exe

Argument : example
e D:\Employees.7z -oD:\mahantesh

Source file path:D:\Employees.7z
Output path: D:\mahantesh
Expression:  "a "+ "\""+ @[User::zip_destFilepath]+ @[User::zip_outputfilename]+ "\""+" "+"\""+ @[User::zip_sourcefilepath]+ @[User::Filename]+"\""

 

Dynamic pivot query in sql

DECLARE @year AS NVARCHAR(MAX),

    @query  AS NVARCHAR(MAX)

select  @year= coalesce(@year+',['+cast((Year) as varchar(4))+']','['+cast(Year as varchar(4))+']')

from (select distinct  Year(OrderDate) year from [Sales].[SalesOrderHeader])t

set @query='

select Name,'+@year+' from (select p.Name,YEAR(OrderDate) year,LineTotal from  [Sales].[SalesOrderHeader] s

join [Sales].[SalesOrderDetail] o on s.SalesOrderID=o.SalesOrderID

join [Production].[Product] p on o.ProductID=p.ProductID) t

pivot

(

sum(LineTotal)

for year in ('+@year+')

) p1';

exec (@query)

Rank() in IBM Cognos crosstab

Using rank function in list is easy but in crosstab it headache
i got this solution

1)Create a data item with the expression like Rank([Measure culumn]) and name it as Rank.
2)Create another data item with expression running-count([Rank]) name it as count
3)Use this count in filter and set application as after auto aggregation.

Year over year analysis in cognos

My requirement is  that:
whenever i pass a year through prompt i should get a report with details of selected year and previous year.

To achieve that create a two data items similar to below expression
date item :current Year

if(?p_year?=[ Year column] )
then
([Measure column])
else
(0)

date item2: Previous year

if ([Year column]=(?p_year? )-1)
then
([Measure column])
else
(0)

these two will give you the desired results.

Constant Total column in cognos crosstab

while calculating percentage of contribution to the total by each individual department we need to keep total value constant.
For that i used following expression in data item .

total("Measure column" within set "Dimension column")-this gives constant total column.
example:
Total([bill hours] within set [departments])

Create one more data item for percentage values.
[bill hours]/total

                                                                                         The above images explains the scenario.

Removing first 2 drop down values from values prompt

1-Give a suitable name to your prompt.

In this case prompt name is:Prompt1

2-Place a html item after prompt.

3-paste following javascript.

<script language="javascript">
var f = getFormWarpRequest();
var list = f._oLstChoicesPrompt1;

list.remove(1);
list.remove(0);
list.removeAttribute("hasLabel");
list.options[0].selected = true;

canSubmitPrompt();
</script>

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