SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Best Practice


Best Practice

Author
Message
mitzyturbo
mitzyturbo
SSC Veteran
SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)

Group: General Forum Members
Points: 249 Visits: 579
Hi all

Just want to run a best practice question by some of you. I've been toying around with designing a report, it would be a report on total calls and response times per month (by year) as well as the average number of calls and average rep time per year. This is where I had some problems
So initially I went for the option to do it by a table, with some totals added and then tried to go with a Matrix.

The Matrix would appear to be the better option but it proves to be quite inflexible when it comes to creating values from a dataset i.e. avg(calls) or total avg(calls).

Using either option I found that I still had to go down the road of prepping the data in the queries instead of the values being generated at report run time.

Would anyone have any sample reports of working out averages, complex grouping etc? Is it best practice with SSRS 2008 to do most of the calculations / aggregation in the dataset query itself?
gsc_dba
gsc_dba
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1766 Visits: 2020
I don't like to use the term "it depends" but in my experience the more you calculate up front (in the data source/set) less goes wrong.

With that said;
are you using SSAS?,
do these measures (metrics) already exist in the database?

If not, then consider creating views or stored procedures (or both) that calculate the averages you are looking for the report to display.

If you go down the route of letting SSRS do the math - which it is perfectly capable of doing ;-) then matrices are the way to go.
Here are some articles to get you started:
http://msdn.microsoft.com/en-us/library/cc627441(v=sql.105).aspx
https://www.simple-talk.com/sql/reporting-services/advanced-matrix-reporting-techniques/
http://www.sqlservercentral.com/articles/Stairway+Series/72649/

_____________________________________________________________________________________
[font=Courier New]gsc_dba[/font]
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search