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>