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…

edX Data Science Orientation – Module 1 & 2

Module 1: The Data Science Curriculum

This module explains the journey of the data science course. It is very informative and I really like the way they delivered message > Easy and Clear!

1M1a1M1b

Module 2: Data Science Fundamentals

  1. Working with data
  2. Exploring data
  3. Visualizing data
  4. Slicing and dicing data
  5. Pivot table and Pivot chart

As I have been working on data with Excel for few years so this module is relatively simple to me. I found one interesting part in visualising data, which is introduced by pretty Liberty (Sorry to say initially I was thinking why she is just sitting there, as a flower vase?! Surprisingly, she is actually an expert!)

Well, she introduced 2 of her favourite charts, which I am interested in:

  1. Histogram
    1. Graphical representation of the distribution of numerical data.
    2. Estimate of the probability distribution of a continuous variable
  2. Box and Whisker Chart
    1. Graphically depicting groups of numerical data through their quartiles in descriptive statistics
    2. Box plots may also have lines extending vertically from the boxes (whiskers) indicating variability outside the upper and lower quartiles
    3. Outliers may be plotted as individual points.

Both of them are very useful in data analysis!

Module 3, to be continued…

Microsoft Professional Program Certificate in Data Science

Finally, I started my blog with the first post on Data Science Course provided by Microsoft.

Ta Dang!!! Screenshot of my first course of Microsoft Professional Program for Data Science

 MDX

Well, data will be in an unimaginable volume one day. Taking this course is a kick start for my data adventure. Instead of being frightening by the size of data, I would like to turn it into the powerful thing!

I am now taking the first orientation course for Data Science course. There are 3 modules and 1 lab to be completed. The first module is guidance and introduction to Data Science course. The second module is the important base for a beginner that has zero knowledge of Excel. The third module is the most challenge to me because my statistic course was taken like 7 years ago and I have totally forgotten what I had learnt in University. Anyhow, all the modules focus on building the base in excel and statistic for the coming courses, so they are pretty easy to follow (even though I have not finished Module 3 yet, but soon I will).

I have a contract with my company that I must complete the Data Science course in 2 years, so I have to start the course, no matter how! Last but not least, this also brought the idea of start blogging! Start with my experience in Data Science course provided by Microsoft.

Thank you, Microsoft!

Thank you, my lovely company for sponsoring!