Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics CRM (Archived) / Dynamics CRM 2016: Pro...
Microsoft Dynamics CRM (Archived)

Dynamics CRM 2016: Problem while using Pre-Filtering in SSRS reports

(0) ShareShare
ReportReport
Posted on by Mohsin Ali 3,614

I've following query that work well untill I am not using CRMAF_ as soon as I put pre filter the report stop working and throw exception. I am not finding way to solve it, any suggestions?

Query 

DECLARE @SQL nvarchar(4000) 

SET @SQL = 'DECLARE @SL nvarchar(500);
SELECT @SL = COALESCE(@SL + '', '', '''') + 
Seller FROM (SELECT new_lastname, 
CASE new_deceased WHEN 0 THEN COALESCE(new_name, '''') + '' '' + COALESCE(new_middlename, '''') + '' '' + COALESCE(new_lastname, '''') 
WHEN 1 THEN COALESCE(new_name, '''') + '' '' + COALESCE(new_middlename, '''') + '' '' + COALESCE(new_lastname, '''') + '' (D)'' 
END AS Seller FROM FilteredNew_party INNER JOIN FilteredSalesOrder AS FilteredSalesOrder ON FilteredNew_party.new_order = FilteredSalesOrder.salesorderid WHERE new_partytype = 2) T ORDER BY new_lastname; SELECT @SL as SellerList'

EXEC (@SQL)

Exception

Report-Error.JPG


*This post is locked for comments

  • Suggested answer
    Community Member Profile Picture
    Community Member on at
    RE: Dynamics CRM 2016: Problem while using Pre-Filtering in SSRS reports

    Make your query like this.

    SELECT CRMAF_FilteredSalesOrder.salesorderid, NP.new_lastname,

    CASE NP.new_deceased WHEN 0 THEN COALESCE(NP.new_name, '''') + '' '' + COALESCE(NP.new_middlename, '''') + '' '' + COALESCE(NP.new_lastname, '''')

    WHEN 1 THEN COALESCE(NP.new_name, '''') + '' '' + COALESCE(NP.new_middlename, '''') + '' '' + COALESCE(NP.new_lastname, '''') + '' (D)''

    END

    FROM FilteredNew_party as NP

    INNER JOIN FilteredSalesOrder AS CRMAF_FilteredSalesOrder

    ON NP.new_order = CRMAF_FilteredSalesOrder.salesorderid AND NP.new_partytype = 2

  • Mohsin Ali Profile Picture
    Mohsin Ali 3,614 on at
    RE: Dynamics CRM 2016: Problem while using Pre-Filtering in SSRS reports

    Here it is:

    DECLARE @SQL nvarchar(4000)

    SET @SQL = 'DECLARE @SL nvarchar(500);

    SELECT @SL = COALESCE(@SL + '', '', '''') +

    Seller FROM (SELECT new_lastname,

    CASE new_deceased WHEN 0 THEN COALESCE(new_name, '''') + '' '' + COALESCE(new_middlename, '''') + '' '' + COALESCE(new_lastname, '''')

    WHEN 1 THEN COALESCE(new_name, '''') + '' '' + COALESCE(new_middlename, '''') + '' '' + COALESCE(new_lastname, '''') + '' (D)''

    END AS Seller FROM FilteredNew_party INNER JOIN FilteredSalesOrder AS CRMAF_FilteredSalesOrder ON FilteredNew_party.new_order = CRMAF_FilteredSalesOrder.salesorderid WHERE new_partytype = 2) T ORDER BY new_lastname; SELECT @SL as SellerList'

    EXEC (@SQL)

  • Community Member Profile Picture
    Community Member on at
    RE: Dynamics CRM 2016: Problem while using Pre-Filtering in SSRS reports

    Share your query which is throwing error.

  • Mohsin Ali Profile Picture
    Mohsin Ali 3,614 on at
    RE: Dynamics CRM 2016: Problem while using Pre-Filtering in SSRS reports

    Saad, this is suspected part that seems to throw exception.

  • Community Member Profile Picture
    Community Member on at
    RE: Dynamics CRM 2016: Problem while using Pre-Filtering in SSRS reports

    Share your code which is throwing error. Use aliasing as well in the query.

    Refer this as how to use prefiltering:

    SELECT     CRMAF_FilteredContact .Contactid

    FROM         FilteredContact AS CRMAF_FilteredContact

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

Daivat Vartak – Community Spotlight

We are honored to recognize Daivat Vartak as our March 2025 Community…

Announcing Our 2025 Season 1 Super Users!

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

Tip: Become a User Group leader!

Join the ranks of valued community UG leaders

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 292,516 Super User 2025 Season 1

#2
Martin Dráb Profile Picture

Martin Dráb 231,407 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans