Sage CRM - Using Views to Enhance Report Writer, Part 1

Enabliser Nathalie

There are many ways in Sage CRM in which we can tailor what we show in our reports. Using Views is one good and relatively simple way. Here are two examples

  1. Use “Group By” in the view to display only summary in the report.

Scenario Overview

You want a report that only shows total Cases created by a user in a given period, without listing all cases.

How to do it
Using the standard report, you can add a chart to show the cases created by User in the period and add the chart to the dashboard. This is great, but when viewing the report, you will still see the list of all cases.

One solution is to create a new View in CRM that groups the cases by the assigned user and maybe by day/month created.

The easiest way to create the view is to use SQL Management Studio to work out the exact syntax and test the data returned and then copy the SELECT statement in the CRM View editor.

Tick the view as a Report view.

Create the report based on the view

  1. Use UNION to show disparate records in one report

Scenario Overview
You want to include in the same report leads and prospect companies.

How to do it
Create a view to Union leads and prospect companies

SELECT lead_leadid, lead_companyname, lead_channelid, lead_createdby, lead_secterr,
lead_personfirstname, lead_personlastname FROM lead
WHERE lead_deleted IS NULL
UNION ALL
SELECT comp_companyid, comp_name, comp_channelid, comp_createdby, comp_secterr, pers_firstname, pers_lastname
FROM company JOIN person ON comp_primarypersonid = pers_personID
WHERE pers_deleted IS NULL AND comp_deleted IS NULL AND comp_type = 'Prospect'
This will return a list of leads and prospects in a single report.

The main trick in a UNION statement is that each part of the UNION needs to have the same number of columns and the data type of each column must match (so you cannot have a lead_createddate as the first field in the first statement of the Union and a comp_createdby as the first field in the second statement)

Once the view is ticked as a report, you can create the report with the usual CRM report writer.

Add new comment