locked
CRM 2013 Pre-Filtered Report error RRS feed

  • Question

  • Hi ,

    im working on SSRS report in Dynamics CRM 2013 ( Dynamics CRM 2013 SP1 update rollup 1 on-premise )  in SQL Server Data Tools.

    I would like to iclude account prefiltering in my report.

    I tried CRMAF alias:  

    select name from filteredaccount as CRMAF_FilteredAccount

    and also explicit pre-filtering:

    	declare @sql varchar (max)
    
    	set @sql = 'select name as [StoreID] from ('+@CRM_FilteredAccount+')as Acc'
    
    	exec (@sql)

    When I execute the report from report area  filter is set do default value.

    But when i execute the report from the account view - run report on the selected reports, or from  account  form i recieve following error on report loading:

    "There are some clauses that have error in the please remove them and try again"

    _https://social.msdn.microsoft.com/Forums/getfile/611382

    _https://social.msdn.microsoft.com/Forums/getfile/611383

    When i remove the condition with error from the filter and set any condition report is displayed correctly.

    Did you experience something similar or can you please provide some workaround ?

    For me it seems as CRM 2013 bug.

    Thanks in advance.


    Friday, February 13, 2015 9:05 AM

Answers

  • Hi,

    issue is solveed ...the error was caused by the fact that primary key of Account entity wasn't searchable ( so it wasn't available in advanced find for prefiltering ).

    Setting of the searchable to yes solved the issue.

    Monday, February 16, 2015 1:50 PM

All replies

  • Hi ,

    im working on SSRS report in Dynamics CRM 2013 ( Dynamics CRM 2013 SP1 update rollup 1 on-premise )  in SQL Server Data Tools.

    I would like to iclude account prefiltering in my report .

    I tried CRMAF alias:  

    select name from filteredaccount as CRMAF_FilteredAccount

    and also explicit pre-filtering:

    	declare @sql varchar (max)
    
    	set @sql = 'select name as [StoreID] from ('+@CRM_FilteredAccount+')as Acc'
    
    	exec (@sql)

    When I execute the report from report area  filter is set do default value.

    But when i execute the report from the account view - run report on the selected reports, or from  account  form i recieve following error on report loading:

    "There are some clauses that have error in the please remove them and try again"

    _https://social.msdn.microsoft.com/Forums/getfile/611382

    _https://social.msdn.microsoft.com/Forums/getfile/611383

    When i remove the condition with error from the filter and set any condition report is displayed correctly.

    Did you experience something similar or can you please provide some workaround ?

    For me it seems as CRM 2013 bug.

    Thanks in advance.

    Friday, February 13, 2015 9:24 AM
  • Hello,

    Please don't create duplicated threads.

    I would suggest to turn on trace, restart IIS and re-run report that causes an error - that would provide you an answer why error happens. In case CRM trace would not give you an answer you can recheck reporting traces as well.


    Dynamics CRM MVP/ Technical Evangelist at SlickData LLC
    My blog

    Friday, February 13, 2015 10:51 AM
    Moderator
  • Hi,

    i turn on trace on CRM as you suggested..but logs didnt show anything - same  with ssrs logs.

    I tried to run both reports on anther CRM installation : CRM 2013 without any update rollup  and without service pack 1 installed

    select name from filteredaccount as CRMAF_FilteredAccount

    and also explicit pre-filtering:

    	declare @sql varchar (max)
    
    	set @sql = 'select name as [StoreID] from ('+@CRM_FilteredAccount+')as Acc'
    
    	exec (@sql)

    and they worked correctly.

    But, when i executed it from Account view on CRM server with Update Rollup 1 for Microsoft Dynamics CRM 2013 Service Pack 1 , i see alert message when loading the report : "There are some clauses that have errors in them please remove them and try again".

    Monday, February 16, 2015 8:20 AM
  • Hi,

    issue is solveed ...the error was caused by the fact that primary key of Account entity wasn't searchable ( so it wasn't available in advanced find for prefiltering ).

    Setting of the searchable to yes solved the issue.

    Monday, February 16, 2015 1:50 PM