You’re offline. This is a read only version of the page.
Hi.
I have created a SQL-based report in Dynamics CRM 365. Filter by date works fine, but I can't make it filter by owning user and his groups. My users want to be able to see they activities by they department.
Here is the query:
select distinct (CRMAF_FilteredActivityPointer.activityid), CRMAF_FilteredActivityPointer.ownerid as ownerid, CRMAF_FilteredActivityPointer.owneridname as name, CRMAF_FilteredActivityPointer.activitytypecode, CRMAF_FilteredActivityPointer.activitytypecodename as activityType, CRMAF_FilteredActivityPointer.scheduledstart as start, CRMAF_FilteredActivityPointer.scheduledend as finish, CRMAF_FilteredActivityPointer.regardingobjectidname as client, CRMAF_FilteredActivityPointer.subject as subject, CRMAF_FilteredActivityPointer.description as description, dbo.GetKR_Sales_Chanel_Value(app.kr_sales_chanel,CRMAF_FilteredActivityPointer.activitytypecode) as kr_sales_chanel, app.kr_result as kr_result, dbo.kr_ActivityMembers(CRMAF_FilteredActivityPointer.activityid, CRMAF_FilteredActivityPointer.activitytypecode) as participants from FilteredActivityPointer as CRMAF_FilteredActivityPointer left outer join FilteredAppointment as app on CRMAF_FilteredActivityPointer.activityid=app.activityid left outer join FilteredEmail as em on CRMAF_FilteredActivityPointer.activityid=em.ActivityId where CRMAF_FilteredActivityPointer.activitytypecode=4212 or CRMAF_FilteredActivityPointer.activitytypecode=4201 or CRMAF_FilteredActivityPointer.activitytypecode=4202 or CRMAF_FilteredActivityPointer.activitytypecode=4210 order by kr_sales_chanel desc, CRMAF_FilteredActivityPointer.owneridname, CRMAF_FilteredActivityPointer.activitytypecodename asc, CRMAF_FilteredActivityPointer.scheduledend asc
We have configured departments and manager hierarchy. Can you give me any directions?
*This post is locked for comments
Based on your similar thread on the stackoverflow looks like you're trying to use prefiltering in your report. Can you please provide a screenshot that shows filters that you use when you get issue you described?
To get current user id in T-SQL report you can use function dbo.fn_FindUserGuid() so to filter records by current owner you can use following SQL:
CRMAF_FilteredActivityPointer.ownerid = dbo.fn_FindUserGuid()
if you want to add activities that are owned by teams to which current user belongs to you can use following filter:
CRMAF_FilteredActivityPointer.ownerid in (select tm.teamid from FilteredTeamMember tm where tm.systemuserid = dbo.fn_FindUserGuid())
I want users see eachother activities. This users in one team and one business unit. Then I choose "Equals Current User or His Reporting Hierarchy" they still see only their activities.
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,253
Super User 2024 Season 2
#2
Martin Dráb
230,188
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (