Sign in
Microsoft.com
 
Microsoft
 
 
 

locked
CRMAF not working RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    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

  • Question
    Sign in to vote
    1
    Sign in to vote

    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