locked
How to make Pre-Filtered SSRS Report in CRM 2011 on premise ? RRS feed

  • Question

  • Hello,

    As i am new in SSRS Reports, I am stucked at this thing. I want to know how to create a Pre-Filtered SSRS Report in CRM 2011 on-premise version.

    I want this report to create on selection on Contacts from Contacts Grid in CRM.

    If user selects 5 Contacts then the reports should be generated for only those 5 Contacts.

    How can i do this, Please suggest some examples.


    If this post answers your question, please click "Mark As Answer" on the post and "Mark as Helpful"

    Wednesday, July 24, 2013 5:51 AM

Answers

  • Your query should look like:-

    SELECT     CRMAF_FilteredNew_enrolment.new_enrolmentdate, CRMAF_FilteredNew_enrolment.new_teacheridname, CRMAF_FilteredNew_enrolment.new_trn,
                          CRMAF_FilteredNew_verification.new_presentationreceivedfromverifier, CRMAF_FilteredNew_submission.new_name
    FROM         FilteredNew_enrolment AS CRMAF_FilteredNew_enrolment INNER JOIN
                          FilteredNew_submission AS CRMAF_FilteredNew_submission ON
                          CRMAF_FilteredNew_enrolment.new_enrolmentid = CRMAF_FilteredNew_submission.new_enrolementsubid INNER JOIN
                          FilteredNew_verification AS CRMAF_FilteredNew_verification ON
                          CRMAF_FilteredNew_submission.new_submissionid = CRMAF_FilteredNew_verification.new_verificationsubid
    WHERE     (CRMAF_FilteredNew_verification.new_presentationreceivedfromverifier IS NOT NULL)


    Regards Faisal

    • Marked as answer by DynamicsCRM31 Friday, August 2, 2013 6:13 AM
    Wednesday, July 24, 2013 7:13 AM
  • Hi,

        The main difference is the user context. When you are using filtered views, it uses the logged on user's user context. It can also happen if the report is not deployed properly. Please try to redeploy the report and see if that resolves the issue.


    Hope this helps.
     
    -----------------------------------------------------------------------
     Minal Dahiya
     
    If this post answers your question, please click "Mark As Answer" on the post and "Vote as Helpful"

    • Marked as answer by DynamicsCRM31 Friday, August 2, 2013 6:14 AM
    Wednesday, July 24, 2013 8:33 AM
  • When you upload report by default it has a filter which is modified in last 30 days. You need to edit filter of report and save it than run report it should produce the desired results.

    Regards Faisal

    • Marked as answer by DynamicsCRM31 Friday, August 2, 2013 6:14 AM
    Thursday, July 25, 2013 7:19 AM

All replies

  • Your query should look like:-

    SELECT     CRMAF_FilteredNew_enrolment.new_enrolmentdate, CRMAF_FilteredNew_enrolment.new_teacheridname, CRMAF_FilteredNew_enrolment.new_trn,
                          CRMAF_FilteredNew_verification.new_presentationreceivedfromverifier, CRMAF_FilteredNew_submission.new_name
    FROM         FilteredNew_enrolment AS CRMAF_FilteredNew_enrolment INNER JOIN
                          FilteredNew_submission AS CRMAF_FilteredNew_submission ON
                          CRMAF_FilteredNew_enrolment.new_enrolmentid = CRMAF_FilteredNew_submission.new_enrolementsubid INNER JOIN
                          FilteredNew_verification AS CRMAF_FilteredNew_verification ON
                          CRMAF_FilteredNew_submission.new_submissionid = CRMAF_FilteredNew_verification.new_verificationsubid
    WHERE     (CRMAF_FilteredNew_verification.new_presentationreceivedfromverifier IS NOT NULL)


    Regards Faisal

    • Marked as answer by DynamicsCRM31 Friday, August 2, 2013 6:13 AM
    Wednesday, July 24, 2013 7:13 AM
  • Thanks Faisal.

    Let say in my case ,if i want to fetch 2 fields in my report,ContactID and FullName then my query should look like belo.

    "select contactid,fullname from FilteredContact as CRMAF_FilteredContacts"

    Correct ?

    I have used this query and it shows some Contact Records (not all) in Report Builder.

    But when i run this report from CRM Application, it does not show me any record.

    What is wrong here?


    If this post answers your question, please click "Mark As Answer" on the post and "Mark as Helpful"

    Wednesday, July 24, 2013 7:23 AM
  • Hi,

        The main difference is the user context. When you are using filtered views, it uses the logged on user's user context. It can also happen if the report is not deployed properly. Please try to redeploy the report and see if that resolves the issue.


    Hope this helps.
     
    -----------------------------------------------------------------------
     Minal Dahiya
     
    If this post answers your question, please click "Mark As Answer" on the post and "Vote as Helpful"

    • Marked as answer by DynamicsCRM31 Friday, August 2, 2013 6:14 AM
    Wednesday, July 24, 2013 8:33 AM
  • Your query has a typo - it should be CRMAF_FilteredContact, not CRMAF_FilteredContacts - the alias matters

    Microsoft CRM MVP - http://mscrmuk.blogspot.com/ http://www.excitation.co.uk

    Wednesday, July 24, 2013 9:11 AM
    Moderator
  • Hi David,

    Thanks for the update. I came to know about and already update the query and got some output also but that is not the appropriate one.

    When i run this report from Entity Home grid ,it does not produce the selected record's report.

    Do i need to do some additional configuration or something like parameters?


    If this post answers your question, please click "Mark As Answer" on the post and "Mark as Helpful"

    Thursday, July 25, 2013 7:04 AM
  • When you upload report by default it has a filter which is modified in last 30 days. You need to edit filter of report and save it than run report it should produce the desired results.

    Regards Faisal

    • Marked as answer by DynamicsCRM31 Friday, August 2, 2013 6:14 AM
    Thursday, July 25, 2013 7:19 AM