SSRS Report - Dynamic Parameters causing awful slowness

0
votes

I've built an SSRS Report with a series of parameters and data sets a user specifies a season (first parameter). There are several data sets -- see below.

The first is a procedure: called LRP_Weekly_Stats returns 9 fields (including, location, eventdata, option, season, and a few others.

The second data set pulls - all the distinct seasons from a view - that gets sent to the parameter drop down. select distinct season from myview it shows/returns just season.

2 additional data set exists it reads 

select distinct location from myview where my season = parameter season

and

 select distinct option from myview where my season = parameter season

each of these data sets return location and option respectively.

enter image description here

We can't build this as 1 single data set because each of the 5 locations has each of the 4 options associated with and the data being returned gets multiplied by itself.

Data in my view looks like this

Location         option
-------------------------
Location1        option1
Location1        option2
Location1        option3
Location1        option4
Location1        option5
Location2        option1
Location2        option2
Location2        option3
Location2        option4
Location3        option1
Location3        option2
Location3        option4
Location3        option5
etc.

In order for the parameters to show each location once and each option 1 for the parameter drop down we do two separate queries.

I didn't build the view and can't adjust it and even if i could the data is such that each location can have each option.

so my two data sets each return the distinct list of possible locations and/or options by season.

in the parameters in the SSRS report the set up looks like this:

enter image description here

so the data coming in to my parameter is pulled from my data set. for each of the three parameters. location and option_1 and option_2. The code itself runs in under 5 seconds however because of all this linkage it takes a good 30+ minutes to run the report. This is completely unacceptable (and I can't remove the drop down data because the users need it).

Any advice about how to streamline this or make it more efficient. Please help.

1
sql-serverdynamicreporting-servicesparametersdataset
Can you skip using the view and DISTINCT and go directly to queries that query for location by season and option by season from the location and option lookup tables in the database? – Jesse
Sadly no, I put in a request to get access to those tables, but so far denied. I have to work with this view because it pulls the data from 4 different databases into one central location. – Elizabeth
Short of you being able to write stored procedures, created specifically for your report, there isn't going to be a lot that you can... :( – Jason A. Long
So your user selects "season" from an unrestricted dropdown parameter list first, then selects "location" and "option" from two dropdown parameter lists that are restricted by the selected season, and finally runs the report, and it's the final report that takes 30+ minutes to run, correct? – Brian
From your response to Brian's comment I can only assume the main report dataset appears to be the problem, can you edit your question and post the dataset query for that. – Alan Schofield

1 Answers

0
votes

Based on your reply in the comments on your initial question, it looks to me like the issue is either in the report procedure itself, or possibly a rendering issue. I would look at performance tuning the main report queries. And I'm afraid I can't be more specific than that without knowing a lot more details about your report's architecture.