Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Best Practice Expand / Collapse
Author
Message
Posted Tuesday, April 9, 2013 3:00 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: 2 days ago @ 8:48 AM
Points: 63, Visits: 360
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?
Post #1440224
Posted Friday, April 12, 2013 7:29 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, December 18, 2014 9:42 AM
Points: 1,309, Visits: 1,573
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/


_____________________________________________________________________________________
gsc_dba
Post #1441689
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse