Skip to main content

HSD’s Dynamics Technical Consultant, Rohan Perera gives an overview of how to include additional Blank option for Parameters in D365 SSRS FetchXML reports.

Previously when we use SSRS report in CRM, we are using SQL to get the data for parameters, so we could easily add additional values (like ‘BLANK’). Then we can use this ‘BLANK’ value in filtering criteria. With SSRS FetchXML report dynamic Parameters, we cannot insert a new value to them.

For this sample report, we wants to get all the Active records created between given dates and for the selected State(s). Optionally users can select to get the records without any value for the State as well.

Assume that we have three parameters.

  • From
    • This is a Date/Time parameter and the default value is less than a month from today.
  • To
    • This is a Date/Time parameter and the default value is today’s date.

We need to add a new parameter and determine that users wants to display State(s) with blank values as well. The issue is we cannot add a condition to check just the selected parameter value. We have to check the selected parameter against an attribute. The idea is to create always true or always false condition based on this parameter and use it within the query.

Resolution: We can select an attribute that always is populated and have some predictable values. We think the best attribute is to use the “createdon’ field because it is always get populated with the current date and time.

We create another Parameter called ‘Include Blank States’:

Name:  IncludeBlankStates

  • Prompt: Include Blank States
  • Available Values
    • Label : Yes              Value: 1800-01-01
    • Label : No               Value: 9999-01-01
  • Add the Default value as 9999-01-01 (No)

Please note these values will be automatically formatted according to the time zone selected for the report by the Visual Studio.

D365 SSRS

Parameters will look like in design:

Creating the filter criteria:
Here we have two conditions with OR filter type.

  1. Get all the records for the selected parameters only
  2. If user selected Yes to ‘Include Blank States’ then include records with blank State(s) as well
  <filter type="or">
        <filter type="and">
          <condition attribute="statecode" operator="eq" value="0" />
          <condition attribute="new_addressstate"  operator="in" value="@States" />
          <condition attribute="createdon" operator="on-or-after" value="@From" />
          <condition attribute="createdon" operator="on-or-before" value="@To" />
        </filter>
        <filter type="and">            
            <condition attribute="createdon" operator="gt"  value="@IncludeBlankStates" />	
            <condition attribute="statecode" operator="eq" value="0" />
            <condition attribute="new_addressstate" operator="null" />
            <condition attribute="createdon" operator="on-or-after" value="@From" />
            <condition attribute="createdon" operator="on-or-before" value="@To" />
        </filter>
   </filter>

The default value ‘No’ for IncludeBlankStates is always less than the createdon value and will fail the condition (always false).

If users selected ‘Yes’ to IncludeBlankStates then it will be greater than createdon value and will pass the condition (always true).

Make sure you check out more of HSD’s blog posts by visiting https://www.hsd.com.au/blog/ and follow us on LinkedIn for up-to-date activity from HSD.