March 21, 2012 at 9:19 am
Hi
I have two Matrix tables in the same report each containing the same information (both tables consist of 3 columns: Date, Sales and Location). Essentially I want to put filters on the two tables so that one of them shows information for the past week and the other for the past month. I know that I need to apply filters within Tablix Properties but I'm not sure how I'd achieve what i want ie show the last weeks/months data regardless of which day the report is run on.
Your help would be much appreciated.
BO
March 21, 2012 at 9:47 am
in terms of past week is it say, I run the report on a Friday so I want to see data from Friday to Friday (exact 7 days) or is it Monday to Friday(working week) and also if I run the report on a Tuesday is is Tuesday to Tuesday or just Monday&Tuesday?
for the month is it to the beginning of the month to the current day or is it 21st Feb to 21st March?
March 21, 2012 at 10:00 am
Ideally I'd like the report to show Monday to Friday for the weekly report but only ever show the last full working week. So if i was to run it today for example (21st March) I'd only be able to view data for 12th March - 18th March (the last full week available). Is this possible?
The monthly would work in a similar fashion insofar as it would go up to the last complete working week (the last Sunday) but would always start from the 1st day of the month. So using today again as the example the report would cover 1st March - 18th March.
March 21, 2012 at 10:03 am
yeah will be do able, can do it in the t-sql for the data set or in the tablix properties as a filter expression.
i'm leaving the office for the day so if someone hasn't come back on topic when i get in tomorrow i will knock up a sample
March 21, 2012 at 10:14 am
Cheers Anthony!!
March 21, 2012 at 11:45 am
Query the full month's worth of data into your dataset.
Put a filter on your weekly that is something like Datefield >= Dateadd("d",-7,Today()).
Put a filter on your monthly that is something like Datefield >= Dateadd("m",-1,Today())
March 21, 2012 at 12:40 pm
Daniel Bowlin (3/21/2012)
Query the full month's worth of data into your dataset.Put a filter on your weekly that is something like Datefield >= Dateadd("d",-7,Today()).
Put a filter on your monthly that is something like Datefield >= Dateadd("m",-1,Today())
that won't fulfill the it's requirements as it will do a full week and not the last full working week so will need to be an expression based on the day of the week minus the right number to get to the Monday of that week then add 5 to get the Friday then do a between
then for the month it will need to minus the the datepart day from today to get the 1st of the month then do a >= the value
March 22, 2012 at 2:24 am
I haven't tested this so please take what I give as a guide
Ok for the weekly report running from Monday @ 00:00:00 to Saturday @ 00:00:00, you can use these expressions at the tablix side of things
#To Get The Monday @ 00:00:00
=IIF(
Weekday(Fields!DateCollected.Value) = 1, DATEADD("d",-6,Format(Today(),"yyyy-MM-dd")),
IIF(
Weekday(Fields!DateCollected.Value) = 2, DATEADD("d",-7,Format(Today(),"yyyy-MM-dd")),
IIF(
Weekday(Fields!DateCollected.Value) = 3, DATEADD("d",-8,Format(Today(),"yyyy-MM-dd")),
IIF(
Weekday(Fields!DateCollected.Value) = 4, DATEADD("d",-9,Format(Today(),"yyyy-MM-dd")),
IIF(
Weekday(Fields!DateCollected.Value) = 5, DATEADD("d",-10,Format(Today(),"yyyy-MM-dd")),
IIF(
Weekday(Fields!DateCollected.Value) = 6, DATEADD("d",-11,Format(Today(),"yyyy-MM-dd")),
IIF(
Weekday(Fields!DateCollected.Value) = 7, DATEADD("d",-12,Format(Today(),"yyyy-MM-dd")),Format(Today(), "yyyy-MM-dd")
)
)
)
)
)
)
)
#To Get The Saturday @ 00:00:00
=IIF(
Weekday(Fields!DateCollected.Value) = 1, DATEADD("d",-1,Format(Today(),"yyyy-MM-dd")),
IIF(
Weekday(Fields!DateCollected.Value) = 2, DATEADD("d",-2,Format(Today(),"yyyy-MM-dd")),
IIF(
Weekday(Fields!DateCollected.Value) = 3, DATEADD("d",-3,Format(Today(),"yyyy-MM-dd")),
IIF(
Weekday(Fields!DateCollected.Value) = 4, DATEADD("d",-4,Format(Today(),"yyyy-MM-dd")),
IIF(
Weekday(Fields!DateCollected.Value) = 5, DATEADD("d",-5,Format(Today(),"yyyy-MM-dd")),
IIF(
Weekday(Fields!DateCollected.Value) = 6, DATEADD("d",-6,Format(Today(),"yyyy-MM-dd")),
IIF(
Weekday(Fields!DateCollected.Value) = 7, DATEADD("d",-7,Format(Today(),"yyyy-MM-dd")),Format(Today(), "yyyy-MM-dd")
)
)
)
)
)
)
)
or if you use two data sets to get the data for weekly and monthly you could bulid something like this into the T-SQL, where you pass startdate and enddate into the where clause in a between statement, eg. WHERE DateColumn BETWEEN @StartDate AND @EndDate
DECLARE @StartDate DATE, @EndDate DATE
IF DATEPART(DW,GETDATE()) = 1 --Sunday
BEGIN
SET @StartDate = DATEADD(DAY,-6, GETDATE())
END
ELSE IF DATEPART(DW,GETDATE()) = 2 --Monday
BEGIN
SET @StartDate = DATEADD(DAY,-7, GETDATE())
END
ELSE IF DATEPART(DW,GETDATE()) = 3 --Tuesday
BEGIN
SET @StartDate = DATEADD(DAY,-8, GETDATE())
END
ELSE IF DATEPART(DW,GETDATE()) = 4 --Wednesday
BEGIN
SET @StartDate = DATEADD(DAY,-9, GETDATE())
END
ELSE IF DATEPART(DW,GETDATE()) = 5 --Thursday
BEGIN
SET @StartDate = DATEADD(DAY,-10, GETDATE())
END
ELSE IF DATEPART(DW,GETDATE()) = 6 --Friday
BEGIN
SET @StartDate = DATEADD(DAY,-11, GETDATE())
END
ELSE IF DATEPART(DW,GETDATE()) = 7 --Saturday
BEGIN
SET @StartDate = DATEADD(DAY,-12, GETDATE())
END
SET @EndDate = DATEADD(DAY,5,@StartDate)
SELECT @StartDate, @EndDate
The same will work for the monthly as well but you will need to modify them to work with that, but the basis is the same
March 22, 2012 at 4:17 am
Anthony
This is brilliant, thanks so much for all your help - again!
Have a great day!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply