sql - What's the best method of creating a SSRS report that will be run manually many times with different Parameters?

Question

I have a SSRS Sales report that will be run many times a day by users, but with different parameters selected for the branch and product types.

The SQL query uses some large tables and is quite complex, therefore, running it many times is going to have a performance cost.

I assumed the best solution would be to create a dataset, or the report will all permutations, once overnight and then apply filters when the users run the report.

I tried creating a snapshot in SSRS which doesn’t consider the parameters and therefore has all the required data, then filtering the Tablix using the parameters that the users selected. The snapshot works fine but it appears to be refreshed when the report is run with different parameters.

My next solution would be to create a table for the dataset which the report would then point to. I could recreate the table every night using a stored procedure. With a couple of small indexes the report would be lightning fast.

This solution would seem to work really well but my knowledge of SQL is limited, and I can’t help thinking this is not the right solution.

Is this suitable? Are there better ways? Can anybody confirm either way?

Answer 1

等待大神答复