Hi there.
I am working on a set of reports where I am summing/averaging data elements based on what period they are in. For example, the report output should look something like this:
For each of the items I am summing, all I have is a datetime of when the event happened. This is a relational database (not a cube), so I am struggling with how to create the 'buckets' based on period. I think the best way is to dynamically create the buckets based on a given date. Is there a way in RS that it can do this bucketing for you?
Thanks, Mike
There are several ways to "create buckets". I recently posted something here http://spacefold.com/lisa/post/Partition-Magic.aspx having discovered some of the SQL 2005 syntax that I never knew existed, which may help you in your explorations of non-cube data -- and there is also a PIVOT clause which is really neat.
When you look at it, it looks as though you can't dynamically figure out how many buckets you have and go for it, but you actually can, if you write a bit of very easy dynamic SQL. I was actually planning on posting about that today, having helped a co-worker do it!
But in RS, you can do this using a matrix layout control, which basically does the thing for you, albeit with (from my POV) some frustrating and counter-intuitive ways of thinking.
Look into the matrix data region first, if you need to display the buckets across, and if you don't like it look into the PIVOT clause to do this in SQL Server (NB: if your data source isn't SQL Server you don't have this T-SQL syntax but there are ways of getting around that if you need to <g>.)
If you need to display the buckets down, I think you have even easier ways to do it. What you seem to be showing (as I read your example table) is a table that has grouping and you've suppressed the detail rows that might ordinarily appear with each line (the dates for each event). OK so far?
The very simplest way to get what you want is to write your query like this:
SELECT MONTH(eventdate) AS M, Datepart(quarter,eventdate) AS Q, YEAR(eventdate) AS Y, eventtype, eventdate from YourEventTable
... now you can summarize by having appropriate groups on the first three calculated values that you see in this query.
The wrinkle that most people have when doing this particular thing is that they actually want the fiscal month, the fiscal quarter, and the fiscal year rather than the base values that you see here. So you generally want to write a couple of UDFs that pass in your first month of fiscal year to handle this properly. These can be a PITA but once you've written them appropriately for your situation, you are usually okay forever. Give a shout if you find you need help with this part. (I may be offline for about a week, but if it is urgent I'm sure many other people can help you with this).
So that's how you do it if your "buckets" are rows, as they seem to be from your example layout. If they are really columns, again, look into matrix and pivot.
>L<
|||Hi Lisa.
Thanks so much for the great response. I was working on doing something similar, but there are a few more wrinkles (aren't there always?). As you mentioned, the buckets are rows, so that is good, but in this case, my columns can vary, so I need to use a matrix control.
1. The data set I need is for the current quarter (based on getdate()) and the previous 5 full quarters. I think this can be easily handled in the where clause, so that should be ok.
2. The matrix control is strange in that when you add row groups, it actually adds them as columns on the report itself (I posted a question on this recently). My customer doesn't want it that way, so I have to get the dataset to match the control - meaning, I will need to have my dataset return the summed/averaged bucket rows and NOT have the control handle the buckets. So, I think I am stuck.
Can you see any way around that?
Thanks, Mike
|||Hi again, Mike,
I can't actually think closely about what you're stuck on here, because (I think I said) I'm leaving on a trip and 'way late on preparing <s>. But, in fact, a PIVOT clause might be just the ticket here -- for this reason among others I did end up posting a blog entry about that. http://spacefold.com/lisa/post/Matrix-Rebuilt-More-non-standard-fun-with-T-SQL.aspx
It's discussing some aspects of the clause that you may or may not be interested it but it will give you some idea of the scope of what it can help you accomplish. In your case -- since you actually know how many buckets there are (6 quarters) -- it may be especially apt.
I'll be back in a week, if you haven't got what you need by then I'll do my best to help <s>. Look forward to reading whatever else has been posted on this thread by then!
>L<
|||Thanks Lisa.
I'll check into it. Enjoy your time 'away'.
- Mike
