February 16, 2012 at 1:03 am
Hi,
Right now I am generating weekly reports & I consider first 7 days of month as 1st week and so on. My code for this is :
=((DATEPART(DateInterval.Day, (Fields!InvoiceDate.Value))-1) \ 7) + 1
Now for example February has 4 days in the first week. My report should consider Sunday to Saturday as week rather than dividing the number of days in a month by 7. How can I do this?
Regards,
Nithin
February 16, 2012 at 4:08 am
This was removed by the editor as SPAM
February 17, 2012 at 5:54 am
Hi Stewart,
Can you please show How I can use the Weekday() in the Expression. Tried various options . I am completely lost.
=DATEPART(DateInterval.Weekday, (Fields!InvoiceDate.Value))
Stewart "Arturius" Campbell (2/16/2012)
Consider using either> WeekdayName() (returns the day name (e.g. Sunday, Monday etc)
> Weekday() (returns the day of the week, e.g. 1,2,3 - this value depends ofn the DATEFIRST setting)
February 17, 2012 at 6:39 am
select DATEPART(weekday, GETDATE())
February 17, 2012 at 6:59 am
Hi,
Let me make clear what my requirement is. I am generating a Weekly report on SSRS 2008 and in the report as shown in the excel file I have to retrieve count for each client for each week of the month. The filters provided in my report are Year, Month & Client. As of now the date grouping Expression for weekly report is :
=((DATEPART(DateInterval.Day, (Fields!InvoiceDate.Value))-1) \ 7) + 1
This code considers 1st 7 days of every month as 1st week and so on.
What I require is as per the calender. For example For the month of Feb 2012 the dates 1,2,3 & 4 fall in the first week. So the count for first week should be for the 4 days rather than 1-7 as it is now in my report.
I am confused what my grouping expression should be.
I hope I am clear now.
Eugene Elutin (2/17/2012)
select DATEPART(weekday, GETDATE())
February 17, 2012 at 7:43 am
I see now.
Ok my advise is:
If you want to achieve the best performance and make your reports neat, use a calendar table (in the datawarehouse world it usually called date dimension).
Create a table which will contain required attributes of each date. In your query you will be able to join to this table and return any date attribute you want, already precalculated. then you will be able to use it for grouping.
Here is small example of setup with temp table for just one year:
-- setup for this year...
DECLARE @dt datetime = '20120101'
select top 366 dateadd(DAY,ROW_NUMBER() over (order by s.name)-1, @dt) dt
into #calendartable
from sys.columns s
select dt
,DATEPART(week, dt) as week_of_year
,DENSE_RANK()
OVER (PARTITION BY MONTH(dt)
ORDER BY (DATEPART(week, dt))) as week_of_month
from #calendartable
You will be actually better off with having permanent table which you can prepopulate for as many years you want. Having index on the date will help in performance and you can have as many pre-calculated qttributes as you like.
Please note: The week number calculation depends of which day of the week is the first one. You can control it with SET DATEFIRST.
Calculating the week of month in report will not be as clear as the above method and most likely will not perform well...
You can calulate week of month in T-SQL using another way:
DATEDIFF(week, DATEADD(MONTH, DATEDIFF(MONTH, 0, [date]), 0), [date]) +1
Just add it into your query as another column to return and then use it for grouping in SSRS
February 20, 2012 at 2:18 am
Hi,
Your expression works in SSMS:
select DATEDIFF("WEEK", CONVERT(DATETIME,DATEADD("MONTH", DATEDIFF("MONTH", 0, GETDATE()), 0)), GETDATE()) +1
But when I try it in SSRS :
=DATEDIFF("WEEK", CDATE(DATEADD("MONTH", DATEDIFF("MONTH", 0, Fields!EnquiryDate.Value), 0)), Fields!EnquiryDate.Value) +1
I get the following error shown in the attachment. I have converted the Date1 value using CDATE. Still i get the error. What am i doing wrong here?
Eugene Elutin (2/17/2012)
I see now.Ok my advise is:
If you want to achieve the best performance and make your reports neat, use a calendar table (in the datawarehouse world it usually called date dimension).
Create a table which will contain required attributes of each date. In your query you will be able to join to this table and return any date attribute you want, already precalculated. then you will be able to use it for grouping.
Here is small example of setup with temp table for just one year:
-- setup for this year...
DECLARE @dt datetime = '20120101'
select top 366 dateadd(DAY,ROW_NUMBER() over (order by s.name)-1, @dt) dt
into #calendartable
from sys.columns s
select dt
,DATEPART(week, dt) as week_of_year
,DENSE_RANK()
OVER (PARTITION BY MONTH(dt)
ORDER BY (DATEPART(week, dt))) as week_of_month
from #calendartable
You will be actually better off with having permanent table which you can prepopulate for as many years you want. Having index on the date will help in performance and you can have as many pre-calculated qttributes as you like.
Please note: The week number calculation depends of which day of the week is the first one. You can control it with SET DATEFIRST.
Calculating the week of month in report will not be as clear as the above method and most likely will not perform well...
You can calulate week of month in T-SQL using another way:
DATEDIFF(week, DATEADD(MONTH, DATEDIFF(MONTH, 0, [date]), 0), [date]) +1
Just add it into your query as another column to return and then use it for grouping in SSRS
February 21, 2012 at 8:20 am
I tried replacing the 0 with '1900/01/01',
=DATEDIFF("WEEK", CDATE(DATEADD("MONTH", DATEDIFF("MONTH","1900/01/01", Fields!EnquiryDate.Value),"1900/01/01")), Fields!EnquiryDate.Value) +1
I get the following error:
The Group expression for the grouping 'GroupName1' contains error: Argument 'Interval' is not a valid value.
February 21, 2012 at 8:49 am
Why do you resist to adding this column to the query itself?
February 22, 2012 at 2:31 am
Hi Eugene,
Thanks a looooooooooooooooooooooooot !!!!!!!!! for the timely help. Sorry for not implementing your idea of adding the column to query. I failed to understand that part. Thanks again for correcting me.:-):-):-):-):-):-):-)
Eugene Elutin (2/21/2012)
Why do you resist to adding this column to the query itself?
February 22, 2012 at 7:03 am
Happy to help!
As I've advised in the previous post, it is a good idea to have a table of all (reasonable) dates (usually called date dimension in datawarehouses) in the database which can be used for reporting purposes. It can contain all required date pre-calculated attributes such as week, month, day, day of the week, day of the year, fin. year, fin week, etc. You can index this table to achieve much better performance of many reporting queries which require some date manipulations including grouping,...
February 22, 2012 at 8:16 am
Thanks a lot Eugene for your valuable feedback. Will create a table for all dates as advised.:-):-)
Eugene Elutin (2/22/2012)
Happy to help!As I've advised in the previous post, it is a good idea to have a table of all (reasonable) dates (usually called date dimension in datawarehouses) in the database which can be used for reporting purposes. It can contain all required date pre-calculated attributes such as week, month, day, day of the week, day of the year, fin. year, fin week, etc. You can index this table to achieve much better performance of many reporting queries which require some date manipulations including grouping,...
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply