SSRS Snapshot Filter UserID

0
votes

We have a large "Staff List" report from our HR system which I would like to Cache/Snapshot on a nightly basis to avoid daytime load on the HR DB.

I would like for the Snapshot to return all staff, in all departments, and then have the report filter the rows by department based on @UserID.

The data linking Users to allowed Departments is also in the HR DB.

I currently have 2 datasets. One for the Staff details and another linking Users to Departments.

  • I assume I can't use @UserID in either dataset query and still cache/snapshot.
  • I can't maintain a folder with linked reports for each Department, there are hundreds!
  • Adding a Filter to the Datasets seems to prevent caching/snapshotting.
  • I can't find a way to filter a Table with data from the Staff Details dataset using data in the Linking Dataset.

Any ideas?!

2
reporting-servicessql-server-2008-r2

2 Answers

1
votes

I would set up a Cache Refresh Plan in combination with a Data Driven Subscription to preload the cache for the required Staff Details rows.

Here's the doco:

http://technet.microsoft.com/en-us/library/ms155927.aspx

0
votes

I have managed to resolve this issue by Caching the (shared) dataset rather than the Report. The report parameters are therefore dynamic but the bulk of the data is drawn from an overnight cache.