Report running really really slowly!!

Hi, I have a report with 18 cascading report parameters. Each report parameter has a unique dataset which passes the value of the previous parameter into the sql string. As I am selecting the report parameters it is taking longer to query the further down I go. I think this is because of the number of where conditions that are being passed through the sql query. The last report parameter is passing 17 where conditions.

In access when I have done this the parameters near the bottom were being refreshed quicker than the top ones - why is it the opposite way round in reporting services? Any ideas of how to speed this process up?

probobaly becuase in access the data is cached locally.

|||How do you have your parameters structured? Are you building the sql string dynamically? If you give a few more details/examples, I might be able to point you in a direction with less overhead...|||

I wont list all my report parameters, but to show you what I am doing I will list the first few. I have parameters:

Publication, Division, Product Manager.

Parameter Publication is just bringing back a list of publications, divison is bringing back a list of divisions where publication is in paramter publication, i.e

="SELECT
division_code
FROM base_table
WHERE publication_code in ('
" & Parameters!par_pub.Value & "
') " &
"
GROUP BY 1
ORDER BY 1"

Product Manager is bringing back a list of product managers, where publication is in parameter publication and where division is in parameter division, ie

="SELECT
product_range_manager_code,
trim(product_range_manager_desc)as product_range_manager_desc
FROM base_table WHERE division_code IN ('
" & join(Parameters!par_div.Value," ','
") & "
') " &
"

AND publication_code in ('

" & Parameters!par_pub.Value & "

') " &

"
GROUP BY 1,2
ORDER BY 1,2"

Like I said this goes on, so the 4th parameter will contain 3 WHERE conditions until the 18th parameter contains 17 where conditions. My main dataset which populates my report table looks at each of the parameters and filters based on these values. Oh also as you probably have guessed all the processing is done on the server. Hope you can help.

|||First of all, I think that you will see some speedup if you make stored procedures. Depending on the database structure, and where the bottle-neck is that speedup could be considerable or not. It seemed that you were pulling all of your data from one base table? If that is the case, you could write one stored procedure that would populate each dropdown, with a where clause of the form:

Code Snippet

WHERE foo IN (@.param1) AND (@.param2 IS NULL OR bar IN (@.param2)) AND ...

You should also verify whether there are any dependencies in your parameters that you can exploit to reduce calls to the database. In other words check if selecting a particular parameter is the same as selecting another one downstream (1 to 1), in which case that second filter (and clause) does not need to be applied at all. Similarly, verify that you need the previous filters applied once you know the downstream selections. For example, if all publications are produced by only one division, then in the second query you supplied you do not need to filter for the divison code at all.

Since you are not aggregating any columns, the group by clause is unnecessary. The SQL compiler should have caught that, so eliminating it probably won't save you anything, but it never hurts to remove unnecessary bits.

Finally, if these steps still result in a report that takes too long to refresh, you have a couple of options. You can create a drill-through report, wherein you actually navigate to a child report with fewer parameters, and you can look at improving your database structure to optimize this sort of activity, e.g. via normalization and adding indexes.

I hope this helps.