Category: SSRS

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!!!

Advertisements

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!!!

SSRS Performance Improvement – First experience

Well, I would like to share my personal first experience on SSRS performance improvement. The story began with getting complaints by the user on the long waiting time in report generation. Loading… Loading… Loading… then time out error?!! Holy Happy!

I have found a useful Link regarding SSRS best practise, not all of them could be applied to my SSRS report, eventually, by applying a few of them, the report generation time was significantly reduced!

  1. Modify page break properties. Please ensure the KeepTogether properties for Tablix is also False. [Displaying all data on a single page will typically result in a performance issue. The report will not display any data until it completes the report processing and rendering.]

PageBreakNote that you have multiple rectangles in a report, and originally they are one rectangle per page, now you might get crazy by setting this page break properties. Just imagine you have 100 pages in the first rectangle, are you going to click 100 times before reaching the second rectangle? No worries, I would advise using document map, in each rectangle properties, set the DocumentMapLabel as the screenshot below.

DocumentMap

Result:

DocumentMap2

2. Avoid data conversion or having the custom format on the report example format date with dd-MMM-yyyy. Instead of this, please enhance your query. [Suppose a string data type is being stored as a date value. If you need to perform date operations to create three different values for your report, you will have to convert the value three times. If you performed the data conversion at the query level then you could directly perform the date operation to get the three new values. This would reduce the burden on the report server and would make it easier to maintain.]

Suggestion:

  1. select CONVERT(VARCHAR(11), T.DateABC, 106) as DateABC from Table1
  2. Get DateABC on the report without any formatting

 

A first attempt in optimising performance! Only 2 steps, my SSRS report is generated > 10x faster!

To be continued…