Monthly,Weekly & Yearly Report in SSRS 2008

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

SQL Server 2008 Forums

Analysis Server and Reporting Services (2008)

Monthly,Weekly & Yearly Report in SSRS 2008

Author

Topic

rajan.nithin
Starting Member

42 Posts

Posted - 2011-08-04 : 03:47:15

Hi,

Let me explain the scenario. My Boss wants me to generate reports for my clients. ie, I want to generate monthly, weekly & yearly reports for each client.
In the case of monthly report if I choose the parameter month then I need to generate the total for each client for every day of the particular month. If I choose the parameters month July & year 2011. I need the monthly report for july as follows.

Days__ 1 2 3 4 5 6 7 ............31
---------------------------------
CLIENT1 5 3 4 9 4 5 8 ............47
CLIENT2 4 2 4 5 7 0 0 ............6
CLIENT3 1 2 0 0 4 1 6 ............45
.....
.....
.....
.....
.....
CLIENTn 2 1 4 4 1 0 4 ............14

Similarly I need to generate a weekly report when i select a date range i need to generate report for each week between the date range. & in the yearly report i need to generate total for each month of the year for each client.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-04 : 09:10:45

Depending on your requirement you need to apply grouping on Client and also on date field by taking any one of year, month or day values uing date functions like YEAR(),MONTH() and DAY(). for week you can use DATEPART(wk,yourdatefield) and group based on it

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

rajan.nithin
Starting Member

42 Posts

Posted - 2011-08-05 : 05:44:51

Thanks for the help provided. Since I am new to Reporting Services , i am finding it difficult to design such a report in SSRS 2008.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-08 : 04:43:42

whats the difficulty faced in design? what column and row group are you applying?

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

rajan.nithin
Starting Member

42 Posts

Posted - 2011-08-09 : 03:39:56

Hi,

As I said I am new to SSRS 2008. I have no idea as to how to start of with this report. Is there any good documentation with which I can get started to generate such reports. I have been searching for around a month to generate such reports. Can U help me out ?????

quote:
Originally posted by visakh16

whats the difficulty faced in design? what column and row group are you applying?

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-09 : 10:10:01

see
http://msdn.microsoft.com/en-us/library/cc627441.aspx

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

rajan.nithin
Starting Member

42 Posts

Posted - 2011-08-10 : 07:36:42

Thanks a looooooooot Visakh for the help provided. I was able to generate reports for the scenarios daily & monthly, but not for each week of the month. For example if July has 5 weeks I need to populate data for each week.

quote:
Originally posted by visakh16

see
http://msdn.microsoft.com/en-us/library/cc627441.aspx

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

rajan.nithin
Starting Member

42 Posts

Posted - 2011-08-10 : 08:06:49

SSRS is not supporting when i include the following in the value field to get the week of the month
=((DATEPART(DAY, '2011/07/31')-1) / 7) + 1

when i execute the same in SSMS i get result as 5

SELECT ((DATEPART(DAY, '2011/07/31')-1) / 7) + 1

quote:
Originally posted by rajan.nithin

Thanks a looooooooot Visakh for the help provided. I was able to generate reports for the scenarios daily & monthly, but not for each week of the month. For example if July has 5 weeks I need to populate data for each week.

quote:
Originally posted by visakh16

see
http://msdn.microsoft.com/en-us/library/cc627441.aspx

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-10 : 09:00:11

look into syntax for datepart in ssrs
i think it should be

=((DATEPART(DAY, CDate('2011-07-31'))-1) / 7) + 1

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

rajan.nithin
Starting Member

42 Posts

Posted - 2011-08-19 : 05:43:31

The following code worked for me
=((DATEPART(DateInterval.Day, (Fields!Date.Value))-1) \ 7) + 1

quote:
Originally posted by visakh16

look into syntax for datepart in ssrs
i think it should be

=((DATEPART(DAY, CDate('2011-07-31'))-1) / 7) + 1

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

rajan.nithin
Starting Member

42 Posts

Posted - 2011-08-19 : 05:49:33

I have one more query:

In my Matrix report I want the alternate rows to be colored in different colors for easy readability.

I tried the following code in the background color but it did not work properly. I get two different colors but, it is not for alternate rows. It is haphazard. In my matrix table many fields are blank. Is this the cause ?
=iif(RowNumber(Nothing) Mod 2, "Green", "White")

How can I do this?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-19 : 06:01:44

try

=iif(RowNumber("Yourdatasetname") Mod 2, "Green", "White")

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

rajan.nithin
Starting Member

42 Posts

Posted - 2011-08-19 : 06:32:00

Nope! It does not work.

rajan.nithin
Starting Member

42 Posts

Posted - 2011-08-19 : 06:36:50

quote:
Originally posted by rajan.nithin

Nope! It does not work.

I get this issue only with my matrix reports, else it works fine.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-19 : 07:06:52

is there multiple row groups in matrix?

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

rajan.nithin
Starting Member

42 Posts

Posted - 2011-08-19 : 07:29:45

Yes, I do have multiple row groups.

quote:
Originally posted by visakh16

is there multiple row groups in matrix?

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-19 : 07:35:01

then give inner most rowgroup

=iif(RowNumber("innermostrowgroupname") Mod 2, "Green", "White")

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

rajan.nithin
Starting Member

42 Posts

Posted - 2011-08-19 : 07:54:54

Sorry!! I don't have multiple row groups , whereas i have one row group & 1 column group.

quote:
Originally posted by visakh16

then give inner most rowgroup

=iif(RowNumber("innermostrowgroupname") Mod 2, "Green", "White")

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-19 : 08:09:35

then give that rowgroup name inside

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

rajan.nithin
Starting Member

42 Posts

Posted - 2011-08-19 : 08:21:08

Nope!! I get the same result when I changed to

=iif(RowNumber("GroupName") Mod 2, "Green", "White")

quote:
Originally posted by visakh16

then give that rowgroup name inside

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-20 : 02:01:58

shouldnt it be?

=iif(RowNumber("GroupName") Mod 2 = 0, "Green", "White")

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

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources