Implementing Dynamic Filters Using Drop-Down Lists in SSRS for Dynamics 365

 

Introduction

In this guide, we’ll show how to set up dynamic filters using drop-down lists in SSRS reports for Dynamics 365. This method enhances report flexibility and user interaction by allowing end-users to select different fields and date ranges dynamically.

Steps to Implement Dynamic Filters

1. Create a Date Field Parameter

• Parameter Name: DateField

• Data Type: Text

• Available Values: Define the list of fields that users can filter by, such as "Created On" and "Modified On". Assign each option a value, like 1 for "Created On", 2 for "Modified On", etc.

  Example values:

  • Label: Created On, Value: 1
  • Label: Modified On, Value: 2

  Null values are not allowed to ensure each report execution has a defined context.

2. Create Date/Time Parameters for Filtering

• Parameters: From,  To
• Data Type: Date/Time
• Allow null values: Yes
• These parameters allow users to specify the date range for the selected date field.

3. Setup Invisible Date Parameters for Each Field

For each date field, such as "Created On", create two hidden parameters to manage the date ranges internally:

• Parameters: CreatedOnFrom, CreatedOnTo

• Data Type: Date/Time

• Visibility: Set to Hidden

• Allow null values: No

  Default and Available Values for CreatedOnFrom:

=IIF(Parameters!DateField.Value = 1 AND IsNothing(Parameters!From.Value), CDate("1900-01-01"), Parameters!From.Value)

          Default and Available Values for CreatedOnTo:

=IIF(Parameters!DateField.Value = 1 AND IsNothing(Parameters!To.Value), Today(), Parameters!To.Value)

Note:

If you want to set CreatedOnTo to today's last minute if To field is null or empty, can use below expression:




=IIF(Parameters!DateField.Value = 1 AND IsNothing(Parameters!To.Value), DateAdd("s", -1, DateAdd("d", 1, Today())), Parameters!To.Value)

4. Modify the FetchXML Query

Integrate the date filters into your dataset's FetchXML query. Use the hidden parameters to apply the conditions dynamically based on the user's selection:

<condition attribute="createdon" operator="on-or-after" value="@CreatedOnFrom" />

<condition attribute="createdon" operator="on-or-before" value="@CreatedOnTo" />

Replace createdon with the appropriate field based on the DateField parameter selection in your actual implementation.

Conclusion

Implementing dynamic filters with drop-down lists allows users to customize SSRS reports extensively in Dynamics 365. This setup provides a flexible framework for generating reports that adapt to different user needs and scenarios.