Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 777
  • Last Modified:

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

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
pstather
Asked:
pstather
1 Solution
 
Patrick MatthewsCommented:
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
 
pstatherAuthor Commented:
Thanks,
I shall have a look at this tomorrow morning, first thing
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
planoczCommented:
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
 
pstatherAuthor Commented:
This worked wonderfully.
Thanks
0
 
pstatherAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now