Yellowfin General Usage – View and Reports
30 January, 2013
Could you please suggest a solution for below mentioned items:
1) Am using the 'Yellowfin General Usage' to generate reports in order to track activities like, broadcast time, report run time,report edited time etc. I have used 'EventTime' field of 'All Events' table but the time was not displayed for any of the events. I do get to see the 'EventDate' and 'EventCode'. I have not altered the existing links between the tables. Please help to see the event time data.
2) I had created a report named 'Audit Report' using the view 'Yellowfin General Usage' so as to track the events that take place on reports. In order to see the events, i created a new 'sample report' and saved the report. After running the 'sample report' for the first time, i had ran 'Audit Report' which contained 3 information i.e. 1 for RPTCREATE, 2 for RPTRUN for 'sample report' in the 'Audit Report'. Then i had run the 'sample report' again and have checked the stats in 'Audit Report'. When i looked 'Audit Report' for the second time, it had 2 entries for RPTCREATE,4 for RPTRUN . Every time when i run the 'Audit Report' the events like RPTRUN,RPTCREATE is multiplied by two. i am not sure if this is happening due to existing/shipped joins in 'Yellowfin General Usage'. Please help in resolving the same.
3) There are about 90 tables in 'Yellowfin General Usage' view. Can you provide me a document that indicates linkage between the tables?
4) I would like to have information on the broadcasted report like the start time, end time, report name, status(success/failure), to whom the report got delivered etc ... Please suggest what fields to be used from 'ReportBroadcast', 'ReportBroadcastReceipent' in order to link/join them.
Thanks in advance !!!
1) Am using the 'Yellowfin General Usage' to generate reports in order to track activities like, broadcast time, report run time,report edited time etc. I have used 'EventTime' field of 'All Events' table but the time was not displayed for any of the events. I do get to see the 'EventDate' and 'EventCode'. I have not altered the existing links between the tables. Please help to see the event time data.
2) I had created a report named 'Audit Report' using the view 'Yellowfin General Usage' so as to track the events that take place on reports. In order to see the events, i created a new 'sample report' and saved the report. After running the 'sample report' for the first time, i had ran 'Audit Report' which contained 3 information i.e. 1 for RPTCREATE, 2 for RPTRUN for 'sample report' in the 'Audit Report'. Then i had run the 'sample report' again and have checked the stats in 'Audit Report'. When i looked 'Audit Report' for the second time, it had 2 entries for RPTCREATE,4 for RPTRUN . Every time when i run the 'Audit Report' the events like RPTRUN,RPTCREATE is multiplied by two. i am not sure if this is happening due to existing/shipped joins in 'Yellowfin General Usage'. Please help in resolving the same.
3) There are about 90 tables in 'Yellowfin General Usage' view. Can you provide me a document that indicates linkage between the tables?
4) I would like to have information on the broadcasted report like the start time, end time, report name, status(success/failure), to whom the report got delivered etc ... Please suggest what fields to be used from 'ReportBroadcast', 'ReportBroadcastReceipent' in order to link/join them.
Thanks in advance !!!
Hi,
here is the answer for question 1:
that is correct, the EventTime field isn't used, the field you need is called GMTDateTime. However it isn't included in the view so you'll have to edit the view and add it.
Another thing is that GMTDateTime is of type DECIMAL so you may need to convert it to a DATETIME by doing something similar to the following SQL Server query:
SELECT CONVERT(DATETIME,(LEFT ((CONVERT(VARCHAR(14), GMTDATETIME) ),8) + ' ' +
SUBSTRING(CONVERT(VARCHAR(14), GMTDATETIME),9,2) + ':' +
SUBSTRING(CONVERT(VARCHAR(14), GMTDATETIME),11,2) + ':' +
RIGHT ((CONVERT(VARCHAR(14), GMTDATETIME) ),2)),120) as 'DateTimeCast
FROM Event
Also, please note that the GMTDateTime field stores the timestamp as GMT+0 hours, so you would have to convert it to your local timezone.
2) I tried to reproduce this issue but unfortunately wasn't able to, I found that everytime I ran my sample report the RPTCREATE didn't change (i.e. there was only the one initial creation) and the RPTRUN only incremented by 1 for each time it was run. Could you please check if the doubling of values is occurring directly in the database or only in the report.
3) I'm sorry but the only such document at the moment is many versions out of date so I can't give it out because there have been many changes over the years thus the information would be incorrect.
4) You'll be able to find most of that broadcast data when you join the broadcast record to its corresponding TaskSchedule record by the ScheduleUnitID which is contained within the EventData column of the Event table
Good luck with your report, if there are any further questions or issues that may arise then please don't hesitate to contact us.
Regards,
Dave
here is the answer for question 1:
that is correct, the EventTime field isn't used, the field you need is called GMTDateTime. However it isn't included in the view so you'll have to edit the view and add it.
Another thing is that GMTDateTime is of type DECIMAL so you may need to convert it to a DATETIME by doing something similar to the following SQL Server query:
SELECT CONVERT(DATETIME,(LEFT ((CONVERT(VARCHAR(14), GMTDATETIME) ),8) + ' ' +
SUBSTRING(CONVERT(VARCHAR(14), GMTDATETIME),9,2) + ':' +
SUBSTRING(CONVERT(VARCHAR(14), GMTDATETIME),11,2) + ':' +
RIGHT ((CONVERT(VARCHAR(14), GMTDATETIME) ),2)),120) as 'DateTimeCast
FROM Event
Also, please note that the GMTDateTime field stores the timestamp as GMT+0 hours, so you would have to convert it to your local timezone.
2) I tried to reproduce this issue but unfortunately wasn't able to, I found that everytime I ran my sample report the RPTCREATE didn't change (i.e. there was only the one initial creation) and the RPTRUN only incremented by 1 for each time it was run. Could you please check if the doubling of values is occurring directly in the database or only in the report.
3) I'm sorry but the only such document at the moment is many versions out of date so I can't give it out because there have been many changes over the years thus the information would be incorrect.
4) You'll be able to find most of that broadcast data when you join the broadcast record to its corresponding TaskSchedule record by the ScheduleUnitID which is contained within the EventData column of the Event table
Good luck with your report, if there are any further questions or issues that may arise then please don't hesitate to contact us.
Regards,
Dave
To display the date time in your server's timezone, you can use the following snippet:
[code]
SELECT TOP 10
dateadd(mi, datediff(mi, getutcdate(), getdate()), convert(DATETIME, (left((convert(VARCHAR(14), GMTDATETIME)), 8) + ' ' + substring(convert(VARCHAR(14), GMTDATETIME), 9, 2) + ':' + substring(convert(VARCHAR(14), GMTDATETIME), 11, 2) + ':' + right((convert(VARCHAR(14), GMTDATETIME)), 2)), 120)) AS [Event Datetime]
FROM Event
[/code]
Cheers,
Christophe.
[code]
SELECT TOP 10
dateadd(mi, datediff(mi, getutcdate(), getdate()), convert(DATETIME, (left((convert(VARCHAR(14), GMTDATETIME)), 8) + ' ' + substring(convert(VARCHAR(14), GMTDATETIME), 9, 2) + ':' + substring(convert(VARCHAR(14), GMTDATETIME), 11, 2) + ':' + right((convert(VARCHAR(14), GMTDATETIME)), 2)), 120)) AS [Event Datetime]
FROM Event
[/code]
Cheers,
Christophe.