Report of broadcast reports run count
28 July, 2015
In the Yellowfin Usage reports area, there is a most/least favorite report. The numbers shown do not include reports that are broadcast.
Is there a report, or query, that can be run that will show the number of times a broadcast has been run over a time period?
I'm thinking of something like:
Date (typical date filter), Category, subcategory, report name, count
Here is a query for you, courtesy of our Software Architect Steve ;
select e.GMTDateTime, r.ReportId, r.ReportName, catDesc.ShortDescription, subDesc.ShortDescription
from Event e, ReportHeader r, ReportViewSource s, OrgReferenceCodeDesc catDesc, OrgReferenceCodeDesc subDesc
where e.EventTypeCode = 'REPORT'
and e.EventCode = 'RPTBROADCASTSENT'
and e.ContentId = r.ReportId
and r.SourceId = s.SourceId
and s.IpOrg = catDesc.IpOrg
and catDesc.RefTypeCode = 'RPTCATEGORY'
and r.CategoryCode = catDesc.RefCode
and s.IpOrg = subDesc.IpOrg
and subDesc.RefTypeCode = 'RPTSUBCATEGORY'
and r.SubCategoryCode = subDesc.RefCode;
This will get all the broadcast event records.
A few things about this query:
* The GMTDateTime is a numeric field that stores the GMT timestamp when the broadcast was sent, in the format yyyyMMddHHmmss
* If you want to do a count by date, you could get the EventDate field instead of GMTDateTime, and group by that.
* The query doesn't return the recipient(s) for a broadcast. You could get this from the EventData field in the EventTable if you want it (but that field also holds other data in a big string).
* You will get multiple records if a broadcast is sent to multiple people.
Any questions on this, please let us know.