locked
CRM 2011 Report not removing default filter on sub-reports RRS feed

  • Question

  • Hi,

    My parent report is attaching a filter to my sub report and I cannot remove it.

    I have two reports.  One of them simply runs the other one in a subreport grid.  I've added them both to CRM and removed the default filters for both.  If I run the second (sub) report on its own from the reports screen and manually enter an ID, it runs fine and does not include any default filter.  I have run SQL profiler on the database to see what query is being run, and I have included it here in its entirety so as not to omit anything;

    exec sp_executesql N'declare @binUserGuid varbinary(128)
    											declare @userGuid uniqueidentifier
    											select @userGuid = N''{2a063b3e-fd04-e311-983f-00155d14f2a4}''
    											set @binUserGuid = cast(@userGuid as varbinary(128))
    											set context_info @binUserGuid;
    
    											SELECT *
      FROM (
                    SELECT  CRMAF_FilteredEmail.activityid AS ActivityId, CRMAF_FilteredEmail.subject AS Subject, CRMAF_FilteredEmail.category AS Category, CRMAF_FilteredEmail.subcategory AS SubCategory, CRMAF_FilteredEmail.description AS Description, 
                          CRMAF_FilteredEmail.regardingobjectidname AS RegardingObjectIdName, CRMAF_FilteredEmail.owneridname AS OwnerIdName, CRMAF_FilteredEmail.actualdurationminutes AS DurationMinutes, CRMAF_FilteredEmail.prioritycodename AS PriorityCode, 
                          CRMAF_FilteredEmail.scheduledend AS ScheduledEnd, CRMAF_FilteredEmail.actualend AS ActualEnd, dbo.GetPartyPeople(CRMAF_FilteredEmail.activityid, ''To Recipient'') AS ToRecipients, dbo.GetPartyPeople(CRMAF_FilteredEmail.activityid, ''Sender'') AS Sender,
    dbo.GetPartyPeople(CRMAF_FilteredEmail.activityid, ''BCC Recipient'') AS BccRecipients,
    dbo.GetPartyPeople(CRMAF_FilteredEmail.activityid, ''CC Recipient'') AS CcRecipients,
                            ROW_NUMBER() OVER(PARTITION BY [Subject] ORDER BY [ActualEnd] DESC) rn
                        FROM (select 
    [email0].* 
    from
     FilteredEmail as "email0") as CRMAF_FilteredEmail                    WHERE (CRMAF_FilteredEmail.regardingobjectid = CONVERT(uniqueidentifier, @objectid))
    
                  ) a
    WHERE rn = 1',N'@objectid nvarchar(36)',@objectid=N'4C5A920C-457F-E211-99E8-00155D14CEB5'
    
    

    Now when I run the first report (parent) which simply calls this sub report and passes in the ID, the following SQL is run and as you can see there is now suddenly a filter on it which restricts items to the last 30 days (fn_beginoflastxday).  This was the default filter that I removed, but it always appears again whenever the report is run as a sub report.  The filter definitely does not exist on either of my reports;

    exec sp_executesql N'declare @binUserGuid varbinary(128)
    											declare @userGuid uniqueidentifier
    											select @userGuid = N''{2a063b3e-fd04-e311-983f-00155d14f2a4}''
    											set @binUserGuid = cast(@userGuid as varbinary(128))
    											set context_info @binUserGuid;
    
    											SELECT *
      FROM (
                    SELECT  CRMAF_FilteredEmail.activityid AS ActivityId, CRMAF_FilteredEmail.subject AS Subject, CRMAF_FilteredEmail.category AS Category, CRMAF_FilteredEmail.subcategory AS SubCategory, CRMAF_FilteredEmail.description AS Description, 
                          CRMAF_FilteredEmail.regardingobjectidname AS RegardingObjectIdName, CRMAF_FilteredEmail.owneridname AS OwnerIdName, CRMAF_FilteredEmail.actualdurationminutes AS DurationMinutes, CRMAF_FilteredEmail.prioritycodename AS PriorityCode, 
                          CRMAF_FilteredEmail.scheduledend AS ScheduledEnd, CRMAF_FilteredEmail.actualend AS ActualEnd, dbo.GetPartyPeople(CRMAF_FilteredEmail.activityid, ''To Recipient'') AS ToRecipients, dbo.GetPartyPeople(CRMAF_FilteredEmail.activityid, ''Sender'') AS Sender,
    dbo.GetPartyPeople(CRMAF_FilteredEmail.activityid, ''BCC Recipient'') AS BccRecipients,
    dbo.GetPartyPeople(CRMAF_FilteredEmail.activityid, ''CC Recipient'') AS CcRecipients,
                            ROW_NUMBER() OVER(PARTITION BY [Subject] ORDER BY [ActualEnd] DESC) rn
                        FROM (select  [email0].*  from  FilteredEmail as "email0" join (select top 1 dbo.fn_BeginOfLastXDay(GetUTCDate(), 30) as modifiedon1,GetUTCDate() as modifiedon2 order by modifiedon1,modifiedon2) as modifiedondtc on 1=1  where  ( email0.modifiedonutc >= modifiedondtc.modifiedon1 and email0.modifiedonutc <= modifiedondtc.modifiedon2 )) as CRMAF_FilteredEmail                    WHERE (CRMAF_FilteredEmail.regardingobjectid = CONVERT(uniqueidentifier, @objectid))
    
                  ) a
    WHERE rn = 1',N'@objectid nvarchar(36)',@objectid=N'4c5a920c-457f-e211-99e8-00155d14ceb5'
    

    Any help or pointers for this would be appriciated as I am completely stuck.


    Tuesday, October 7, 2014 3:55 PM

