Exports fail due to broken folder links
Use attached .jsp
Find Schedules that are attached to views/reports NOT open
select taskschedule.scheduleunitid, taskschedule.scheduleunitcode, reportbroadcast.broadcastid, reportbroadcast.reportid, reportheader.reportname,reportheader.reportstatuscode, reportview.viewdescription, reportview.viewstatuscode from taskschedule
inner join reportbroadcast on taskschedule.scheduleunitid=reportbroadcast.broadcastid
inner join reportheader on reportbroadcast.reportid=reportheader.reportid
inner join reportview on reportheader.viewid=reportview.viewid
where reportheader.Reportstatuscode <> 'OPEN'
OR
reportview.viewstatuscode <> 'OPEN'
Find filter group schedules based on data source id
select * from taskschedule
inner join reportfieldtemplate on taskschedule.scheduleunitid=reportfieldtemplate.fieldtemplateid
inner join reportview on reportfieldtemplate.viewid=reportview.viewid
inner join reportviewsource on reportview.sourceid=reportviewsource.sourceid
where reportviewsource.sourceid=<sourceID>
Find data source information for viewlevel filter groups. Using the TaskSchedule table as reference point
select * from ReportViewSource
where SourceId in (select SourceId from ReportView where ViewId in (select ViewId from ReportFieldTemplate where Fieldtemplateid in (select scheduleunitid from TaskSchedule where ScheduleUnitCode='FILTERGROUPID')))
Find Filter schedules based on ReportViewStatusCode
select * from taskschedule
inner join reportfieldtemplate on taskschedule.scheduleunitid=reportfieldtemplate.fieldtemplateid
inner join reportview on reportfieldtemplate.viewid=reportview.viewid
where reportview.ViewStatusCode =<ViewStatusCode>
Delete cached reports from the DocumentData table, but KEEP the last 5 cached copies (be extra safe here with backups)
select * from documentdata where revisionid not in (select revisionid from DocumentRevision where DocumentId in (
SELECT ri.DocumentId
FROM ContentManagement cm, ReportHeader rh, ReportInstance ri
WHERE cm.ContentEntityCode = 'REPORT'
AND cm.ContentCode = rh.SubCategoryCode
AND rh.ReportId = ri.ReportId
AND ri.DocumentId IS NOT NULL
AND ri.DocumentId != 0
AND ri.RequestTypeCode != 'COMMENT'
and ri.ReportGMTDateTime in (select top 5 ri_.ReportGMTDateTime FROM ContentManagement cm_, ReportHeader rh_, ReportInstance ri_
WHERE cm_.ContentEntityCode = 'REPORT'
AND cm_.ContentCode = rh.SubCategoryCode
AND rh_.ReportId = ri.ReportId
AND ri_.DocumentId IS NOT NULL
AND ri_.DocumentId != 0
AND ri_.RequestTypeCode != 'COMMENT'
and ri.reportid = ri_.reportid
order by ri_.ReportGMTDateTime desc)));
Split out Event Data for Report Runs from the EventData column in the Yellowfin DB (7.4 & later)
Oracle
SELECT eventid,
to_timestamp(TO_CHAR(gmtdatetime), 'yyyymmdd hh24:mi:ss') AS GMTDATETIME,
SUBSTR(CAST(EventData as VarChar2(169)), (INSTR(CAST(EventData as VarChar2(169)), ',rep') + 8), (INSTR(CAST(EventData as VarChar2(169)), ',requestor') - (INSTR(CAST(EventData as VarChar2(169)), ',rep')+8) )) AS ReportID,
SUBSTR(CAST(EventData as VarChar2(169)), 11, (INSTR(CAST(EventData as VarChar2(169)), ',num') - 11) ) AS TimeToRun,
SUBSTR(CAST(EventData as VarChar2(169)), (INSTR(CAST(EventData as VarChar2(169)), ',num') + 9), (INSTR(CAST(EventData as VarChar2(169)), ',report') - (INSTR(CAST(EventData as VarChar2(169)), ',num')+9) )) AS RowCount,
SUBSTR(CAST(EventData as VarChar2(169)), (INSTR(CAST(EventData as VarChar2(169)), ',req') + 11), (INSTR(CAST(EventData as VarChar2(169)), ',requestortype') - (INSTR(CAST(EventData as VarChar2(169)), ',req')+11) )) AS Requestor
FROM EVENT
WHERE eventcode='RPTRUN'
Postgres
select cast (SUBSTRING(CAST(EventData as VarChar(169)) FROM 11 FOR (POSITION(',num' IN CAST(EventData as VarChar(169)) ) - 11) ) as integer) as TimeToRun, cast(SUBSTRING(CAST(EventData as VarChar(169)) FROM (POSITION(',num' IN CAST(EventData as VarChar(169))) + 9) FOR (POSITION(',report' IN CAST(EventData as VarChar(169))) - (POSITION(',num' IN CAST(EventData as VarChar(169)))+9) )) as integer) as NumRows, cast (SUBSTRING(CAST(EventData as VarChar(169)) FROM (POSITION(',rep' IN CAST(EventData as VarChar(169))) + 8) FOR (POSITION(',requestor' IN CAST(EventData as VarChar(169))) - (POSITION(',rep' IN CAST(EventData as VarChar(169)))+8) )) as integer) as ReportID, cast (SUBSTRING(CAST(EventData as VarChar(169)) FROM (POSITION(',req' IN CAST(EventData as VarChar(169))) + 11) FOR (POSITION(',requestortype' IN CAST(EventData as VarChar(169))) - (POSITION(',req' IN CAST(EventData as VarChar(169)))+11) )) as integer)as RequestorID, TO_TIMESTAMP(CAST(GMTDATETIME AS VARCHAR), 'YYYYMMDDHH24MISS') as GMTDateTime from event WHERE EventCode = 'RPTRUN'
Toggle all chart series colors from Default to Solid - Custom (this is required upon upgrading from pre-7.35 to 7.35 onwards... ref this task)
INSERT INTO reportformat
SELECT ReportId, EntityCode, EntityId, 'SERIESUSECUSTOMCOLOUR', 'TRUE', FormatValue, Description, FieldId, ChartId, SeriesId, AxisCode
FROM dbo.reportformat WHERE FormatTypeCode='SERIESCOLOR'
Find events that capture edited/deleted events, along with related content
SELECT c.reportname as "Report/Dash Name", c.reportdescription as "Description", c.categorycode as "Top Folder", c.subcategorycode as "Child Folder", a.referenceid, a.ipsource, a.eventtypecode, a.eventcode, a.gmtdatetime, a.eventdate, a.eventdata, b.fullname
FROM event a
INNER JOIN Person b on(a.ipsource = b.ipperson)
INNER JOIN reportheader c on(c.reportid = a.contentid)
WHERE a.eventcode LIKE '%DELETE%' OR a.eventcode LIKE '%REMOVE%' OR a.eventcode LIKE '%EDIT%'
UNION
SELECT c.shortdescription, c.longdescription, c.categorycode, c.subcategorycode, a.referenceid, a.ipsource, a.eventtypecode, a.eventcode, a.gmtdatetime, a.eventdate, a.eventdata, b.fullname
FROM event a
INNER JOIN Person b on(a.ipsource = b.ipperson)
INNER JOIN reportgroup c on(c.groupid = a.contentid)
WHERE a.eventcode LIKE '%DELETE%' OR a.eventcode LIKE '%REMOVE%' OR a.eventcode LIKE '%EDIT%'
ORDER by gmtdatetime DESC;
Query to find all filter used in reports
SELECT ReportFilter.ColumnOperator, ReportFilter.WhereClauseOperator, ReportHeader.ReportName, ReportHeader.ReportStatusCode FROM ReportHeader LEFT JOIN ReportFilter ON ReportHeader.ReportId = ReportFilter.ReportId AND ReportHeader.ReportStatusCode='OPEN'
List Custom Query Filter value by report name
SELECT rh.ReportId, rh.ReportName, rh.ReportDescription, td.DataChunk FROM ReportHeader rh JOIN CachedFilter cf ON rh.ReportId=cf.ReportId JOIN TextData td ON cf.TextId=td.TextId WHERE rh.ReportName = 'CustomQuery'List Scheduled Report Refreshes
SELECT * FROM TaskSchedule WHERE ScheduleUnitId IN (SELECT ReportId FROM ReportHeader WHERE ScheduleTypeCode='AUTO' AND ReportStatusCode='OPEN');List Assigned Dashboard Tabs by User Group
With AccessGroupAssigned as( SELECT rg.GroupId as DashboardID, ag.ShortDescription as AccessGroup FROM AccessGroup ag JOIN ReportGroup rg ON rg.IpOwner = ag.AccessGroupId) Select rg.ShortDescription as "Default Dashboard Assigned", a.AccessGroup as "Team", a.DashboardID FROM ReportGroup rg JOIN ReportGroupEntity rge ON rg.GroupId = rge.EntityId JOIN AccessGroupAssigned a on rge.groupid = a.DashboardID
Find any report with a calculated field
select * from ReportHeader where ReportId in (select ReportId from Reportfield where FieldTemplateId in (select FieldTemplateId from reportfieldtemplate where RefRltshpTypeCode in ('CUSTOMFUNCTIONREPORT', 'CALCFIELD', 'CALCFIELDSIMPLE')))
and ReportStatusCode = 'OPEN'
Find Reports by Column Name
SELECT ReportName FROM reportheader as h
WHERE ReportId in (
SELECT ReportId FROM reportfield as f
INNER JOIN reportfieldtemplate as t
WHERE t.FieldTemplateId=f.FieldTemplateId
AND t.ColumnName="DEMOGRAPHIC");
select * from REPORTFIELD
inner join REPORTFORMAT
on ReportField.ReportID = ReportFormat.ReportID
and ReportField.FieldId = ReportFormat.EntityId
where ReportFormat.FORMATTYPECODE = 'DISPLAYCOLUMN'
and ReportFormat.FORMATCODE = 'false';
Find Associations (Related Content) where the Parent Report status is OPEN, however, links to a non 'OPEN' Child Report. Indicates that the Child Report has been deleted or overwritten
SELECT * FROM ReportAssociate
WHERE ParentReportId IN (SELECT ReportId FROM ReportHeader WHERE ReportStatusCode = 'OPEN')
AND ChildReportId IN (SELECT ReportId FROM ReportHeader WHERE ReportStatusCode != 'OPEN');
Find the 'OPEN' Report ID of Child Reports listed above
SELECT ReportId FROM ReportHeader WHERE ReportStatusCode = 'OPEN' AND PublishUUID IN (SELECT PublishUUID FROM ReportHeader WHERE ReportId in (<ReportIDs>));
Update the Association between Parent and Child Reports from above (you could also use the AssociationId from the first query above)
UPDATE ReportAssociate SET ChildReportId = <ReportIDOpen> WHERE ChildReportId = <ReportIDClosed>;
List all Fonts used in Yellowfin
select distinct FormatCode from ReportFormat where FormatTypeCode in ('TITLEFONTFAMILY', 'LABELTITLEFONTFAMILY, NUMBERVALUEFONTFAMILY', 'PROPBARLABELFONTFAMILY', 'PROPBARVALUEFONTFAMILY', 'XAXISLABELSFONTFAMILY', 'YAXISLABELSFONTFAMILY', 'TRELLISFONTFAMILY', 'YREFLINETEXTFONT', 'XREFLINETEXTFONT', 'IMGSCALEFONTFAMILY', 'IMGLABELFONTFAMILY', 'IMGTEXTFONTFAMILY', 'SLIDERFONTFAMILY', 'CATEGORYAXISTITLEFONTFAMILY', 'RANGEAXISTITLEFONTFAMILY', 'INNERLABELFONTFAMILY', 'OUTERLABELFONTFAMILY', 'METERTICKFONTFAMILY', 'VALUEFONTFAMILY', 'FIELDNAMEFONTFAMILY', 'TARGETFONTFAMILY', 'TREEMAPLABELFONTFAMILY', 'DATAFONTFAMILY', 'TITLEFONTFAMILY', 'CROSSTAB_METRICHEADINGS_FONTFAMILY', 'CROSSTAB_COLUMNVALUES_FONTFAMILY', 'CROSSTAB_ROWVALUES_FONTFAMILY') and ISNUMERIC(FormatCode) = 0 and FormatCode is not null
UNION ALL select distinct Description from ReportFormat where FormatTypeCode in ('TITLEFONTFAMILY', 'LABELTITLEFONTFAMILY, NUMBERVALUEFONTFAMILY', 'PROPBARLABELFONTFAMILY', 'PROPBARVALUEFONTFAMILY', 'XAXISLABELSFONTFAMILY', 'YAXISLABELSFONTFAMILY', 'TRELLISFONTFAMILY', 'YREFLINETEXTFONT', 'XREFLINETEXTFONT', 'IMGSCALEFONTFAMILY', 'IMGLABELFONTFAMILY', 'IMGTEXTFONTFAMILY', 'SLIDERFONTFAMILY', 'CATEGORYAXISTITLEFONTFAMILY', 'RANGEAXISTITLEFONTFAMILY', 'INNERLABELFONTFAMILY', 'OUTERLABELFONTFAMILY', 'METERTICKFONTFAMILY', 'VALUEFONTFAMILY', 'FIELDNAMEFONTFAMILY', 'TARGETFONTFAMILY', 'TREEMAPLABELFONTFAMILY', 'DATAFONTFAMILY', 'TITLEFONTFAMILY', 'CROSSTABMETRICHEADINGSFONTFAMILY', 'CROSSTABCOLUMNVALUESFONTFAMILY', 'CROSSTABROWVALUESFONTFAMILY') and FormatCode is null
UNION ALL select distinct configdata from Configuration Where ConfigCode IN ('CHARTTITLEFONTFAMILY', 'AXISTITLEFONTFAMILY', 'AXISLABELFONTFAMILY', 'LEGENDFONTFAMILY', 'LABELFONTFAMILY', 'SSELFONTFAMILY', 'DASHBOARDTABFONTFAMILY', 'REPORTTITLEFONTFAMILY', 'REPORTDESCFONTFAMILY', 'REPORTSTYLEHEADERFONTFAMILY', 'REPSTYLECROSSTABMETRICHEADERFONTFAMILY', 'REPORTCROSSTABCOLUMNVALUESFONTFAMILY', 'REPORTCROSSTABROWVALUESFONTFAMILY', 'REPORTSTYLEDATAFONTFAMILY', 'REPORTSTYLESECTIONTITLEFONTFAMILY', 'REPORTSTYLEHEADERFOOTERFONTFAMILY', 'REPORTSTYLESUMMARYFONTFAMILY')
UNION ALL Select distinct FormatValue from StoryboardThemeProperty Where FormatKey IN ('QUOTEATTRIBUTEFONTFAMILY', 'SUBTITLEFONTFAMILY')
List all broadcasts running on a date (format yyyy-mm-dd):
select * from TaskSchedule where ScheduleSubjectCode = 'BROADCAST' and ScheduleIsOn = 1 and '2020-12-31' = LastRunDate or ScheduleSubjectCode = 'BROADCAST' and ScheduleIsOn = 1 and '2020-12-31' = case when FrequencyTypeCode = 'MINUTES' then convert(varchar, getdate(), 23) when FrequencyTypeCode = 'DAILY' then DATEADD(DAY,FrequencyUnit,LastRunDate) when FrequencyTypeCode = 'WEEKLY' then DATEADD(DAY,FrequencyUnit,LastRunDate) when FrequencyTypeCode = 'FORTNIGHTLY' then DATEADD(DAY,14,LastRunDate) when FrequencyTypeCode = 'MONTHLY' then DATEADD(MONTH,1,LastRunDate) when FrequencyTypeCode = 'QUARTERLY' then DATEADD(MONTH,4,LastRunDate) when FrequencyTypeCode = 'BIANNUAL' then DATEADD(MONTH,6,LastRunDate) when FrequencyTypeCode = 'ANNUAL' then DATEADD(year,1,LastRunDate) when FrequencyTypeCode = 'ENDOFMONTH' then EOMONTH(LastRunDate,1) when FrequencyTypeCode = 'WEEKDAYS' THEN case when DATENAME(weekday,getdate()) IN ('Monday','Tuesday','Wednesday','Thursday','Friday') then convert(varchar, getdate(), 23) END END
Return list of reports using sections along with report names and section column names:
SELECT DISTINCT rf.ReportId, rh.ReportName, rft.ColumnName FROM ReportField rf JOIN ReportHeader rh ON rf.ReportId=rh.ReportId JOIN ReportFieldTemplate rft ON rf.FieldTemplateId=rft.FieldTemplateId WHERE rf.OutputLocationCode = 'BREAK';
Create a new Admin user from scratch - useful in case your admin is locked out and you can't reset password for some reason, plus existing admin doesn't have Web Services permissions, etc.
INSERT INTO Person VALUES(7,'Admin','YF',NULL,'M',NULL,'ATMN SSTM',NULL,'Administrator2','Administrator2','EN','PRIVATE',4,'EMAIL',NULL,NULL,NULL,'AUSTRALIA/SYDNEY',NULL,NULL,NULL,'ACTIVE')
INSERT INTO IpClass Values (7,'newadmin',NULL,NULL,1262026299,0,'2019-12-16','9999-12-31',0)
INSERT INTO IPContact VALUES( 7,'EMAIL','WORK',1,'TEXT',NULL,NULL,NULL,'newadmin@yellowfin.com.au',0)
INSERT INTO IpRltshp VALUES(1,7,'STAFFMEMBER','2001-01-01','9999-12-31',NULL,NULL,4,NULL,NULL)
INSERT INTO StaffMemberRole VALUES(7,1,'YFADMIN','2019-12-16','9999-12-31',1)
You'll have to use /ResetAdmin.jsp after this.
Return user name and role along with corresponding role permissions
SELECT DISTINCT p.FullName AS 'User Name', orcd1.ShortDescription AS 'Role', acl.AccessLevelCode AS 'Access Level', orcd2.ShortDescription AS 'Role Function' FROM Person p JOIN StaffMemberRole smr ON p.IpPerson = smr.IpEmployee JOIN OrgReferenceCodeDesc orcd1 ON smr.RoleCode = orcd1.RefCode JOIN AccessClassList acl ON smr.RoleCode = acl.RoleCode JOIN OrgReferenceCodeDesc orcd2 ON acl.FunctionName = orcd2.RefCode WHERE p.Status = 'ACTIVE' AND smr.EndDate > now() ORDER BY p.FullName ASC, orcd2.ShortDescription;
Attached Task Audit Reports
Attached Broadcast Audit Reports (showing users,groups,emails)
Show all Dashboards which show a specific Report (v9) - replace report name in both queries
SELECT distinct rg.ShortDescription FROM ReportGroup rg
WHERE rg.GroupId IN (
SELECT rge.GroupId FROM ReportGroupEntity rge
where rge.EntityId IN (
SELECT rge.GroupId FROM ReportGroupEntity rge
inner join ReportHeader rh on rge.EntityId = rh.ReportId
WHERE rh.ReportName = 'Region Revenue by Year' and ReportStatusCode = 'OPEN'
)
)
UNION
SELECT distinct rg.ShortDescription FROM ReportGroupEntity rge, ReportGroup rg
WHERE rge.EntityId IN (
SELECT wc.EntityId FROM WidgetItem wi, WidgetFormat wf, WidgetCanvas wc
WHERE wi.WidgetUUID = wf.WidgetUUID
AND wi.WidgetType = 'com.hof.mi.widgetcanvas.ReportWidget'
AND wf.PropertyName = 'publishUUID'
AND wi.CanvasUUID = wc.CanvasUUID
AND wf.PropertyValue = (select PublishUUID from ReportHeader where ReportName = 'Region Revenue by Year' and ReportStatusCode = 'OPEN')
)
AND rge.GroupId = rg.GroupId
In order to verify for certain what version of yellowfin the client was using, you can query the recently restored config db with the following command
SELECT *
FROM yellowfin.configuration
WHERE ConfigTypeCode = 'BUILD'
Get Next Scheduled Runtime estimate (based off last runtime - displayed in GMT Time) - SQL Server:
SELECT ScheduleUnitId,
case
when FrequencyTypeCode = 'MINUTES' then DATEADD(MINUTE,FrequencyUnit,CONVERT(datetime,STUFF(STUFF(STUFF(LastRunGMTDateTime ,13,0,':'),11,0,':'),9,0,' ')))
when FrequencyTypeCode = 'DAILY' then DATEADD(DAY,1,CONVERT(datetime,STUFF(STUFF(STUFF(LastRunGMTDateTime ,13,0,':'),11,0,':'),9,0,' ')))
when FrequencyTypeCode = 'WEEKLY' then DATEADD(DAY,7,CONVERT(datetime,STUFF(STUFF(STUFF(LastRunGMTDateTime ,13,0,':'),11,0,':'),9,0,' ')))
when FrequencyTypeCode = 'FORTNIGHTLY' then DATEADD(DAY,14,CONVERT(datetime,STUFF(STUFF(STUFF(LastRunGMTDateTime ,13,0,':'),11,0,':'),9,0,' ')))
when FrequencyTypeCode = 'MONTHLY' then DATEADD(MONTH,1,CONVERT(datetime,STUFF(STUFF(STUFF(LastRunGMTDateTime ,13,0,':'),11,0,':'),9,0,' ')))
when FrequencyTypeCode = 'QUARTERLY' then DATEADD(MONTH,4,CONVERT(datetime,STUFF(STUFF(STUFF(LastRunGMTDateTime ,13,0,':'),11,0,':'),9,0,' ')))
when FrequencyTypeCode = 'BIANNUAL' then DATEADD(MONTH,6,CONVERT(datetime,STUFF(STUFF(STUFF(LastRunGMTDateTime ,13,0,':'),11,0,':'),9,0,' ')))
when FrequencyTypeCode = 'ANNUAL' then DATEPART(year,DATEADD(year,1,CONVERT(datetime,STUFF(STUFF(STUFF(LastRunGMTDateTime ,13,0,':'),11,0,':'),9,0,' '))))
when FrequencyTypeCode = 'ENDOFMONTH' then
case when LastRunGMTDateTime != NULL then
CONVERT(datetime, CONCAT(CAST(EOMONTH(CONVERT(datetime,STUFF(STUFF(STUFF(LastRunGMTDateTime ,13,0,':'),11,0,':'),9,0,' ')),1) as varchar(10)),' ',SUBSTRING(STUFF(STUFF(LastRunGMTDateTime ,13,0,':'),11,0,':'),9,8)))
else
NULL
end
when FrequencyTypeCode = 'WEEKDAYS' THEN
case when DATENAME(weekday,DATEADD(DAY,1,CONVERT(datetime,STUFF(STUFF(STUFF(LastRunGMTDateTime ,13,0,':'),11,0,':'),9,0,' ')))) IN ('Monday','Tuesday','Wednesday','Thursday','Friday') then
DATEADD(DAY,1,CONVERT(datetime,STUFF(STUFF(STUFF(LastRunGMTDateTime ,13,0,':'),11,0,':'),9,0,' ')))
ELSE DATEADD(DAY,3,CONVERT(datetime,STUFF(STUFF(STUFF(LastRunGMTDateTime ,13,0,':'),11,0,':'),9,0,' ')))
END
END as NextRunGMTDateTime
from TaskSchedule
where
ScheduleSubjectCode = 'BROADCAST'
and LastRunGMTDateTime is not NULL
and ScheduleIsOn = 1This will show all broadcasts and the filter sets that are used for them.
SELECT *
FROM ReportInstanceFilter
WHERE InstanceId IN
(SELECT min(ReportInstanceId) from ReportInstance where ReportStatusCode = 'BROADCASTBOOKMARK'
AND ReportID IN (SELECT ReportId from ReportBroadcast where SubjectLine LIKE '%yourBroadcastSubjectLine%'))
The above query gives the result for the first broadcast saved filter values. Removing the min() function gives filters used in all broadcasts.
Get the SQL query of a view
Steps outlined in this question: https://community.yellowfinbi.com/agent/report/32/object/24882