Sage CRM Tips & Tricks – Reports PART 2 - Aptus Business Solutions
Aptus Business Solutions specialise in delivery and supporting solutions for Sage X3, Sage 300, Sage CRM and Sage Intacct. With over 30 years of knowledge, we have been supporting clients from around Australia and internationally. We cater to businesses of all sizes and business areas. Our services, support and business systems understanding enable clients to succeed and grow in their industry. We cover all aspects of system design, business processes, consultancy, hosting and IT, development and systems integration.
ERP, Consultants, Sage, Finance Management, Accounts Management, Sage 300, Sage X3, Sage Intacct, Sage Enterprise Manager, Sage CRM, Finance software, Accounts software, Business Solutions, Business Systems
16346
post-template-default,single,single-post,postid-16346,single-format-standard,ajax_fade,page_not_loaded,,side_area_uncovered_from_content,transparent_content,qode-child-theme-ver-1.0.0,qode-theme-ver-16.7,qode-theme-bridge,wpb-js-composer js-comp-ver-5.5.2,vc_responsive

Sage CRM Tips & Tricks – Reports PART 2

Sage CRM Tips & Tricks – Reports PART 2

As we’ve seen in Part 1 of our Sage CRM Report Blog, Views are where the data comes from. It is therefore essential to understand them and know how to get the best out of them.

By now you will realise that a column you need might not be available for a report when you try to select it. This is when you need to modify or create the View the report is based on. (Note: you will need the proper access rights to do this)

Click on the Profile Menu (top right corner of the main Sage CRM screen) then on Administration and from there on Customisation.

The screen will give you a list of Primary Entities from where you can customise views. Depending on your setup, you can also find useful views in Secondary Entities that appear in a drop-down below. You will need to explore this screen a bit if you want to find the right view.

Once you’ve selected an entity you will see a contextual menu that will give you access to this entity’s views:

Next you can click on the view you want to modify or create a new one by clicking New. System Views (in the View Type column) should not be modified as they impact many areas of the system. Although there is no direct way to copy a view, you can open an existing one, take a memo of its parameters, and create a new identical view with a different name.

At its core, a view is a SQL database query, so some SQL knowledge is necessary to modify or create one. If you face difficulties, feel free to contact an Aptus consultant as we will be happy to assist you.

However, here are a few tips that could save you a lot of time.

Missing columns

Views will always look something like this :

CREATE VIEW {name of your view} AS {SELECT…FROM statement}

One key point to remember when you are missing a column: it may already exist in one of the tables used by the view but be restricted by the SELECT statement.

For example, if you have this View on the Person table:

SELECT FirstName , LastName FROM Person

Then your view will only return the first and last name of a Person. In order to get all possible columns in the table you should change it to:

SELECT * FROM Person

You will then be able to see all the columns when building a report on this view. Once happy, you can get back to your view and restrict it to the columns you used in the report, to make processing a bit more efficient.

Case

Very often, it is useful to have a column that is derived from existing SQL columns, but not equal to its value. The CASE keyword can be very helpful here. Consider this example :

(
CASE WHEN client = 'Y' THEN sys_version
ELSE '' END
) as Version ,

 

This creates a column called Version that will hold the sys_version column in case the client column is Y and be empty otherwise. There is no need to get both columns if they’re only used in that context. This can save a lot of clutter on the final report.

Joins

It’s likely that you will need to retrieve data from several tables when building your views. This is done through joins, that can be easy to understand but hard to master.

The most important concept is to know what type of join you need :

  • (INNER) JOIN: Returns records that have matching values in both tables.
  • LEFT (OUTER) JOIN: Returns all records from the left table, and the matched records from the right table.
  • RIGHT (OUTER) JOIN: Returns all records from the right table, and the matched records from the left table.

 

So let’s use a quick example :

SELECT *
FROM company c
LEFT JOIN Person p
ON c.comp_PrimaryPersonID = p.Pers_PersonID

This view is focused on company but will also return records in the Person table if the company’s Primay person exists. Why is this useful ? Because you will still get a list of all companies but you will also get data on the primary person if it’s there.

And nothing prevents you from joining on several external tables if more information is needed in your view. This is so much easier to do at SQL level rather than having 2 reports and cross-referencing them in Excel for example.

The error log

You will no doubt need to experiment with views and this will lead to some trial and error.

But nothing is more frustrating than receiving a cryptic error and having no idea where it might be coming from. Fortunately, Sage CRM comes with full logging capability. This can save you a lot of time with a bit of practice.

Let’s say you run a query and receive an error. You can have a look at the log by going to Administration > System > Logging

Once you click on today’s log you will get a list of events, and SQL errors will be clearly identified. If you check the log immediately after getting the error, it’s very likely going to be the last event in the log, making it easier to spot.

Since a lot of errors will be SQL errors, you can look for them online and find answers to the most common problems.

Conclusion

This list is by no means exhaustive and its purpose is not to replace a full course on SQL, yet using the above when creating views can save you a lot of time and give you results that are a bit more advanced than a basic query.