Thursday 13 August 2015

Report and Sub-Report Error 'the subreport could not be found at the specified location. Please verify that the subreport has been published and that the name is correct'

The main culprit is one of three things:

  • The Project needs to be rebuilt as you made changes to a subreport and the report data is not current.
  • The Report data is corrupt some how and needs to be erased (look in your FILE location and delete the *.rdl.data file.)
  • Your parameter you are passing in is bad or in the wrong format. All Subreports having parameters NEED TO HAVE THEM PASSED IN, or they will not run and give an error the equivalent of 'object set to an instance of a null'.

Reference: http://stackoverflow.com/questions/17534606/error-the-subreport-could-not-be-found-at-the-specified-location-please-verify

Filter Today Date in FetchXML Report

We can filter today records using new FetchXML operator Today:

<filter type="and">
      <condition attribute="modifiedon" operator="today" />
      <condition attribute="modifiedon" operator="yesterday" />
</filter>

We can filter today date using report's built-in field execution time:

=Format(Globals!ExecutionTime,"yyyy-MM-dd")

The Execution Time is formatted as yyyy-MM-dd to be compatible with the query.

Now we can add a parameter field, named "TodayFetchXML", and set it's default value to the expression above and use it to parameterize our FetchXML like this:

<filter type="and">
      <condition attribute="scheduledstart" operator="on" value=@TodayFetchXML />
</filter>

We can filter on other dates by adding/subtracting days:

// Parameter function for @YesterdayFetchXML
=Format(DateAdd("d",-1,Globals!ExecutionTime),"yyyy-MM-dd")

// Parameter function for @TomorrowFetchXML
=Format(DateAdd("d",1,Globals!ExecutionTime),"yyyy-MM-dd")

Combining with new FetchXML date operators: on-or-before and on-or-after, we can create more flexible query

<filter type="and">
      <condition attribute="scheduledstart" operator="on-or-after" value="@TomorrowFetchXML" />
</filter>

Enable Pre Filter in SSRS Report for Dynamics CRM in FetchXML

Simply add enableprefiltering="1" attribute to entity node in FetchXML

For example:

    
        
        
        
        
    


It will create a parameter field for entity ID automatically in format CRM_
We can also set the name for the parameter field in FetchXML using prefilterparametername=""
If the report is uploaded to CRM solution without prefiltering enabled, we need to delete that report in the solution and upload prefiltering enabled report again.