Jason A. Long (5/22/2015)

---

Jeff Moden (5/22/2015)

---

Jason A. Long (5/22/2015)

---

Lowell (5/19/2015)

---

look at the report design as well; as mentioned, make sure the underlying query/proceudre is effience and tuned of course, but check this out:

i inherited a suite of reports that did Select [lots of columns] from sometable, and then did the grouping in the report.

load a MillionBillion row table into SSRS, and then let it handle the grouping, ouch! it made the report run in hours.

changing the report to do the grouping on the SQL side, and report the results, made it run in seconds, even with the overhead and crappy design of SSRS.

+10

To add to Lowell's response, do all of your filtering in SQL as well. Try to avoid bringing over more data than you intend to display.

The only reason to use a tablix filter is if you you have multiple tablix objects that are displaying subsets of a single, larger data set... In which case, it's better to pull all the required data in a single shot and divvy it up report side.

It depends. If your ultimate goal is to produce a matrix with subtotals, etc, you might be far better off doing the aggregations and sub-totals, etc, on the T-SQL side of the house so that you don't take a performance hit in pulling all the required data into SSRS.

Huh? That was Lowell's point... And I agree 100% with what he said. My point, as an add on, was to say, "also, make sure aren't bringing back more data than what you intend to display". Handle the filtering in SQL not in the tablix.

The exception being, if your data is being split between multiple tablix, bring back everything all at once...

As an example... The company I work for handles workers comp claims. It's not uncommon for the ops folks to want the data split between surgical and nonsurgical. From both the query and report layout perspectives it's all the same. That means I can pull both surgical and nonsurgical at the same time in the same data set and dump the the surgical into one tablix and nonsurgical into another simply by using the tablix filers to split the data between the two.

The only reason to use a tablix filter is if you you have multiple tablix objects that are displaying subsets of a single, larger data set... In which case, it's better to pull all the required data in a single shot and divvy it up report side.