SQL Server Performance

User level usage at the Report level.

Discussion in 'SQL Server Reporting Services' started by Claudia, May 9, 2005.

  1. Claudia New Member

    Hi friends,
    I have one issue. In our organization there are CEO, Managers, asst
    managers, TeamLead, Teammembers.


    There is a report which displays all of them data. But what I want is
    if the user is CEO then he should be able to see all other's data in
    that report.


    if the user is Managers then they should be able to see all asstmanager
    and teamlead and members data in the same report.


    if the user is Asstmanager then he should be able to see teamlead and
    members data in the same report and NOT his superior's data.


    like that Teammembers should be able to see their own data and other
    team members data too. But not superiors data.

    Like these many reports are there. So the model should be become common
    to all reports. Really I am waiting for a good suggestion and advices.


    How can we implement this at report level? We don't have any active
    directory setup. I want just only using system_user from sqlserver.

    Thanks in advance,
    Cladi
  2. moe_mayne New Member

    Here is the way i was able to get by it

    CREATE PROCEDURE GetFundingSummary
    @OrganizationID int,
    @StartDate DateTime,
    @EndDateDateTime,

    AS

    DECLARE @orgNamevarchar(30)
    DECLARE @orgTypevarchar(15)
    DECLARE @lastLoadDateDateTime

    /* Get Organization Name, Type and Last Load Date */
    SELECT@orgName = Name,
    @orgType = OrganizationType,
    @lastLoadDate = dbo.GetLastLoadStatusDate()
    FROM dbo.vOrganization
    WHERE OrganizationId = @OrganizationId

    if @orgType = 'CEO'
    SELECT
    @orgName As OrganizationName,
    @orgType As OrganizationType,
    blah,
    blah
    Else -- If they are another role
    If @orgType = 'VP'
    Select
    blah ,
    blah ,



    then in the report for any field you want to hide say a list for instance you want to go to there Visibility property and type this in the expression

    =IIF(OrganizationType = “CEO”, false, true)...

    I hope this helps...


Share This Page