October 20, 2009 at 9:41 am
Hopefully some can help a rather confused beginner; I’ve just started using SSRS on a Sql 2005 box.
The report I’m trying to generate I thought was quite simple but I seem to be struggling.
I have a flat table that consist of three columns, ID, EnteredOnDate and LTypeID. Now I would like to return the results with LTypeID on the left hand and enteredOndate across the top and the ID count value in the middle.
But I really need the month of the date across the top, going horizontally.
Any ideas?
Regards lemondash
October 20, 2009 at 2:18 pm
in your query instead of bringing back the entire EnteredOnDate use the month() and year() functions to get you what you need either in addition to the date or instead of...
In other words if your query looks like
Select ID, EnteredOnDate, LTypeID
FROM myTable
Add the following
Select ID, EnteredOnDate, LTypeID,
DATENAME(month, EnteredOnDate) as EnteredMonth,
DATENAME(year, EnteredOnDate) as EnteredYear
FROM myTable
Or you could concat them together as well...
Hope that helps,
-Luke.
October 20, 2009 at 3:23 pm
Hi,
Can you be more clear....
As far as my understanding you can use a matrix instead of a table.
October 20, 2009 at 3:29 pm
Sorry if i'm not clear enough.
I tried to use a matrix, have the typeid at the side and the month part at the top. so that ti could have the count value in between but it just don't work for me.
October 20, 2009 at 6:56 pm
Any chance you could post a screen shot or perhaps your rdl? Can you be a bit more specific about what did not work for you? Did it not produce the numbers you were expecting or was there a different problem?
Thanks,
-Luke.
October 21, 2009 at 12:20 pm
As per your message to me it seems like you have gotten your counting by the month to work, and now you'd like to aggregate by year then month and have the month able to expand when needed.
What you'd need to do is add another Column group. Make one for month and one for year.
The year one should group by YEar and the Month one should group by Month and Year.
Right click on the month group and choose Edit Group. Click the visibility tab and check Visibility can be toggled by another report item Check box. Then select your Year coumn.
I think that should get you what you are looking for...
since it seems you are rather new to SSRS, I'd suggest a decent book along the lines of Sql Server Reporting Service 2005 Step by Step from the Microsoft Press. At the very least, I'd check out some of the articles in the SSRS section over at simple-talk.com[/url]. They have some very good introductory to semi advanced things postedover there that may help you on your way.
-Luke.
October 21, 2009 at 1:14 pm
Cheers i give that a go. By the way still using matrix ?
Lee
October 21, 2009 at 1:37 pm
Yeah I probably would still use a matrix, although it could prove to be somewhat hard to handle since once you click on a year and the months would pop out, you could have a very long(horizontally speaking) report if you have a couple of years worth of data you are looking at.
Since you are only tracking one data point depending on the amount of data your are reporting on you may find that a line chart may work a bit better, but it's really all about how your users want to see the data.
-Luke.
October 21, 2009 at 1:41 pm
Ok. You are a star.
Mayber one more thing is that the best book to get ?
If so you know a link i can get it from tonight so i can start throught it ?
October 21, 2009 at 1:50 pm
I don't know if that's the best book or not, it's the only one I've paged through. There were some issues with the sample databases that a lot of people had issues with, but with a bit of common sense I was able to work through them. If you really are looking for a book recommendation I'd suggest starting a new thread asking for recommendations and places to get started with SSRS. the Simple Talk article as well as various articles I've read on this site about SSRS are pretty good, and free which is great if you don't have a training budget.
Beyond that I think I remember hearing that Brian LArsen's books on the subject were pretty good, as well as the Professional SQL Server Reporting Services books.
I'd also check Amazon and search for SQL Server Reporting Services in the books section and see what some of the other reviewers had to say.
-Luke
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply