August 20, 2020 at 12:41 pm
I have a parameter in my report to put the sunday date in of the current week =DATEADD("d" ,7-DATEPART(DateInterval.WeekDay,Today(),FirstDayOfWeek.Monday),Today()) so for this week would put the date as 23/08/2020
I need to set a start date that puts in the Monday Date from 11 weeks ago based on the end of week date above so using the example 08/06/2020
I have tried a number of combinations and the best I can get to is =DATEADD(DateInterval.WeekOfYear, -11, Today()) but that sets the date exactly 11 weeks ago from today so I am not getting the correct date obviously. I am probably missing something very simple using the expression to get the last day of the current week to calculate back to the monday 11 weeks prior but I cant see it. Is there any way of putting an expression to calculate this?
Thanks
Chris
August 20, 2020 at 2:22 pm
SELECT StartDate = DATEADD(wk,-11,DATEADD(dd,DATEDIFF(dd,0,GETDATE())/7*7,0)) --Monday, 11 Weeks ago
,EndDate = DATEADD(wk, 1,DATEADD(dd,DATEDIFF(dd,6,GETDATE())/7*7,6)) --This coming Sunday
;
The "0" and the "6" are known as DATE SERIAL NUMBERS and represent the number of days since the SQL Server Base Date of 01 Jan 1900.
"0" is the first of January 1900, which is a Monday.
"6" is the first Sunday in 1900.
"/7*7" is an integer math method of rounding down to the day evenly divisible by 7... in other words, weeks that start with the reference day.
These types of temporal calculations are fundamental and essential to high performance reporting. I recommend doing a deep dive on how they work and some of the incredible things you can do with such knowledge of temporal manipulation.
Calendar tables have their uses but, if someone suggests a Calendar table for this, I recommend you ignore them because it will be slower and more read intensive than the formula above and certainly more complex to maintain.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 20, 2020 at 9:28 pm
@JeffModen - although your code works in SQL Server, it will not work in an expression in an SSRS report.
Although you can figure out how to use the same type of code using the DATEADD/DATEDIFF functions - it is actually easier to use .NET code instead.
For the end date parameter - to get the Sunday for the current week: =Today.AddDays(-Today.DayOfWeek) - this returns 2020-08-16 for today (2020-08-20). For next Sunday you can use: =Today.AddDays(7 - Today.DayOfWeek) or =Today.AddDays(-Today.DayOfWeek).AddDays(7)
To get the Monday 11 weeks ago - we can then take one of the above and subtract the appropriate number of days. For example: =Today.AddDays(-Today.DayOfWeek).AddDays(-69) or =Today.AddDays(7 - Today.DayOfWeek).AddDays(-76).
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 20, 2020 at 10:07 pm
@JeffModen - although your code works in SQL Server, it will not work in an expression in an SSRS report.
Missed the fact that this was an SSRS forum. Thanks for the cover.
As a bit of a sidebar (and it's just me), I'd still be tempted to write the code as a stored procedure and let SSRS consume that.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 20, 2020 at 10:27 pm
Jeffrey Williams wrote:@JeffModen - although your code works in SQL Server, it will not work in an expression in an SSRS report.
Missed the fact that this was an SSRS forum. Thanks for the cover.
As a bit of a sidebar (and it's just me), I'd still be tempted to write the code as a stored procedure and let SSRS consume that.
Yes - you can create a dataset that returns the two columns and use those as the source for the default values of the parameters. I generally don't do that for a default values - rather I will do that for a list of available values - but that is just a preference.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 20, 2020 at 10:33 pm
Jeff Moden wrote:Jeffrey Williams wrote:@JeffModen - although your code works in SQL Server, it will not work in an expression in an SSRS report.
Missed the fact that this was an SSRS forum. Thanks for the cover.
As a bit of a sidebar (and it's just me), I'd still be tempted to write the code as a stored procedure and let SSRS consume that.
Yes - you can create a dataset that returns the two columns and use those as the source for the default values of the parameters. I generally don't do that for a default values - rather I will do that for a list of available values - but that is just a preference.
Not quite what I was talking about. I'd write a proc to return the correct result set and let SSRS consume the result set... not just the result of two formulas.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 21, 2020 at 9:25 am
Jeffrey Williams wrote:@JeffModen - although your code works in SQL Server, it will not work in an expression in an SSRS report.
Missed the fact that this was an SSRS forum. Thanks for the cover.
As a bit of a sidebar (and it's just me), I'd still be tempted to write the code as a stored procedure and let SSRS consume that.
About a third of my duties are SSRS, I am lucky in that I have full and unfettered access to all the DBs I need to report from.
I tend to try to do most of the "work" in the query or sProc, SSRS functions just seem bit "limited" or overly verbose in comparison, for certain tasks. Usually far easier to get the dataset looking right in SSMS I've found. Of course - It Depends.
I do however sometimes have to kick myself when I am an hour down a rabbit hole of writing complex groupings and windowing functions, then have a stern word with myself after the penny drops that it would be a few clicks to achieve what I want on the report side 😀
"Knowledge is of two kinds. We know a subject ourselves, or we know where we can find information upon it. When we enquire into any subject, the first thing we have to do is to know what books have treated of it. This leads us to look at catalogues, and at the backs of books in libraries."
— Samuel Johnson
I wonder, would the great Samuel Johnson have replaced that with "GIYF" now?
August 21, 2020 at 9:32 am
Thank you Jeff & Jeffrey for your helpful replies. I have sorted it now
Sadly my skills to SQL are not great so I find it easier to do these queries at SSRS level as that seems to be more in line with my excel skills, just sometimes I get tripped up like above when I can't write a formula in SSRS as I would in excel.
It's not the first time that I have been told that it is better to do these queries at SQL DB level, may have to get myself on a course at some point but up to now I have managed to put together reports ok with the help of everyone in here to point me in the right direction when I get stuck
August 21, 2020 at 9:41 am
Thank you Jeff & Jeffrey for your helpful replies. I have sorted it now
Sadly my skills to SQL are not great so I find it easier to do these queries at SSRS level as that seems to be more in line with my excel skills, just sometimes I get tripped up like above when I can't write a formula in SSRS as I would in excel.
It's not the first time that I have been told that it is better to do these queries at SQL DB level, may have to get myself on a course at some point but up to now I have managed to put together reports ok with the help of everyone in here to point me in the right direction when I get stuck
You would find brushing up on the SQL side extremely useful, I am sure. Your users might thank you too - you might find your reports start to run immeasurably faster if you can optimise the datasets at source.
Do you have any reports that take more than few seconds to run? Maybe a massive dataset, filtered in the report? Could be a really good personal development exercise to pick one and create a new version doing the heavy lifting SQL-side.
I've had a lot of people - consultants, suppliers, forum members (not here) etc be quite scathing about SSRS, saying it's really slow, dreadful, blah blah. I am the opposite - it's a fantastic tool and performs very well if you do the right work in the right place.
"Knowledge is of two kinds. We know a subject ourselves, or we know where we can find information upon it. When we enquire into any subject, the first thing we have to do is to know what books have treated of it. This leads us to look at catalogues, and at the backs of books in libraries."
— Samuel Johnson
I wonder, would the great Samuel Johnson have replaced that with "GIYF" now?
August 21, 2020 at 9:52 am
cmw 66135 wrote:Thank you Jeff & Jeffrey for your helpful replies. I have sorted it now
Sadly my skills to SQL are not great so I find it easier to do these queries at SSRS level as that seems to be more in line with my excel skills, just sometimes I get tripped up like above when I can't write a formula in SSRS as I would in excel.
It's not the first time that I have been told that it is better to do these queries at SQL DB level, may have to get myself on a course at some point but up to now I have managed to put together reports ok with the help of everyone in here to point me in the right direction when I get stuck
You would find brushing up on the SQL side extremely useful, I am sure. Your users might thank you too - you might find your reports start to run immeasurably faster if you can optimise the datasets at source.
Do you have any reports that take more than few seconds to run? Maybe a massive dataset, filtered in the report? Could be a really good personal development exercise to pick one and create a new version doing the heavy lifting SQL-side.
I've had a lot of people - consultants, suppliers, forum members (not here) etc be quite scathing about SSRS, saying it's really slow, dreadful, blah blah. I am the opposite - it's a fantastic tool and performs very well if you do the right work in the right place.
I like what I can do in SSRS so have no complaints personally. I do find it slow on bigger reports so will get some SQL training at some point. I see merits on both but If I can improve on what I have already done then all the better.
Thank you for the advice David
August 21, 2020 at 10:42 am
Personally, I'm a bit sad (and also kind of excited I guess) that the days of SSRS are numbered for me - got to start from scratch and learn PowerBI now - seems a bit dumbed-down compared to SSRS, but shinier. I might be wrong of course....
"Knowledge is of two kinds. We know a subject ourselves, or we know where we can find information upon it. When we enquire into any subject, the first thing we have to do is to know what books have treated of it. This leads us to look at catalogues, and at the backs of books in libraries."
— Samuel Johnson
I wonder, would the great Samuel Johnson have replaced that with "GIYF" now?
August 21, 2020 at 3:22 pm
Jeffrey Williams wrote:Jeff Moden wrote:Jeffrey Williams wrote:@JeffModen - although your code works in SQL Server, it will not work in an expression in an SSRS report.
Missed the fact that this was an SSRS forum. Thanks for the cover.
As a bit of a sidebar (and it's just me), I'd still be tempted to write the code as a stored procedure and let SSRS consume that.
Yes - you can create a dataset that returns the two columns and use those as the source for the default values of the parameters. I generally don't do that for a default values - rather I will do that for a list of available values - but that is just a preference.
Not quite what I was talking about. I'd write a proc to return the correct result set and let SSRS consume the result set... not just the result of two formulas.
The problem with this approach is that you no longer have a dynamic report where the user can select the date range. You have a fixed report for a specified time frame and if you need a different time frame - do you create a new procedure and new report?
Setting up default values for the start/end dates allows the report to execute based on the defaults - but then if the user wants to run for a different date range they can modify those values.
The downside to this approach is that a user could select a date range that could cause the report to take too long to execute or returns too much data, but that is an easy check to put in a procedure if/when it becomes an issue.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 22, 2020 at 1:42 am
The problem with this approach is that you no longer have a dynamic report where the user can select the date range. You have a fixed report for a specified time frame and if you need a different time frame - do you create a new procedure and new report?
Setting up default values for the start/end dates allows the report to execute based on the defaults - but then if the user wants to run for a different date range they can modify those values.
The downside to this approach is that a user could select a date range that could cause the report to take too long to execute or returns too much data, but that is an easy check to put in a procedure if/when it becomes an issue.
The stored proc could be written for the best of both worlds... no parameters, fixed 11 week look back. Parameters filled, then check them for a reasonable time frame and either alert the operator that they've asked for something ridiculous and run the report code after that check has cleared.
No matter what, though, I'm really glad that my primary job isn't writing reports for users... it's a bad as what the poor front end developers (I used to be one a long time back) go through with color, location of objects on the screen, etc, etc, ad infinitum. 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply