locked
CRMAF not working RRS feed

  • Question

  • Hi

    I am creating a report but it not prefiltring record on student entity

    my query is

    SELECT  CRMAF_Filteredses_student.ses_studentid as SId, 
    	 
    	CRMAF_Filteredses_student.ses_studentname as StdentName, 
    CRMAF_Filteredses_student.ses_student as Student,
    	FilteredInvoice.invoiceid AS TrxID, 
            FilteredInvoice.invoicenumber AS TrxNo, 
    	FilteredInvoice.ses_invoicedate AS TrxDate,
      		
    	FilteredInvoice.totalamount as Amount
    FROM            Filteredses_student as CRMAF_Filteredses_student INNER JOIN
                            FilteredInvoice ON CRMAF_Filteredses_student.ses_studentid = FilteredInvoice.ses_studentid
    Where  
    (FilteredInvoice.ses_invoicedate BETWEEN @Start and @End)
    UNION
    
    
    SELECT  CRMAF_Filteredses_student.ses_studentid as SId, 
    	CRMAF_Filteredses_student.ses_student as Student, 
    	CRMAF_Filteredses_student.ses_studentname as StdentName, 
     	Filteredses_payment.ses_paymentId AS TrxID, 
    	Filteredses_payment.ses_PaymentNo AS TrxNo,
    
    	Filteredses_payment.ses_PaymentDate AS TrxDate,	
    	Filteredses_payment.ses_PaymentAmt * - 1 as Amount
    
    FROM           Filteredses_student as CRMAF_Filteredses_student  INNER JOIN
                          Filteredses_payment on CRMAF_Filteredses_student.ses_studentid=Filteredses_payment.ses_studentid
    Where 
     (Filteredses_payment.ses_PaymentDate BETWEEN @Start and @End)

    I am using CRM 2011 on-Premise

    Thursday, January 2, 2014 4:16 PM

All replies

  • With the UNION, you may need to re-write using Explicit Pre-Filtering:

    When you use the CRMAF_ prefix to enable automatic pre-filtering, Microsoft Dynamics CRM adds a parameter in the query. With a more complex query, such as a query that uses UNION statements, this can lead to unexpected results because Microsoft Dynamics CRM might only add the parameter to the first query

    • Proposed as answer by Ronald Liu Thursday, January 2, 2014 7:34 PM
    Thursday, January 2, 2014 5:02 PM