February 7, 2014 at 8:11 am
Using MS SQL SERVER 2008, Report Builder 3.0. have created a query containing 2 datasets, Dataset1 shows the arrears balance and date for one week, ie 30.06.2013, Dataset2 shows the arrears balance and date for another week, ie 07.07.2013, this will allow users to check if arrears are going up or down. The query for both datasets used the same table that contains 3 elements, the TENALTKEY is the unique identifier for each addresss, the ARRSDATE is the arrears date, and the ARRBAL is the arrears balance. I need some advice in how I can create 2 parameters that will allow users to choose what dates they want to view, ie this week and last week dates. I am new to sql reporting so simple examples and explanations would be appreciated, thank you
Dataset1 =
SELECT
stagingarrsbal.tenaltkey
,stagingarrsbal.arrsdate
,stagingarrsbal.arrbal
FROM
stagingarrsbal
INNER JOIN stagingTenancy
ON stagingarrsbal.tenaltkey = stagingTenancy.tenaltkey
WHERE
stagingarrsbal.arrsdate = N'2013-06-30T00:00:00'
AND stagingarrsbal.arrbal > 0
AND stagingTenancy.tenind != N'F'
Dataset2=
SELECT
stagingarrsbal.tenaltkey
,stagingarrsbal.arrsdate
,stagingarrsbal.arrbal
FROM
stagingarrsbal
INNER JOIN stagingTenancy
ON stagingarrsbal.tenaltkey = stagingTenancy.tenaltkey
WHERE
stagingarrsbal.arrsdate = N'2013-07-07T00:00:00'
AND stagingarrsbal.arrbal > 0
AND stagingTenancy.tenind != N'F'
February 10, 2014 at 5:48 pm
There are a couple of ways of doing this, but for maximum flexibility I have done it by creating 4 datetime report parameters (a new date range and an old range):
@newstart
@newend
@oldstart
@oldend
Then you could modify your queries something like this (untested code):
Dataset1 =
SELECT
stagingarrsbal.tenaltkey
,stagingarrsbal.arrsdate
,stagingarrsbal.arrbal
FROM
stagingarrsbal
INNER JOIN stagingTenancy
ON stagingarrsbal.tenaltkey = stagingTenancy.tenaltkey
WHERE
stagingarrsbal.arrsdate >= @newstart
AND stagingarrsbal.arrsdate < DATEADD(DAY, 1, DATEDIFF(DAY, 0, @newend))
AND stagingarrsbal.arrbal > 0
AND stagingTenancy.tenind != N'F'
Dataset2=
SELECT
stagingarrsbal.tenaltkey
,stagingarrsbal.arrsdate
,stagingarrsbal.arrbal
FROM
stagingarrsbal
INNER JOIN stagingTenancy
ON stagingarrsbal.tenaltkey = stagingTenancy.tenaltkey
WHERE
stagingarrsbal.arrsdate >= @oldstart
AND stagingarrsbal.arrsdate < DATEADD(DAY, 1, DATEDIFF(DAY, 0, @oldend))
AND stagingarrsbal.arrbal > 0
AND stagingTenancy.tenind != N'F'
That way users can compare any two time windows they wish.
February 14, 2014 at 5:04 am
really kind of you to respond, will give it a go and post the outcome, thanks again
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply