Below you can see that my Social Media interactive dashboard has a simple chart showing the number of saved tweets for each of my contacts that I track using the Twitter component.

This is a very simple chart but it does demonstrate the point that anything inside the Sage CRM database can be reported on and views can be used to transform that data to allow derived and calculated information to be displayed within those reports.

This article will show how this report was generated.  In order to do that we need to start at the beginning!

We can see from the image below that the Twitter component will display all the tweets issued by a particular account and it can also allow you to search for a phrase.  In the case below I am able see whatever my contact is saying publically and whatever is publically said to my contact is also show.

Each of these individual tweets can be saved into the system.  You just have to hover over the individual tweet and the option to save will appear.

The tweets in the Sage CRM v7.1 version of the Twitter component are saved as notes.

The notes will be stored against either a company or a person as both these entities can have their own twitternames and the Twitter components handles either of these as 'tweeps' or Twitter contacts.

In order to be able to report on the notes, I created a view called 'vTwitterNotes' that unioned the notes associated with the person entity and notes associated with the company entity and excluded all notes which are not created by the Twitter component.

The SQL for the view is


 SELECT     dbo.vNotes.Note_ForeignTableId, dbo.vNotes.Note_ForeignId, dbo.vNotes.Note_CreatedBy, dbo.vNotes.Note_CreatedDate, CAST ( note_note AS nvarchar(200)) as 'Tweet',
dbo.vNotes.Note_UpdatedDate, dbo.vNotes.Note_TimeStamp, dbo.vNotes.Note_ChannelID, RTRIM(dbo.Person.Pers_FirstName) 
+ ' ' + RTRIM(dbo.Person.Pers_LastName) AS 'Tweep', dbo.Person.pers_xttc_username AS 'TwitterName' 
FROM         dbo.vNotes LEFT OUTER JOIN
dbo.Person ON dbo.vNotes.Note_ForeignId = dbo.Person.Pers_PersonId
WHERE     (dbo.vNotes.Note_ForeignTableId = 13) and Note_Note like 'From Twitter%'
union
SELECT     dbo.vNotes.Note_ForeignTableId, dbo.vNotes.Note_ForeignId, dbo.vNotes.Note_CreatedBy, dbo.vNotes.Note_CreatedDate, CAST(note_note AS nvarchar(200)) as 'Tweet',
dbo.vNotes.Note_UpdatedDate, dbo.vNotes.Note_TimeStamp, dbo.vNotes.Note_ChannelID, dbo.Company.Comp_Name AS 'Tweep', 
dbo.Company.comp_xttc_username AS 'TwitterName'
FROM         dbo.vNotes LEFT OUTER JOIN
dbo.Company ON dbo.vNotes.Note_ForeignId = dbo.Company.Comp_CompanyId
WHERE     (dbo.vNotes.Note_ForeignTableId = 5) and Note_Note like 'From Twitter%';

  • 'Tweep' is the name of the full name of the person or company the Tweet was saved against.
  • 'Tweet' is the Twitter message.
  • 'TwitterName' is the Twittername stored.

Once I had created my view I then created a report.

In my case I created a new Report Category for Social Media Reporting.

The columns that were derived within the view 'vTwitterNotes' could then be referenced in the new Report definition.

I have included the Tweet and the date it was stored as the report's output and grouped it by the Twittername and Tweep.

I added the chart to the report as shown in the image below:

Once the report "Saved Tweets by Tweeps" had been saved is was then available as a data source for the Chart gadget type.

And once that new gadget was been saved it showed in the Dashboard like the image at the beginning of the article.