As powerful as Infor CRM groups are, they, unfortunately, do not allow you to aggregate data. Your options are either to create a report, export to Excel, or create a dashboard to summarize information. However, you can summarize record level data using SQL Views in Infor CRM (like Won Opportunities’ Amount YTD for each Account).
Enabling a SQL view on the Windows Client is fairly simple. Once the view is created, you have to launch the DB manager, Enable it, and then it shows up in the Windows Client. You can immediately start using the SQL view in groups.
Unfortunately, it’s not so simple on the web client. The web client is pretty particular about how views are defined and named due to the nHibernate entity model. Here are some items to be aware of when creating SQL views for the web client:
- The view should be under the sysdba context.
- The view name MUST be in all CAPS.
- The view name cannot start with the letter i.
- The fields in the view should be in all all CAPS (you can have the display names in any case you like).
- The view name should not begin with SLX and the view name should not end in an underscore. These older defects may be resolved by now, but it’s safer to avoid these in your names—just in case.
- Any DATE fields added to a view should always be in UTC because the InforCRM Provider always converts these, regardless of how they are defined.
- Field level security is NOT implemented when using a view, so you need to implement this in the view manually by joining to SECRIGHTS. For example:
create view [sysdba].[VW_ACCOUNT_SALES] as (SELECT A1.*, S0.ACCESSID ACCESSID_ FROM sysdba.CONTACT A1, SECRIGHTS S0 WHERE A1.SECCODEID= S0.SECCODEID)While the capitalization may seem odd, the reason for these restrictions is nHibernate and how the SDATA feed is generated by Infor CRM.
Keep these items in mind and you’ll be able to use your SQL views in Infor CRM groups without any issues!