How filter the data for SSRS reports using the Business Unit of the current logged in user?

(0) Share

Report

Posted on by Community Member

Hi,

I want to develop SSRS reports where I can filter the records and the data by the Business Unit of the current logged in user. These reports will be displayed on the Dashboard.

Can I get the id of the current logged in user or its business unit in my SQL query at runtime, so that I can use it to filter the records such that when the user of say Business Unit A logs in, he is shown the reports but only with the data relevent to the users of Business Unit A. ?

Please advise.

Thanks,
Fahad

*This post is locked for comments

I have the same question (0)

All responses (8)

Answers (0)

Suggested answer

Aileen Gusni 44,524 on at

Like (0)

Report
Copy link

Link copied!

Hi Fahad,

If you use the filteredview, actually it will retrieve and return the data based on what the each user can see, it will be managed by Security Role and Business Unit thing.

So,let's say you use select * from filteredaccount, you set User A to BU A then give access to only BU Access.

Then the user A when run the report will see all Account under his BU ownership, owned by BU A.

While, other user, User B can only see records owned by BU-B and System Admin can see all accounts created and owned by any BU.

Anyway, you can get the current user id (logged used) data from:

CRM SQL function fn_FindUserGuid.

crmtipoftheday.com/.../reflecting-current-user-ssrs

Hope this helps.

Thanks.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report
Copy link

Link copied!

Thanks Aileen,

Looks good. I will check this as there are some complex reports and will get back here.

You're a great help :)

Best,
Fahad

Was this reply helpful? Yes No
Aileen Gusni 44,524 on at

Like (0)

Report
Copy link

Link copied!

Hi Fahad,

To create a report in SSRS, this is very important to use the filteredview and also use the CRM SQL built-in function.

Hope this can solve your problem.

Thanks.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report
Copy link

Link copied!

Hi Aileen,

Can you please reflect more on filteredview? Does this mean that we filter the date via SQL in SSRS query?

Best regards,

Fahad

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report
Copy link

Link copied!

I want to the records of Incident (Case) based on the BU. There are several BUs and each BU manager needs to see the reports for the records of their own BU.

Was this reply helpful? Yes No
Suggested answer

Aileen Gusni 44,524 on at

Like (0)

Report
Copy link

Link copied!

Fahad,

Yes, as I mentioned before.

FilteredView will be useful for you to filter the data easily using security role, not parameter at all in the SSRS.

Thanks.

Was this reply helpful? Yes No
SHRIKANT KARPUR 250 on at

Like (0)

Report
Copy link

Link copied!

can we make it work somehow in VS 2013 BIDS?

Was this reply helpful? Yes No
Suggested answer

Alagunellaikumar 6,212 on at

Like (0)

Report
Copy link

Link copied!

I think VS2013 BIDS doesn't support for Dynamics CRM report extension. try either VS2012 BIDS or 2015BIDS

Was this reply helpful? Yes No