Building Sage CRM Groups and Reports

How SQL Views Influence Your Sage CRM Groups & Reports

All Sage CRM Groups and Reports are built on SQL views. That sounds straightforward, but if you’re not the one writing the views, it can be difficult to decide which view to choose. Here we’ll talk about some strategies for choosing the best view for Group and Report building.

When creating Sage CRM Groups or Reports, you’ll be prompted to select the associated view. Here we’re creating a new marketing group, “Marketing – Prospects for Green Tunics.” A list of associated “Source View” options appears.

But at this stage it’s not clear what you will find behind each of the views. They are not alphabetized, and there is no obvious glossary. For this marketing list, we might choose “Person Summary.” We are likely to find fields that are stored at the Person entity that relate to their prospect type and whether they are interested in green tunics. (This assumes you’ve been profiling apparel data in your CRM system.)

But we want to be more sure of which fields are associated with the “Person Summary” view. To understand that, and the logic behind each view, you need to do a little more investigation.

There are 2 ways you could do this.

#1 – the Easy Way

Start making your Sage CRM Groups and Reports, select a view, and then look at the fields available in the “Select Column” box. That tells you all the columns (aka fields) that are available in the SQL view.  For example, here we see that “Person Summary”includes fields from Address, Person, and Company entities.

#2 The Hard Way

The hard way is not that hard, but should be attempted with sobriety. This way is to navigate to the views themselves within Sage CRM. You need Administrator-level rights to do this. To see the Person Summary view, we navigate to Administration>Customization>Person. There we see a “Views” tab. This stores all the views that are part of the person entity. Some of them are leveraged in reports and groups, and others build lists and screens.

This is why this method involves sobriety. Never edit these without the support of your Sage business partner.

To review a view, click on the view that interests you and paste it to a text editor like Notepad. There, you can analyze what’s happening in the SQL view without disrupting any Sage CRM experiences. The advantage of this approach is that you can more easily see the logic behind the view. The logic is included in the “FROM” and “WHERE” clauses that tell us more about how the tables are joined and what the filters are.

Both of these methods can be fun to explore with your Sage business partner.

Naming Views

The best way to make it easier for people building Sage CRM Groups and Reports in CRM is to give views good, descriptive names. Here’s an example of a custom view that shows Person records in the system that have been duplicated. The custom view has a “Translation,” shown here. The translation is what appears to the group builder when they select a view. In this case, we translate “vdqPersDupeRpt” as “vdqPersonDuplicates.” Ideally that makes it easier to choose the correct view for Sage CRM Groups and Reports.

We hope these tips are helpful – enjoy your SQL journey and good luck with the reports and group building!