Category: SQL

SSRS Report – Dynamic Grouping at Report Level

I found myself in trouble when some users would like to view the total amount grouped by the employee while some users would like to view the total amount by the shipping method or even by the vendor!!! Then, dynamic grouping came across my mind, which could be used to easily achieve this design.

HOW???

  1. Create a Parameter (named as GroupingType), set 3 values in this parameter = Employee, Vendor and Shipping.

CCBADG parameter.png

2. At report level, in the row group’s properties, set the group on expression as shown below:

=Switch(Parameters!GroupingType.Value = “E”, Fields!FirstName.Value, Parameters!GroupingType.Value = “V”, Fields!vendor.Value, Parameters!GroupingType.Value = “S”, Fields!shipname.Value)

CCBADG dynamic grouping

By doing that, the report could be grouped by 3 levels depending on the selected Parameter. Easy and Cool!!!

CCBADGb report desgin

Any side effect???

Yes, it has a painful impact on report loading time. However, if your data is aggregated or relatively small in volume, then don’t bother! Else, I would suggest you avoid the dynamic grouping at report level and I have shared this in another post – SSRS Performance Improvement III – Avoid Dynamic Grouping at Report Level

Please feel me to email me via Contact if you would like to get my sample.

Just try it yourself!!!

SSRS Performance Improvement III – Avoid Dynamic Grouping at Report Level

Part III of SSRS performance improvement! Thanks to myself for not giving up in fine tuning my report performance, the report loading time has reduced from almost 2 minutes to 10 seconds! I am incredibly happy for this great achievement, not wasting my effort of thinking squeezing rotating my brain throughout the night.

My previous post about SSRS Performance Improvement II – Avoid Data Filter at Report Level has significantly reduced the loading time by half, but the report loading time is still taking almost 2 minutes. It is fine for some users but some of the impatient users, 2 minutes report loading time is killing them!!!

So, without further mumbling, let’s start working on the SSRS report enhancement! In this post, I will show how to avoid dynamic grouping by Parameter at SSRS report level. I never realised that dynamic grouping has such a great impact on performance.

What is dynamic grouping? The report could be grouped by 3 levels depending on the selected Parameter. Please visit SSRS Report – Dynamic Grouping at Report Level for more details. I have shared the step by step guidance on how to setup the dynamic grouping at the report level.

Well, back to the topic, HOW to avoid the expression grouping at report level? we could enhance the dataset query as below:

First of all, we have to enhance the dataset query as below:

SELECT T.*, E.FirstName, V.Name as vendor, S.Name as shipname,
 case when @GroupingType = 'E' then E.FirstName
 when @GroupingType = 'V' then V.Name
 when @GroupingType = 'S' then S.Name end as GroupingType
 FROM [AdventureWorks2014].[Purchasing].[PurchaseOrderHeader] T
 left join [AdventureWorks2014].[Purchasing].Vendor V on V.BusinessEntityID = T.VendorID
 left join [AdventureWorks2014].[Purchasing].ShipMethod S on S.ShipMethodID = T.ShipMethodID
 left join [AdventureWorks2014].[Person].[Person] E on E.BusinessEntityID = T.EmployeeID

Then, let’s set the row group properties to group on “GroupingType”

CCBADG grouping parameter.png

The GroupingType parameter is passed to a new column [GroupingType] in the dataset with case scenario. If E is selected, then the column will be retrieved from FirstName of the Employee. This simple step has reduced the loading time from minutes to seconds. Yeah!!!

Last but not least, as usual, I appreciate if you could provide some brilliant ideas for me. 🙂

Also, please feel me to email me via Contact if you would like to get my sample.

Just try it yourself!!!

SSRS Performance Improvement II – Avoid Data Filter at Report Level

Again, I would like to share another real life example in SSRS performance improvement. Obviously, in some complicated reports, my suggestions in SSRS Performance Improvement – First experience are unable to help.

So, I will show how to avoid filter in SSRS report design in this post. This is because data filtering at report level in SSRS has a significant impact on performance.

When possible, filter the data at the query level using a WHERE clause because the database is better suited to filter data. There is no gain in pulling all of the data from the database and later filtering it at the report level. When you don’t have any other option and you can’t modify the dataset query then you could use dataset filtering at the report level (i.e. you have a report containing more than one matrix/list/tablix/chart and they share the same general dataset), but the outputs must be filtered differently (i.e. one Tablix is filtered on the customers and the other Tablix is filtered on the products). Link

Real experiment:

With the filter in the tablix properties, my report loading time is 3-4 minutes; after removing that filter, the report loading time is reduced to 1-2 minutes.

Case Scenario: There are 2 parameters in the reports.

  • @StatementDate
  • @DateType – ShipDate [S], OrderDate [O]

CCBFRM parameter

The chosen date type’s date should be compared to the selected statement date, in order to display the desired result.

Initially, I have a filter in the tablix as shown in the screenshot below:

CCBFRM tablix filter

To increase the performance, I have fine tuned my query as shown below:

SELECT * FROM [AdventureWorks2014].[Purchasing].[PurchaseOrderHeader] T
where 
T.OrderDate <= case when @DateType = 'O' then @DateStatement else getdate() end
and
T.ShipDate <= case when @DateType = 'S' then @DateStatement else getdate() end

The DateType is passed to the where clause in the query with case scenario. This simple step has reduced the loading time to half. Yeah!!! However, I know my report would need more improvement, I appreciate if anyone of you has a better suggestion.

Please feel me to email me via Contact if you would like to get my sample.

Just try it yourself!!!