SSRS Report Content Filtering by User Group

Question

SSRS Report Content Filtering by User Group

Larry Schmidt-491187

SSCrazy

Points: 2908
More actions
June 3, 2013 at 12:03 am

#294664

Comments posted to this topic are about the item SSRS Report Content Filtering by User Group

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

Marzipan Valued Member Points: 73 More actions · Answer 1

I really liked your article.

One potential issue that may cause confustion to doing manual troubleshooting of user/group membership, if the group that has access to the report "GroupA" has a nested groups:

1) members of all nested groups end up in the "SSRS_GroupMembers" table but the corresponding "UserGroup" column will be listed as "GroupA" or the top level group.

2) only "GroupA" ends up in the "SSRS_Group_Reports" "GroupName" column.

If a user is only a member of "GroupB" (nested group), during troubleshooting most manual AD Query tools like "net user /domain [userid]" will NOT list "GroupA" as a group the user is a member of.

Ideally, changing the column name from "GroupName" to "ParentGroupName" or "TopLevelGroupName" and having a flag column named "InNestedGroup" in "SSRS_GroupMembers" would be helpful. However, the code for that would be much more involved.

sridhartvmalai SSC Enthusiast Points: 112 More actions · Answer 2

Very interesting article, I really like it.

Thank you

Larry Schmidt-491187 SSCrazy Points: 2908 More actions · Answer 3

Thanks for your reply!

I really hadn't thought of the nested group situation - I don't think we use that much, so it didn't come up - but I think you have some very good points for someone who might want to adopt this approach in an organization where nested groups are used.

Thanks again for your kind words,

Larry

Larry Schmidt-491187 SSCrazy Points: 2908 More actions · Answer 4

Larry Schmidt-491187

SSCrazy

Points: 2908

June 3, 2013 at 7:55 am

#1620557

Thanks for your kind words!

Larry

Brett Phipps Default port Points: 1483 More actions · Answer 5

Brett Phipps

Default port

Points: 1483

June 3, 2013 at 9:39 am

#1620621

Wish I'd known about this 4 years ago!!!!

Larry Schmidt-491187 SSCrazy Points: 2908 More actions · Answer 6

Brett,

I assume that means you'll find this useful . . . hopefully, better late than never!

Thanks,

Larry

wzkmafi SSC Veteran Points: 292 More actions · Answer 7

This is an awesome article and I'm ready to show off the potential. But,

Is this limited to SharepointMode SSRS? I have a native mode solution.

Larry Schmidt-491187 SSCrazy Points: 2908 More actions · Answer 8

Thanks - glad you liked it!

No, I didn't do anything special in the SSRS module, other than use the SharePoint data source and destination connections which are standard in Visual Studio 2010, and can easily be added to 2008 by going to the link in the article.

Larry

Larry Schmidt-491187 SSCrazy Points: 2908 More actions · Answer 9

Sorry, it's early (!) - the SharePoint Data Source and Destination connections are, of course, SSIS "plug ins." I did nothing special in the SSRS report - by that time, everything was in a SQL Server table that I could easily query. Each SSIS package builds a table for the report's use.

Larry

wzkmafi SSC Veteran Points: 292 More actions · Answer 10

Thank you for your response. I think I can pull this off specifically for Sales Territories, perhaps using district alignments. Worlds of opportunity in my head after reading this; I came in to work two hours early!

tony-591909 SSC Enthusiast Points: 174 More actions · Answer 11

Very nicely done! Was there a duplicate query image in the presentation, under the sentence:

Then, the report's main dataset includes this in the WHERE clause of its query:

I expected to see the main dataset query.

Thanks!

Larry Schmidt-491187 SSCrazy Points: 2908 More actions · Answer 12

Thanks for your reply!

The key word in that sentence is "report's main dataset includes this in the WHERE clause" . . . in the interest of brevity, I didn't include the whole query, which was quite lengthy.

And, actually, since you brought this up, a later version of the report shipped off the query to a stored procedure, and there, instead of being in the WHERE clause, the filtering on BusinessSegmentName took place in the JOIN - @GLSegment contains the various segments that the user is able to view:

from dbo.uv_TRM_Scorecard_Data SD

-- Added line for INNER JOIN to GL_OU table

INNER JOIN GL_OU AS ou ON SD.GL_OU = ou.GL_OU AND ou.BusinessSegmentName IN (Select Data from Split(@GLSegment,','))

-- Added line ^

LEFT OUTER JOIN dbo.uv_TRM_Scorecard_Milestone SM ON SD.UHT_ID=SM.UHT_ID

LEFT OUTER JOIN dbo.uv_TRM_EST_CostSummary_current SC ON SC.ProjectNumber=SD.UHT_ID

Larry

tony-591909 SSC Enthusiast Points: 174 More actions · Answer 13

Thanks Larry! I just learned something new as well with this select/split command: "(Select Data from Split(@GLSegment,',')". Will add this to my command reference list!

Larry Schmidt-491187 SSCrazy Points: 2908 More actions · Answer 14

Thanks, Tony!

You will also need to create a Split function in your database . . . can be found in lots of places on the web, but here's one variation:

CREATE FUNCTION [dbo].[Split]

(

@RowData nvarchar(max),

@SplitOn nvarchar(5)

)

RETURNS @RtnValue table

(

Id int identity(1,1),

Data nvarchar(100)

)

AS

BEGIN

Declare @Cnt int

Set @Cnt = 1

While (Charindex(@SplitOn,@RowData)>0)

Begin

Insert Into @RtnValue (data)

Select

Data = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))

Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))

Set @Cnt = @Cnt + 1

End

Insert Into @RtnValue (data)

Select Data = ltrim(rtrim(@RowData))

Return

END