Skip to main content

Notifications

Customer experience | Sales, Customer Insights,...
Suggested answer

SSRS SQL report show data based on login user role

(0) ShareShare
ReportReport
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.

  • pskateel Profile Picture
    pskateel 102 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,325 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,325 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,325 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,325 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,325 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

News and Announcements

Announcing Category Subscriptions!

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Verified Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,359 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,370 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans