Thanks all for your reply....Shall try and let you know guys!

Thanks for you reply.

My Paramater time is datetime only.... I have not changed anythin wrt reporting side....everythig is the same...just instaed accessing the query directly, I had created a SP.

The...

My where clause should be just between startdate and enddate...but wheras I have use start of week also in the where clause...

I hope wat I had figured out is right..

Thanks

I ve tried out in SSRS.....It causes prob...alright wil try wit temp tables...thank u

Im just thinking wat other ways wld be possible to join the result set of 2 sp's apart from using Temp table....

Thank You

You mean to say from SPA- get the results into a temp table

SPb- get the results into another temp table

and combine their results?? something like this?

Thanks!

Yah,

Say SP A's results will be in this format as below:

EmpNoEmpName TotalAWeek

1A 2 20/08/2012

1B 3...

Ya can create two datasets...

But summing up the value from Dataset1+Dataset2 doesnt work in a matrix report

Say I have a rpt like

COl Week1 ...

Alright....Will check out...thanks

Thanks for your reply.

When I test the connection in the connection manager to the destination server, the connection is successful. Say if I run the package 5 times, 3 times...

Ya date is the only column that is common between 2 matrix report

and the the third matrix report should the total of both the matrix reports date wise

Metric ...

Ya , I tried it out Thanks!

But I feel this will not be an optimal solution satisfying the requirement. Better option would be a date dimension table. Thanks anywyas!

Thannks Both of you

The above query works :

Declare @Param_Start date,

@Param_End date

select @Param_Start='11/05/2012',

@Param_End='11/11/2012'

;with cte1(date1)

as

(

select convert(date,@Param_Start)

union all

select DATEADD(week,1,date1) from cte1

where date1<=@Param_End

)

select * from cte1

But my concern is say I...

Thanks for ur reply..

Week is a calcualted feild from date and I had changed the string to date and it works fine. Thanks!

Thanks all for ur reply!