Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics CRM (Archived) / SSRS Prefiltering With...
Microsoft Dynamics CRM (Archived)

SSRS Prefiltering With Complex Query

(0) ShareShare
ReportReport
Posted on by Community Member Microsoft Employee

Poor question,,

Hii, i want to create Report for CRM 2016 On-premise using pre-filtering. I have a query like below :

;with cte as (select dbo.new_troubleticket.new_troubleticketid,  
dbo.new_actiontaken.new_name as [Action Taken], dbo.new_troubleticket.new_rootcauseidname as [Root Cause] from dbo.new_new_troubleticket_new_actiontaken 
INNER JOIN new_troubleticket ON new_new_troubleticket_new_actiontaken.new_troubleticketid = new_troubleticket.new_troubleticketid 
INNER JOIN new_actiontaken ON new_new_troubleticket_new_actiontaken.new_actiontakenid = new_actiontaken.new_actiontakenid)
, cte1 as (Select new_serviceId, dbo.Account.new_CustomerID as [Customer ID],new_accountidname as [Name] from new_service LEFT JOIN Account ON new_service.new_AccountId = Account.AccountId)

Select  dbo.new_troubleticket.new_name as [Ticket ID], dbo.new_troubleticket.new_referalticketname as [Referral Ticket],dbo.new_service.new_Building1 as Link, Dateadd(mi, datediff(mi, getutcdate(), getdate()), new_startdown) As [Down Time], 
Dateadd(mi, datediff(mi, getutcdate(), getdate()), new_startup) As [Up Time], 
dbo.new_service.new_AccountIdName as [Customer Name], cte1.[Customer ID], dbo.new_service.new_name as [Link ID], dbo.new_service.new_Building1 as [Link Name] ,
 dbo.new_troubleticket.new_RootCauseIdName, dbo.new_troubleticket.new_TTActionTaken
,FLOOR((new_troubleticketservice.new_duration*60)/86400) AS HARI
,FLOOR(((new_troubleticketservice.new_duration*60)/3600) - FLOOR((new_troubleticketservice.new_duration*60)/86400) *24) AS JAM 
,FLOOR(((new_troubleticketservice.new_duration*60)/60) - FLOOR((new_troubleticketservice.new_duration*60)/3600) *60) AS MENIT
, new_troubleticketservice.new_duration as [Menit Total]
 from new_troubleticketservice 
 LEFT JOIN new_troubleticket ON new_troubleticketservice.new_TroubleTicketId = new_troubleticket.new_troubleticketId
 LEFT JOIN cte on new_troubleticketservice.new_troubleticketId = cte.new_troubleticketid 
 LEFT JOIN new_service ON new_troubleticketservice.new_ServiceId = new_service.new_serviceId
 LEFT JOIN cte1 on new_troubleticketservice.new_ServiceId = cte1.new_serviceId

  where cte1.[Name] like 'Company a'

Where should i put 'AS CRMAF_FilteredAccount' ?
On the example i get from the internet, they put 'AS CRMAF_' right after 'From table_name'. Is it same with my query ?

*This post is locked for comments

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: SSRS Prefiltering With Complex Query

    Hii, thanks. I have solved this after reading those article. The idea is using 2 dataset. Where the second dataset hold CRMAF_. and after get CRMAF_Filteredaccount, then pass into the first dataset as parameter

  • Verified answer
    Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: SSRS Prefiltering With Complex Query

    read:blogs.msdn.microsoft.com/.../microsoft-dynamics-crm-pre-filtering-tips

    https://xrmdevel.blogspot.com/2011/09/ssrscrmaf-auto-filter-on-multiple.html?showComment=1500558053970#c6380319131815993251

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: SSRS Prefiltering With Complex Query

    Hi , David, thanks for your reply. A few moments ago, i have succesfully run SSRS with pre-filtering using main and sub report to pass account name/id as parameter. In the main report i use

    select accountid from FilteredAccount as CRMAF_FilteredAccount

    and pass accountid from main report into subreport.

    However, i cant pass the other parameter, so when the user call sub reports, it needs to fill date as another parameter.

    anyways, let me try your code.

    Thanks

  • Verified answer
    David Jennaway Profile Picture
    David Jennaway 14,063 on at
    RE: SSRS Prefiltering With Complex Query

    If you use implicit pre-filtering (using the AS CRM_Filtered<entityname>) then you use the CRM_Filtered<entityname> alias where you reference the entity, though this only works if the entity is only referenced once in the query. It looks like you only reference the account entity once in your query, so the following should work:

    ;with cte as (select dbo.new_troubleticket.new_troubleticketid,  
dbo.new_actiontaken.new_name as [Action Taken], dbo.new_troubleticket.new_rootcauseidname as [Root Cause] from dbo.new_new_troubleticket_new_actiontaken 
INNER JOIN new_troubleticket ON new_new_troubleticket_new_actiontaken.new_troubleticketid = new_troubleticket.new_troubleticketid 
INNER JOIN new_actiontaken ON new_new_troubleticket_new_actiontaken.new_actiontakenid = new_actiontaken.new_actiontakenid)
, cte1 as (Select new_serviceId, dbo.CRMAF_FilteredAccount.new_CustomerID as [Customer ID],new_accountidname as [Name] from new_service 
LEFT JOIN Account AS CRMAF_FilteredAccount ON new_service.new_AccountId = CRMAF_FilteredAccount.AccountId)

Select  dbo.new_troubleticket.new_name as [Ticket ID], dbo.new_troubleticket.new_referalticketname as [Referral Ticket],dbo.new_service.new_Building1 as Link, Dateadd(mi, datediff(mi, getutcdate(), getdate()), new_startdown) As [Down Time], 
Dateadd(mi, datediff(mi, getutcdate(), getdate()), new_startup) As [Up Time], 
dbo.new_service.new_AccountIdName as [Customer Name], cte1.[Customer ID], dbo.new_service.new_name as [Link ID], dbo.new_service.new_Building1 as [Link Name] ,
 dbo.new_troubleticket.new_RootCauseIdName, dbo.new_troubleticket.new_TTActionTaken
,FLOOR((new_troubleticketservice.new_duration*60)/86400) AS HARI
,FLOOR(((new_troubleticketservice.new_duration*60)/3600) - FLOOR((new_troubleticketservice.new_duration*60)/86400) *24) AS JAM 
,FLOOR(((new_troubleticketservice.new_duration*60)/60) - FLOOR((new_troubleticketservice.new_duration*60)/3600) *60) AS MENIT
, new_troubleticketservice.new_duration as [Menit Total]
 from new_troubleticketservice 
 LEFT JOIN new_troubleticket ON new_troubleticketservice.new_TroubleTicketId = new_troubleticket.new_troubleticketId
 LEFT JOIN cte on new_troubleticketservice.new_troubleticketId = cte.new_troubleticketid 
 LEFT JOIN new_service ON new_troubleticketservice.new_ServiceId = new_service.new_serviceId
 LEFT JOIN cte1 on new_troubleticketservice.new_ServiceId = cte1.new_serviceId

  where cte1.[Name] like 'Company a'


    If this doesn't work, you can use explicit pre-filtering to have control over how the query is built.

    Note that your query does not use filtered views for other entities. This will cause it to fail for any users without the relevant SQL permissions, and is also unsupported. You should replace e.g. new_service with Filterednew_service

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

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 Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

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

#2
Martin Dráb Profile Picture

Martin Dráb 230,235 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans