Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
kira
Starting Member
17 Posts |
 Posted - 2009-05-15 : 17:42:11
|
| I need to make a report, which in the end, needs to calculate how many times one columns value changes. For example, the table should looks like this:----------------------year grade----------------------1990 A 1991 B1992 B1993 A1994 A1995 D1996 D1997 C---------------------grade changing times: 5 times ---------------------(note: including the first year's grade)how to get this 5 times inside a SSRS? SSRS doesn't allow me to do a calculation as SUM(IIF(Fields!Grade.value = Previous(Fields!Grade.value), 1, 0)and because the stored procedure to get the dataset is very complicated, I don't want to do a self joining in stored procedure either.how should I deal with the problem? Any help will be very appreciated, |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2009-05-15 : 17:52:13
|
| Take a look here:http://www.sqlteam.com/article/detecting-runs-or-streaks-in-your-data |
 |
|
|
kira
Starting Member
17 Posts |
Posted - 2009-05-15 : 17:59:06
|
| Thank u robvolk,so there is no easy way to do it in SSRS? CrystalReports seems to have a much better support to the aggregation than SSRS. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2009-05-15 : 18:12:55
|
| I don't know if it's possible in SSRS itself, but if you can modify your source query I'm sure you can make it work. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-05-16 : 03:30:34
|
| you need to write a custom function in SSRS if you want this calculation to be done in report. there's no standard way to do this in ssrs at least till sql 2005 |
|
|
|
kira
Starting Member
17 Posts |
Posted - 2009-05-19 : 11:32:40
|
| thank you visakh16! Your advise is very helpful. I guess I need to get some basic knowledge of VB first. :-) |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-05-19 : 11:37:18
|
| i would have done this calculation in sql and brought the measure in report |
|
|
|
kira
Starting Member
17 Posts |
Posted - 2009-05-19 : 16:49:05
|
| Thank you visakh16, and finally I solved the problem in my query (my supervisor hates queries which are complicated than the basic one and he might be mad with my stored procedures once he saw my work today. :-P) Another question: when writing custom function and you call it every row, does this slow the performance much or not? I am not familiar with either VB or custom functions, but I think VB might be a little bit slow compare to other programming languages?Thanks. |
|
|
|
|
|
|
|
|
How to get aggregation of a "Previous" func?