SSRS Report Content Filtering by User Group

  • Larry Schmidt-491187

    SSCrazy

    Points: 2552

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

  • Marzipan

    Valued Member

    Points: 73

    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: 102

    Very interesting article, I really like it.

    Thank you

  • Larry Schmidt-491187

    SSCrazy

    Points: 2552

    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: 2552

    Thanks for your kind words!

    Larry

  • Brett Phipps

    Ten Centuries

    Points: 1373

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

  • Larry Schmidt-491187

    SSCrazy

    Points: 2552

    Brett,

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

    Thanks,

    Larry

  • wzkmafi

    SSC Veteran

    Points: 262

    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: 2552

    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: 2552

    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: 262

    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

    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: 2552

    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

    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: 2552

    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

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

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