INFORMATIONAL ** THIS INFORMATION HAS NOT BEEN VERIFIED **

How to use In-Container Grouping in Sage MAS Intelligence

Entry Type: Informational

Product: SMI Report Designer

Application: Connector

Version Reported: All

Subject:

How to use In-Container Grouping in Sage MAS Intelligence.

Possible Resolution:

In-Container Grouping using the Connector Module can assist with the following types of Scenarios:

A user would also like flexibility to group stores as the reporting line may change. For example currently a Company may have a district manager who is responsible for stores 10, 12, 15, 23. During a re-organization in the company, that manager is now responsible for stores 10, 12, 14, 25 and 31.
A user may want to create separate roll up levels for different individuals in the company.
A user would like to know how to group accounts together to be used in multiple reports, for example: Accounts Receivable: 1200 + 1205; Sales and Service 400 through 405 but not 403, COGS: all accounts that begin with 50_; Operating Expenses: 600 -720; Payroll Expenses: 740-760 , etc.

This method requires access to the Connector module. While it is a faster method of grouping information, it requires a level of SQL knowledge, and if you would like to change the groupings you will need to come back and modify the expression.

1. Open the Connector module. Double click on the relevant Sage MAS connector, then select the Financial Reports 2-0 (MAS) container.

2. Right click on the container and select 'Add Expressions'. From the next dialogue window, select 'SQL Expression'.

3. Select SQL Exception.

4. Give the expression the name 'Customized Group' and click 'OK'.

5. At the next prompt, simply select 'OK'.

6. The expression, 'Customized Grouping', will now be in the container. Select the expression, then type the following into the Expression Source field the click on 'Apply':

CASE WHEN SUBSTRING("GL_Account"."MainAccountCode",1,4)<200 THEN 'Group 1'

WHEN SUBSTRING("GL_Account"."MainAccountCode",1,4)<200 THEN 'Group 2' ELSE 'No GROUP' END

(This expression will look at each account number and if it is below 200, then return the value 'Group 1'; if it is above 200, then it will return the value 'Group 2'. This will happen on each line of raw data in the report. We will now add the field 'Customized Group' to our report.)

7. In the Report Manager module, select the Financial Reports Designer report.

8. The 'Choose Column Fields' window will appear. Scroll down until you find the 'Customized Group' field, place a tick in the box next to it and click 'Ok'.

**When the report runs, there will now be an additional column in the raw data sheet and will be available as a grouping field.