Reporting Services and Date functions and Date Parameters

I would get the stored procedure you use to return the data for the report to do the work, so get the user to select the date parameter and manipulate as you've described, please see my example below using AdventureWorks2008.

select * from HumanResources.EmployeePayHistory where RateChangeDate = dateadd(dd,-2,@Date)

The one thing you will need to be careful of is which type of field you have used whether it's Date or Datetime, as if it's datetime you may need to consider the timings aswell.

ALTER PROCEDURE [dbo].[usp_RptProc1]( @EndDate Datetime)
AS DECLARE @StartDate Datetime SET @StartDate = @EndDate -2
SET TRANSACTION ISOLATION LEVEL READ COMMITTED; select Sum(OpenBalance) +sum(NewCalls) - sum(CloseDuringPeriod) as ClosingBalance ,Priority, convert(char(10),periodEnddate,121) periodEnddate,Service
from (

SELECT CallLog.CallID, CallLog.CustType, CallLog.CallType, CallLog.CallStatus, CallLog.Environment,
CallLog.Service, CallLog.CallDesc, CallLog.HeatReleaseRef, 'P' + CallLog.Priority Priority,
CallLog.RecvdDate,RecvdTime,CallLog.ClosedDate,ClosedTime,ResolvedTime,
case when (CallStatus = 'Open' and CallLog.RecvdDate + ' ' + RecvdTime < convert(varchar(10),@StartDate,121) + ' 00:00:00')
or (CallLog.ClosedDate + ' ' + ClosedTime >= convert(varchar(10),@StartDate,121) + ' 00:00:00' and CallLog.RecvdDate + ' ' + RecvdTime < convert(varchar(10),@StartDate,121) + ' 00:00:00')
or (CallLog.ResolvedDate + ' ' + ResolvedTime >= convert(varchar(10),@StartDate,121) + ' 00:00:00' and CallLog.RecvdDate + ' ' + RecvdTime < convert(varchar(10),@StartDate,121) + ' 00:00:00') then 1 else 0 end as OpenBalance ,
case when (CallLog.ClosedDate + ' ' + ClosedTime between convert(varchar(10),@StartDate,121) + ' 00:00:00' and convert(varchar(10),@EndDate,121) + ' 23:59:59')
or (CallLog.ResolvedDate + ' ' + ResolvedTime between convert(varchar(10),@StartDate,121) + ' 00:00:00' and convert(varchar(10),@EndDate,121) + ' 23:59:59')then 1 else 0 end as CloseDuringPeriod,
case when CallLog.RecvdDate + ' ' + RecvdTime between convert(varchar(10),@StartDate,121) + ' 00:00:00' and convert(varchar(10),@EndDate,121) + '23:59:59' then 1 else 0 end as NewCalls
,@StartDate as periodStartDate
,@EndDate as periodEnddate
FROM HEAT.dbo.CallLog CallLog
WHERE --CallLog.Service='Equities' --
(CallLog.Service Like 'Bonds%' OR CallLog.Service='Equities' OR CallLog.Service='Money Markets' OR Calllog.Service='BND')
AND (CallLog.Environment Like 'Prod%')
AND (CallLog.RecvdDate + ' ' + RecvdTime between convert(varchar(10),@StartDate,121) + ' 00:00:00' and convert(varchar(10),@EndDate,121) + ' 23:59:59' )
or (CallLog.ClosedDate + ' ' + ClosedTime between convert(varchar(10),@StartDate,121) + ' 00:00:00' and convert(varchar(10),@EndDate,121) + ' 23:59:59')
or (CallLog.ResolvedDate + ' ' + ResolvedTime between convert(varchar(10),@StartDate,121) + ' 00:00:00' and convert(varchar(10),@EndDate,121) + ' 23:59:59' )
or (CallLog.RecvdDate + ' ' + RecvdTime < convert(varchar(10),@StartDate,121) + ' 00:00:00' and CallLog.CallStatus = 'Open'))

and to sum up all my open calls i have another stored procedure: ALTER PROCEDURE [dbo].[usp_RptProc2](@EndDate Datetime)
AS
DECLARE @StartDate Datetime
set @StartDate= @EndDate - 2
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT CallLog.CallID, CallLog.CustType, CallLog.CallType, CallLog.CallStatus, CallLog.Environment,
CallLog.Service, CallLog.CallDesc, CallLog.HeatReleaseRef, 'P' + CallLog.Priority Priority,CallLog.RecvdDate,CallLog.ClosedDate,
case when (CallStatus = 'Open' and CallLog.RecvdDate < @StartDate)
or (CallLog.ClosedDate >= @StartDate and CallLog.RecvdDate < @StartDate) or (CallLog.ResolvedDate >= @StartDate and CallLog.RecvdDate < @StartDate )then 1 else 0 end as OpenBalance,
case when --(CallStatus in ('Closed','Resolved')) or
(CallLog.ClosedDate between @StartDate and @EndDate) or (CallLog.ResolvedDate between @StartDate and @EndDate)then 1 else 0 end as CloseDuringPeriod,
case when CallLog.RecvdDate between @StartDate and @EndDate then 1 else 0 end as NewCalls
FROM HEAT.dbo.CallLog CallLog
WHERE --CallLog.Service='Equities' --
(CallLog.Service Like 'Bonds%' OR CallLog.Service='Equities' OR CallLog.Service='Money Markets' OR Calllog.Service='BND')
AND (CallLog.Environment Like 'Prod%')
--AND (CallLog.CallStatus<>'Closed'
--And (CallLog.CallStatus<>'Resolved')
AND ((CallLog.RecvdDate between @StartDate and @EndDate)
or (CallLog.ClosedDate between @StartDate and @EndDate)
or (CallLog.ResolvedDate between @StartDate and @EndDate)
or (CallLog.RecvdDate < @StartDate and CallLog.CallStatus = 'Open'))
ORDER BY CallLog.CallID,CallLog.Service, CallLog.CustType, CallLog.CallType

What i'm trying to achieve is to get two days worth of data at a time. what am i doing wrong? Today's closing balance must be tomorrow's opening balance