locked
How to just displayed selected record in SQL Report in Dynamic CRM 2011 RRS feed

  • 問題

  • Dear all, 

    Now im working on SSRS report and query data by using SQL

    But just want to report the selected record in the grid.

    But it' seem always load all record, so how can I just displayed selected record by using SQL query

    Many thanks,


    If my question/answer can help you, please give me a points :) ----You may be disappointed if you fail, but you are doomed if you don't try---

    2013年1月5日 上午 08:11

解答

  • Hi,

    You need to implement Pre filtering in report. Just add two things in your report.
    1. In your SQL Query add "CRMAF_" alias to the filtredview your are using in your report. Aliasing the Filtered View with a prefix of CRMAF_ will allow CRM to recognize that you would like to enable this entity for pre-filtering.
    2. While deploying the report select the entity you want to display in your report and the Report Display Area. (This is important as you want to run the report just for one record/Selected records)


    Run the report in CRM. 
    1. open the record and run the report 
    2. select any record from the grid view and run the report.
    3. If you run the report from Report section then report will pick first 10 records for that entity to show the report.

    Find More details on Pre filtering in report in below post.
    http://mscrm-chandan.blogspot.in/2009/08/crm-pre-filtering.html

    2013年1月5日 下午 12:38
  • Hi all,

    In case in my SQL query that i have union, left join command or need filter in many times. 

    So the solution in this case is create a parameter.

    Example you want report just selected record:

    1. Create a parameter report like CRM_ContactIds

    2. Follow the step like Chandan kumar Choudhary above to query selected Contact in the CRM into CRM_ContactIds parameter

    3. Use CRM_ContactIds in your complex query many times as you want.

    Ex:

    SELECT ...... FROM FilteredContact as C, FilteredAccount as A 

    WHERE C.number IN (@CRM_ContactIds) AND A.number IN (@CRM_ContactIds)



    If my question/answer can help you, please give me a points :) ----You may be disappointed if you fail, but you are doomed if you don't try---


    • 已標示為解答 AnyMi 2013年1月7日 上午 07:05
    • 已編輯 AnyMi 2013年1月7日 上午 07:06
    2013年1月7日 上午 07:05

所有回覆

  • Hi,

    You need to implement Pre filtering in report. Just add two things in your report.
    1. In your SQL Query add "CRMAF_" alias to the filtredview your are using in your report. Aliasing the Filtered View with a prefix of CRMAF_ will allow CRM to recognize that you would like to enable this entity for pre-filtering.
    2. While deploying the report select the entity you want to display in your report and the Report Display Area. (This is important as you want to run the report just for one record/Selected records)


    Run the report in CRM. 
    1. open the record and run the report 
    2. select any record from the grid view and run the report.
    3. If you run the report from Report section then report will pick first 10 records for that entity to show the report.

    Find More details on Pre filtering in report in below post.
    http://mscrm-chandan.blogspot.in/2009/08/crm-pre-filtering.html

    2013年1月5日 下午 12:38
  • What's the selection criterion?

    If you find this post helpful then please "Vote as Helpful" and "Mark As Answer". Amreek Singh Senior Solution Architect HP Australia Sydney, Australia http://mscrmshop.blogspot.com http://crm2011usersettings.codeplex.com

    2013年1月5日 下午 12:38
  • Hi all,

    In case in my SQL query that i have union, left join command or need filter in many times. 

    So the solution in this case is create a parameter.

    Example you want report just selected record:

    1. Create a parameter report like CRM_ContactIds

    2. Follow the step like Chandan kumar Choudhary above to query selected Contact in the CRM into CRM_ContactIds parameter

    3. Use CRM_ContactIds in your complex query many times as you want.

    Ex:

    SELECT ...... FROM FilteredContact as C, FilteredAccount as A 

    WHERE C.number IN (@CRM_ContactIds) AND A.number IN (@CRM_ContactIds)



    If my question/answer can help you, please give me a points :) ----You may be disappointed if you fail, but you are doomed if you don't try---


    • 已標示為解答 AnyMi 2013年1月7日 上午 07:05
    • 已編輯 AnyMi 2013年1月7日 上午 07:06
    2013年1月7日 上午 07:05