1

Another SSRS question. I've got a report where we want to let the end-user filter the report by a list of names, but those names need to be already filtered by the worksite the user is at. This filtering of the values list for the Name parameter needs to happen behind the scenes; the user shouldn't be able to see or select names that are not relevant to his site. For example, say there are 200 unique names in the table, but only 15 of those work at this site; the end user should only be able to see and select from those 15 names to filter the report by. Is there a way to do this filtering of a parameter's Available Values based on the value of a different parameter (i.e., the site)?

NB: This report is querying a SharePoint list, so a resolution using SQL - as here or here - is probably not going to work; I'm open to correction on that point, though.

Community
  • 1
  • 1
Ben C.
  • 1,761
  • 5
  • 15
  • 24

1 Answers1

1

From the SSRS side of things, I would do this. (I know little about sharepoint)

  1. Create a text report parameter (@user in this example) and set the default value to the Built-In Field "UserID". The userid will be something like "DOMAIN\user".
  2. Create a dataset (siteid in this example) using the @user parameter to pull the current report runners site id. I had to create a lookup table in SQL specifically for this.
  3. Create another report parameter of whatever type is needed for siteid (@site in this example), and set the default value to "Get values from Query". Select the siteid dataset field as the default.
  4. Create a dataset (names in this example) using the @siteid parameter in the where clause.
  5. Set both parameters to "hidden".

This should allow the report to auto-execute and retrieve only the name values that match the current users' siteid.

Eric Hauenstein
  • 2,557
  • 6
  • 31
  • 41
  • I actually had already done more or less what you described, but it turns out I had just forgotten to put a filter on the dataset for the name parameter to filter out by site. It seems obvious now, but I spent three or four days trying to figure that out. – Ben C. Jul 19 '13 at 19:58