All replies

  • hello,

    please check your dataset from reports designer. There will be a parameter called CRM_filteredtext passed into the dataset. Please check the value of this by printing it to a text box on the header /footer of the report.

    see if this parameter is what is getting added.

    regards

    Jithesh

    Tuesday, October 7, 2014 8:13 PM
  • Hi,

    I have no parameter being passed in visible in report designer.  I checked both reports. There is also nothing in the XML.

    Wednesday, October 8, 2014 2:51 PM
  • Hi Creative,

     Please post the actual query that you use in the Dataset (subreport only) where you see the new parameter "fn_beginoflastxday".

    Also please confirm that the sub report does not have a parameter specified here. I wish to take a look. if you do not mind, please send me both the rdl files to kjithesh @y7mail.com (without space), I will double check.

    Also sometimes, most reliable option is to upload the report from report manager rather than from CRM (some cache issue I suppose).

    Regards,

    Jithesh

    Thursday, October 9, 2014 7:44 AM
  • Hi,

    Here is the subreport query I use.  As you can see I do not have that last 30 days where clause in it.  It runs fine on its own, just not when it is run as a sub report.

    SELECT *
      FROM (
                    SELECT  CRMAF_FilteredEmail.activityid AS ActivityId, CRMAF_FilteredEmail.subject AS Subject, CRMAF_FilteredEmail.category AS Category, CRMAF_FilteredEmail.subcategory AS SubCategory, CRMAF_FilteredEmail.description AS Description, 
                          CRMAF_FilteredEmail.regardingobjectidname AS RegardingObjectIdName, CRMAF_FilteredEmail.owneridname AS OwnerIdName, CRMAF_FilteredEmail.actualdurationminutes AS DurationMinutes, CRMAF_FilteredEmail.prioritycodename AS PriorityCode, 
                          CRMAF_FilteredEmail.scheduledend AS ScheduledEnd, CRMAF_FilteredEmail.actualend AS ActualEnd, dbo.GetPartyPeople(CRMAF_FilteredEmail.activityid, 'To Recipient') AS ToRecipients, dbo.GetPartyPeople(CRMAF_FilteredEmail.activityid, 'Sender') AS Sender,
    dbo.GetPartyPeople(CRMAF_FilteredEmail.activityid, 'BCC Recipient') AS BccRecipients,
    dbo.GetPartyPeople(CRMAF_FilteredEmail.activityid, 'CC Recipient') AS CcRecipients,
                            ROW_NUMBER() OVER(PARTITION BY [Subject] ORDER BY [ActualEnd] DESC) rn
                        FROM FilteredEmail AS CRMAF_FilteredEmail
                       WHERE (CRMAF_FilteredEmail.regardingobjectid = CONVERT(uniqueidentifier, @objectid))
    
                  ) a
    WHERE rn = 1


    • Edited by Creatives Thursday, October 9, 2014 8:05 AM
    Thursday, October 9, 2014 8:05 AM
  • hi,

    can you please check the guid used on the parent report to link the child report is same as the report you are trying?.

    also please export the parent and child report and try to import it as 2 new report and see if it fixes the reports issue.

    please let me know the outcome. Unless I have your rdl files and the stored procedures that you have used, I will not be able to check further.

    regards

    Jithesh

    Thursday, October 9, 2014 11:55 AM