Adding a Custom Filter to a Dashboard Gadget in Sage CRM

Custom Filters, Interactive Dashboards, and Sage CRM

 

Since the inception of the Interactive Dashboard, Sage CRM has evolved with the current trend of reporting on demand. Now learn how to add a custom filter to a dashboard gadget in Sage CRM.

No longer are reporting needs satisfied by hours of data compilation and distribution. Today’s users can simply access data from predefined Dashboards that are as up-to-date as the data that they are comprised of. Dashboards typically consist of a number of report gadgets and data can be displayed using a number of different gadget types.

The simplest and most common gadget type is called a List gadget, where data can typically be filtered using any of the fields that are present within the gadget. For example, using the example below, the 107 Communication records can be filtered down to a smaller list if the user chooses to Filter by, “Company Name” and then proceeds to enter a Company Name, followed by selecting the “Go” button.

 

Sage CRM Adding a Custom Filter to a Dashboard Gadget - Picture 1

 

There are a couple of exceptions to the above statement. CRM fields that have a type of Search Select Advance or User Select are excluded from the filter. This can present a challenge with reporting using the Interactive Dashboard. Using the above example, perhaps there is a business need to display a listing of all Pending Communications that the Current User created and assigned to other users. Adding the ability to display only records where the Created By {comm_createdby} equals the current user is not possible using the native tools within Sage CRM.

Let’s explore a work around to the above business need.

The root of all Dashboard Gadget and Reports within Sage CRM are MS SQL Database Queries. These queries can be copied and/or modified within Sage CRM to accommodate for the above challenge to the application. Using the example below, we copied the logic defined in the vReportCommunication database view and relabeled it as vCommReport. This approach allows us to experiment with a new database query without the risk of damaging other reports and/or gadgets that rely on the present integrity of the vReportCommunication query.

As you can see from the example below, we have included an additional database join where we are linking the Users Tables to the Communications table (red highlight). This approach allows us to expose the First and Last Name of the User by adding an alias field called comm_cb (orange highlight).

 

Sage CRM Adding a Custom Filter to a Dashboard Gadget - Picture 2

 

In addition, in order to utilize the alias field within the application, the field must be added to the entity within Sage CRM. In this situation, the comm_cp field was added to the Communication Entity as an Entry Type of Text.

 

Sage CRM Adding a Custom Filter to a Dashboard Gadget - Picture 3

 

Finally, once the Comm. Created By {comm_cb} field has been added to the report contents, it will then be accessible as a “Filter by” field, within the report Gadget.

Note: the “Filter by” field within each report gadget will store the User Specified Preference. Using the example below, if a user enters “Steph” and then selects the “Go” button, CRM will always remember this preference and the gadget will remain with this setting for this user.

 

Sage CRM Adding a Custom Filter to a Dashboard Gadget - Picture 4