Greetings to everyone!!!

So last week one of the report was having a performance issue. It had 10 datasets ,5 data regions and a report parameter. Regions displayed cascading information based on user's selection of parameter.

For example,. lets assume the parameter name to be ReportLevel, it would have 3 possible values - Country, State, City. This being a multi valued parameter will decide which data region to be displayed. In this example we will have 3 datasets binded to 3 tablix or tables or data regions. So if user selects Country only than country data region will be displayed and like wise Country and city
level details will only be shown if the user selects the value as country, city.

Now I assumed (most of you guys would already know the correct behaviour 🙂 ), only those datasets or queries would be executed which are being displayed. On further having a discussion with one of my friend and an expertise in SSRS, I came to know that all datasets will be renderred and queries would be executed.

I further explored this with the help of sql profile.

PERFORMACE TIP - So if you have similar criteria than a performamce tuning tip is to include additional parameter at back end and execute the internal query only if required. Example:

IF ReportLevel='Country'

BEGIN
SELECT * FROM TABLE_COUNTRY

END

Hope this helps, have a fantastic day and best wishes for your IT career!!!