Skip to main content

Notifications

Microsoft Dynamics CRM forum
Suggested answer

SSRS SQL report show data based on login user role

Posted on by 55

I have written SQL query to display created cases and closed cases based on team.But for a agent login it need to only his data.and admin need to see his data as well as agent data.After deploying the rdl it shows same data with different user security roles.any help on this would be appreciated.

Categories:
  • pskateel Profile Picture
    pskateel 100 on at
    RE: SSRS SQL report show data based on login user role

    i have a similar question and i used a fetchxml query to build ssrs report , but not sure what condition i need to add so that logged in user will see  records based on business unit

    community.dynamics.com/.../the-ssrs-report-created-used-in-the-dynamics-365-does-it-show-record-to-users-based-on-security-role-or-it-shows-all-the-record

    a33ik 

  • a33ik Profile Picture
    a33ik 84,321 Most Valuable Professional on at
    RE: SSRS SQL report show data based on login user role

    I'm sorry - I'm not sure what you mean.

  • lathareddy Profile Picture
    lathareddy 55 on at
    RE: SSRS SQL report show data based on login user role

    Can we not use parameter to filter only the current logged in user data

  • a33ik Profile Picture
    a33ik 84,321 Most Valuable Professional on at
    RE: SSRS SQL report show data based on login user role

    The only other way is to use FetchXml datasource for that purpose.

  • lathareddy Profile Picture
    lathareddy 55 on at
    RE: SSRS SQL report show data based on login user role

    Tried that also.but with respect to user login it is not showing data.

  • Suggested answer
    a33ik Profile Picture
    a33ik 84,321 Most Valuable Professional on at
    RE: SSRS SQL report show data based on login user role

    In order to respect security you should use a Filtered view instead of regular one.

    So the query should be like the following:

    Select

    i.incidentid

    ,i.name

    from FilteredIncident i

  • lathareddy Profile Picture
    lathareddy 55 on at
    RE: SSRS SQL report show data based on login user role

    I have written a query like

    Select case when c.statecode=0 then c.createdon else c.resolvedon,ad.name from incident as a

    Left join systemuser as ac on c.owininguser=ac.systwmuserid

    Left join team membership on ac.systemuserid=teammembership.systemuserid

    Left join team as ad on team membership.teamid=ad teamid

    Where c.createon>=dateadd(day,-7,getdate())

    And ad.name='abc'

    Group by createon,ad.team,resolvedon

  • a33ik Profile Picture
    a33ik 84,321 Most Valuable Professional on at
    RE: SSRS SQL report show data based on login user role

    Can you please provide the queries you used?

  • lathareddy Profile Picture
    lathareddy 55 on at
    RE: SSRS SQL report show data based on login user role

    SQL queries

  • a33ik Profile Picture
    a33ik 84,321 Most Valuable Professional on at
    RE: SSRS SQL report show data based on login user role

    Hello,

    What did you use to build the report - T-SQL or FetchXml?

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Anton Venter – Community Spotlight

Kudos to our October Community Star of the month!

Announcing Our 2024 Season 2 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Dynamics 365 Community Newsletter - September 2024

Check out the latest community news

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 290,524 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 228,469 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans