Weekly Report in SSRS 2008 - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Weekly Report in SSRS 2008

Author

Topic

rajan.nithin
Starting Member

42 Posts

Posted - 2012-02-16 : 07:24:57

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?

My report should be something like:
Client_____week1_____week2_____week3_____week4
Client1______0_________2_________7_________15
Client2______10________25________14________14

Regards,
Nithin

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-17 : 13:33:22

this may be?


=(((DATEPART(DateInterval.Day, (Fields!InvoiceDate.Value))-1) \ 7) * 7) -2

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rajan.nithin
Starting Member

42 Posts

Posted - 2012-02-21 : 02:35:47

Hi Visakh,

Thanks for the reply. Still I get the same output.

Let me make clear what my requirement is. I am generating a Weekly report on SSRS 2008 and in the report, 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.

As per my query the number of weeks are 3 till 21 Feb (21/7). But as per calendar for Feb the number of weeks should be 4 till date:

1-4 - 1st week
5-11 - 2nd week
12-18 - 3rd week
19-25 - 4th week
26-29 - 5th week

The following query works on SSMS .

select DATEDIFF("WEEK", (DATEADD("MONTH", DATEDIFF("MONTH",0, GETDATE()), 0)), GETDATE()) +1

but on SSRS when I modify accordingly like:

=DATEDIFF("WEEK", CDATE(DATEADD("MONTH", DATEDIFF("MONTH",0, Fields!EnquiryDate.Value), 0)), Fields!EnquiryDate.Value) +1

I get the following error:

The Group expression for the grouping 'GroupName1' contains error: Argument 'Date1' cannot be converted to type 'Date'.

What am i doing wrong here?

[quote]Originally posted by visakh16

this may be?


=(((DATEPART(DateInterval.Day, (Fields!InvoiceDate.Value))-1) \ 7) * 7) -2

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rajan.nithin
Starting Member

42 Posts

Posted - 2012-02-21 : 10:19:18

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.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-21 : 13:43:32

=DATEDIFF("w", CDATE(DATEADD("MONTH", DATEDIFF("m","1900/01/01", Fields!EnquiryDate.Value),"1900/01/01")), Fields!EnquiryDate.Value) +1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rajan.nithin
Starting Member

42 Posts

Posted - 2012-02-22 : 04:06:22

Unfortunately I again get the old result. I again get 3 weeks for Feb rather than 4 weeks.

If I use the same query in SSMS as shown below the output is 22.

select DATEDIFF("W", (DATEADD("M", DATEDIFF("M",'1900/01/01', GETDATE()),'1900/01/01')), GETDATE()) +1

If I change W to WEEK in SSMS as shown below the output is 4.

select DATEDIFF("W", (DATEADD("M", DATEDIFF("M",'1900/01/01', GETDATE()),'1900/01/01')), GETDATE()) +1

quote:
Originally posted by visakh16

=DATEDIFF("WEEK", CDATE(DATEADD("MONTH", DATEDIFF("m","1900/01/01", Fields!EnquiryDate.Value),"1900/01/01")), Fields!EnquiryDate.Value) +1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rajan.nithin
Starting Member

42 Posts

Posted - 2012-02-22 : 04:38:07

At last I was able to resolve the issue with the help of a friend.

I added

DATEDIFF("WEEK", (DATEADD("M", DATEDIFF("M",'1900/01/01', EnquiryDate),'1900/01/01')), EnquiryDate) +1

as a column into my query and used the column for grouping in ssrs .

Thanks a looooooooooooot !!!!! Visakh for spending your valuable time to help me out.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources