locked
Enable pre-filtering in SQL based Reports RRS feed

  • Question

  • Hi All,

    I have created a custom SQL based report on Invoice entity.

    It takes Invoice Id as input parameter and generates the report.

    I am having following troubles:

    1. I want to show the report under "Run Report" button in Reports area and all Invoice records.

    2. If I select an Invoice record, then report should run for that record.

    Any help is welcome. I am short of time. Please contribute your suggestions.

    Thanks in Advance,

    Regards,

    Tanu.


    Tanu Goyal

    Tuesday, January 7, 2014 3:49 PM

Answers

  • First: you should use the filtered views, not the base tables and use the alias CRMAF_<FilteredView> - e.g.

    from FilteredInvoice as CRMAF_FilteredInvoice
    Second: The pre-filtering will apply the filtering, therefore you should not use a separate parameter for the Inovice Id


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

    • Marked as answer by Tanu.goyal Friday, January 24, 2014 11:45 AM
    Wednesday, January 8, 2014 6:42 AM
    Moderator
  • Hi Tanu

    Filteredviews are views that are created by Default by crm.
    By using them instead of tables you have quite a bunch of additional Features (provided by the CRM Dataconnector).
    Eg.:
    - Using Filteredviews automatically filters the returned data for the executing user
    '- This means if you use the permission System in CRM your User will only see data in the Report that he could access as well in CRM
    - Filteredviews also enable you to use prefiltering. You can not use prefiltering with tables (as far as I know), only with filteredviews

    Have a look at your organizations database in SQL Management Studio and open the section "Views".
    You will find a huge bunch of views. Some of them are filteredviews (indicated by the Name).
    Please also notice that using filteredviews is best practice for reports in CRM anyway.

    -> Use the Filteredview for invoice in your query instead of the InvoiceBase table. This will fix your problem.

    Regards,
    Pascal

    • Marked as answer by Tanu.goyal Friday, January 24, 2014 11:46 AM
    Wednesday, January 8, 2014 7:41 AM
  • Well I suggest you use them if possible.

    This will also simplify your queries as the filteredview always joins the base and extension table. If you create an entity crm always creates two tables in the DB. One table will be the basetable. this table contains the same fields on every entity. The extenssion table contains your custom fields. The extension table references the base table (Entity PK is stored in base table).
    You will no longer have to join InvoiceDetailBase and InvoiceDetailExtensionBase as they are joined already in you filterdview for invoices.

    @Riaz:The reason for this is, that the dataconnector only rewrites the query for the first select statement. The statement after UNION will not be modified and therefor return unfiltered results from the view.

    • Edited by P. Krüttli Wednesday, January 8, 2014 9:09 AM
    • Marked as answer by Tanu.goyal Friday, January 24, 2014 11:46 AM
    Wednesday, January 8, 2014 9:04 AM

All replies

  • Hello,

    Recheck following article please - http://msdn.microsoft.com/en-us/library/gg328288.aspx#SQLBasedPre_Filtering


    Dynamics CRM MVP/ Technical Evangelist at SlickData LLC
    My blog

    Tuesday, January 7, 2014 4:53 PM
    Moderator
  • Hi

    - You have to use Filtered Views in your query. (eg. SELECT * FROM FILTEREDINVOICE)
    - You have to use an Alias for the filteredview you want to prefilter that usese the "CRMAF_" prefix (eg. SELECT * FROM FILTEREDINVOICE AS CRMAF_INVOICE)
    - You have to set invoice as related entity for the Report when you upload it and anable execution on lists and/or forms

    -> If you did this all correct try to delete the Report on CRM and readd it. Somtimes CRM has Troubles updating an existing report

    Hobe that helps

    Tuesday, January 7, 2014 6:33 PM
  • First: you should use the filtered views, not the base tables and use the alias CRMAF_<FilteredView> - e.g.

    from FilteredInvoice as CRMAF_FilteredInvoice
    Second: The pre-filtering will apply the filtering, therefore you should not use a separate parameter for the Inovice Id


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

    • Marked as answer by Tanu.goyal Friday, January 24, 2014 11:45 AM
    Wednesday, January 8, 2014 6:42 AM
    Moderator
  • Hi David,

    Thanks for the reply.

    I am new to SSRS Reports. What is meant by filtered view? How to convert my Query 1 and Query 2 into filtered view?


    Tanu Goyal

    Wednesday, January 8, 2014 6:46 AM
  • Hi Tanu

    Filteredviews are views that are created by Default by crm.
    By using them instead of tables you have quite a bunch of additional Features (provided by the CRM Dataconnector).
    Eg.:
    - Using Filteredviews automatically filters the returned data for the executing user
    '- This means if you use the permission System in CRM your User will only see data in the Report that he could access as well in CRM
    - Filteredviews also enable you to use prefiltering. You can not use prefiltering with tables (as far as I know), only with filteredviews

    Have a look at your organizations database in SQL Management Studio and open the section "Views".
    You will find a huge bunch of views. Some of them are filteredviews (indicated by the Name).
    Please also notice that using filteredviews is best practice for reports in CRM anyway.

    -> Use the Filteredview for invoice in your query instead of the InvoiceBase table. This will fix your problem.

    Regards,
    Pascal

    • Marked as answer by Tanu.goyal Friday, January 24, 2014 11:46 AM
    Wednesday, January 8, 2014 7:41 AM
  • Hi P.Kruttli,

    Thankyou for your reply.

    Do i have to use the filtered view for all tables that are used in my Queries or just for InvoiceBase?


    Tanu Goyal

    Wednesday, January 8, 2014 7:44 AM
  • Hi

     CRMAF_ prefix  enables 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 it has some problem like unexpected result.

    view link suggested by Andrii.

    • Edited by Riaz Usmani Wednesday, January 8, 2014 8:21 AM
    Wednesday, January 8, 2014 8:17 AM
  • Well I suggest you use them if possible.

    This will also simplify your queries as the filteredview always joins the base and extension table. If you create an entity crm always creates two tables in the DB. One table will be the basetable. this table contains the same fields on every entity. The extenssion table contains your custom fields. The extension table references the base table (Entity PK is stored in base table).
    You will no longer have to join InvoiceDetailBase and InvoiceDetailExtensionBase as they are joined already in you filterdview for invoices.

    @Riaz:The reason for this is, that the dataconnector only rewrites the query for the first select statement. The statement after UNION will not be modified and therefor return unfiltered results from the view.

    • Edited by P. Krüttli Wednesday, January 8, 2014 9:09 AM
    • Marked as answer by Tanu.goyal Friday, January 24, 2014 11:46 AM
    Wednesday, January 8, 2014 9:04 AM
  • Hi All,

    1. I was able to enable pre-filtering, but my second input parameter is dependent on "select_invoice" parameter.

    How to handle that?

    2. After enabling pre-filtering, the report was running only on invoives which were "modified in last 30 days". I removed default filter from both main and sub-           report. Even then the report is not running for other invoice records.

    Please guide. I am stuck. Need a solution at earliest.

     


    Tanu Goyal

    Friday, January 24, 2014 11:49 AM