Solved

How do I group by every day in the month for SSRS 2005

Posted on 2010-08-25
6
764 Views
Last Modified: 2012-06-27
Hello,
I am trying to create a report so that on the left hand side, it shows me sites and job types.
Along the top, it needs to show me every day of a particular month.
When a site has a job on a particular date, a 1 is in the relevant place.

I can get a matrix to display the dates which have been selected in the database but I need every day of the month.

Could anyone please help.
Thanks
0
Comment
Question by:pstather
6 Comments
 
LVL 16

Expert Comment

by:vdr1620
ID: 33523336
0
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 125 total points
ID: 33523527
A typical solution to this sort of problem involves using a "calendar" table that is prepopulated with all of the dates you would be interested in, and then linking to that table with an outer join in your SQL statement.
0
 

Author Comment

by:pstather
ID: 33523560
Thanks,
I shall have a look at this tomorrow morning, first thing
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 27

Expert Comment

by:planocz
ID: 33536437
You could also change to a standard table (not Matrix) and setup  your detail row to show all the days of the month per job site and job types by grouping the job site.
0
 

Author Closing Comment

by:pstather
ID: 33536456
This worked wonderfully.
Thanks
0
 

Author Comment

by:pstather
ID: 33565418
The way I did this,
I created a table in SQL with an int. field
I then created a variable in variable in my report query called start day and end day
The start day is 1 and the end day is set through an if statement.
It is either 28, 29, 30 or 31 depending on the month the report is running for.
The date is then set using convert(month, numbefromsql, year).
I used an outer join to match the days together with my data which left me with all the days of the month.
Thanks for your help guys
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
crm 2010 on premise 5 62
How to import SQL 2000 database to SQL 2014 5 100
SSRS - Generating separate reports for each department 2 57
SSRS troubles 4 21
A recent questions about how to add SSRS named instances, couldn't find any that talks about SQL server 2008, anyway I decided to help by creating some screen shots. The installation is straightforward, you just pop the SQL server 2008 installati…
On Sep 22nd 2014 Microsoft released Update Rollup 1 for Microsoft Dynamics CRM 2013 Service Pack 1 and back in July Update Rollup 3 was released.  So we now have:   Update Rollup 1Update Rollup 2Update Rollup 3Service Pack 1Update Rollup 1 for S…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now