Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2010-08-25
6
Medium Priority
?
774 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 93

Accepted Solution

by:
Patrick Matthews earned 500 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

Question has a verified solution.

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

It is helpful to note: This is a cosmetic update and is not required, but should help your reports look better for your boss.  This issue has manifested itself in SSRS version 3.0 is where I have seen this behavior in.  And this behavior is only see…
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…
Integration Management Part 2
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

564 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