Best Practice

• mitzyturbo

  Hall of Fame

  Points: 3452

  More actions

  April 9, 2013 at 3:00 am

  #394545

  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

  SSCertifiable

  Points: 5407

  More actions

  April 12, 2013 at 7:29 am

  #1605953

  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