Creating a view for SData access

SData (Sage Data) is a Sage Standard which enables desktop, server, and web-based Sage applications to communicate with each other as well as third-party applications and the Web. SData is built on top of leading industry standards including HTTP, XML, REST, and Atom/RSS.

In Sage CRM SData "feeds" can be consumed in a read-only format by Sage CRM via the Interactive Dashboard and by third-party applications.

The view in this example includes a derived field, oppo_daysopen, to show how long an opportunity has been open. When created as an SData view, this can easily be made available to the users via the Interactive Dashboard. The SData view could also be used by a third-party application which supports SData feeds.

SData views are not affected by the SData setting on the External Access tab. In this example, the Read-only SData setting on the <My Profile> | Administration | Customization | Primary Entities | Opportunity | External Access tab can be set to Yes or No. The view is still available for access.
  1. Click <My Profile> | Administration | Customization | Primary Entities | Opportunity | Views.
  2. Click New.
  3. Enter the View Name. For example vOppoDatesOpen.
  4. Select SData View.
  5. Enter a description and translation.
  6. Enter SQL for the new view. For example, change the existing:
    Copy
    CREATE VIEW vOppoDatesOpen AS SELECT * FROM OPPORTUNITY

    to:

    Copy
    CREATE VIEW vOppoDatesOpen AS SELECT 
      RTRIM(ISNULL(Pers_FirstName, '')) + ' ' + RTRIM(ISNULL(Pers_LastName, '')) AS Pers_FullName, Pers_PersonId, Pers_CreatedBy, Pers_SecTerr,   Pers_PrimaryUserId, Pers_ChannelID, 
      epd_pers.epd_EmailAddress as Pers_EmailAddress, 
      Comp_Name, Comp_CompanyId, Comp_CreatedBy, Comp_SecTerr, Comp_PrimaryUserId, Comp_ChannelID,
      epd_comp.epd_EmailAddress as Comp_EmailAddress,
      Chan_ChannelId, Chan_Description, 
      datediff(day, getdate(),oppo_opened) as oppo_daysopen, 
      Opportunity.*,
     (COALESCE(Oppo_Forecast, 0) * COALESCE(Oppo_Certainty, 0)) / 100 AS Oppo_Weighted 
    FROM Opportunity 
      LEFT JOIN Person ON Pers_PersonId = Oppo_PrimaryPersonId 
      LEFT JOIN CRMEmailPhoneData epd_pers ON epd_pers.epd_EntityID = 13 AND epd_pers.epd_RecordID = Pers_PersonID 
      LEFT JOIN Company ON Comp_CompanyId = Oppo_PrimaryCompanyId 
      LEFT JOIN CRMEmailPhoneData epd_comp ON epd_comp.epd_EntityID = 5 AND epd_comp.epd_RecordID = Comp_CompanyID 
      LEFT JOIN Channel ON Oppo_ChannelId = Chan_ChannelId 
    WHERE Oppo_Deleted IS NULL
  7. Click Save.
  8. To test the view on the Interactive Dashboard, click My CRM | Dashboard and open a dashboard.
  9. Click Modify Dashboard | Add New Gadget | SData Feed.
  10. Select CRM SData Provider and click Next.
  11. Select the new view you created, and click Next.
  12. Select the columns to display on the gadget and complete the gadget wizard steps.

The SData view you created is represented in a new gadget on the user's landing page. The URL to make the view available to another application would take the following format:

Copy 
http://myserver/sdata/{install name}j/sagecrm/-/vOppoDatesOpen