Skip to Content

Obtaining consolidated assessment data for each report

You can obtain consolidated assessment data for each report, which saves the time required to run each SQL query separately and combine the collected data manually. Run the database-specific SQL query to fetch this data.

Related topics

Assessing-the-BMC-Helix-ITSM-Smart-Reporting-environment

Running-the-migration-tool

The result that you get after running the query includes the following information:

Column name

Description

REPORTID

The ID of the report.

REPORTNAME

The name of the report.

FOLDERNAME

The name of the folder where the report is located.

SUBFOLDERNAME

The name of the subfolder where the report is located.

OOTB_OR_CUSTOM

Indicates whether the report is out-of-the-box or custom.

HIT_COUNT

The number of times the report was accessed.

PUBLIC_OR_PRIVATE

Indicates whether the report is public or private.

CHARTTYPE

The type of chart used in the report.

COUNT_CALCULATED_FIELDS

The number of calculated fields used in the report.

COUNT_DBFN

The count of database functions used in the report.

COUNT_DRILL_CHILD_REPORTS

The number of child reports that can be opened from the report.

COUNT_CO_DISPLAY_CHILD_REPORTS

The number of CO-Display reports added as child reports.

HAS CHILD OR CO-DISPLAY

A Boolean value that indicates whether the report has subreports or related reports added as CO-Display.

HAS CO-DISPLAY

Indicates whether the report has related reports added as CO-Display.

DASHBOARD_NAME

The name of the dashboard based on which the report is created.

COUNT_BROADCAST

The number of broadcasts that are configured for the report.

LASTACCESSEDDATE

The date when the report was last accessed.

Before you begin

Make sure that the prerequisites specified in the following topic are met: Assessing-the-BMC-Helix-ITSM-Smart-Reporting-environment

To obtain consolidated assessment data

  1. Connect to the BMC Helix ITSM: Smart Reporting database by providing the required database details and credentials.
  2. In the database query panel, run the following queries based on the database type.
    • Sample query for the MSSSQL database
      select distinct a.ReportId as "REPORTID",
      a.ReportName as "REPORTNAME",
      a.categorycode as "FOLDERNAME",
      a.subcategorycode as "SUBFOLDERNAME",
      rv.viewdescription as "VIEWNAME",
      CASE WHEN a.iplastmodifier=5 THEN 'OOTB Report' ELSE 'Custom Report' END as "OOTB_OR_CUSTOM",
      a.hitcount as "HIT_COUNT",
      CASE WHEN a.ExternalAccessCode='CORPORATE' THEN 'Public' WHEN a.ExternalAccessCode='PERSONAL' THEN 'Private' ELSE 'UNKNOWN' END as "PUBLIC_OR_PRIVATE",
      c.ChartType as "CHARTTYPE",
      COALESCE(d.count_calculated_fields ,0) "COUNT_CALCULATED_FIELDS",
      COALESCE(g.count_dbfn, 0) "COUNT_DBFN",
      COALESCE(e.count_child_reports,0) "COUNT_DRILL_CHILD_REPORTS",
      COALESCE(f.count_co_display,0) "COUNT_CO_DISPLAY_CHILD_REPORTS",
      h.Dashboard_Name as "DASHBOARD_NAME",
      COALESCE(i.Broadcast_Count,0) as "COUNT_BROADCAST",
      lastaccesseddate as "LASTACCESSEDDATE",
      x.FILTER_COUNT as "FILTER_COUNT"
      from ReportHeader a
      LEFT OUTER JOIN Person b On a.Ipcreator = b.IpPerson
      LEFT OUTER JOIN reportview rv on a.viewid = rv.viewid
      LEFT OUTER JOIN (select RH.ReportName,RH.reportid, count(RF.fieldtemplateid) as FILTER_COUNT from ReportFilter RF, ReportHeader RH
                           WHERE RF.ReportId = RH.ReportId AND RH.ReportStatusCode = 'OPEN'
                           group by RH.ReportName,RH.reportid
                          )x on x.reportid = a.reportid       
      LEFT OUTER JOIN (SELECT rh.reportid, COALESCE(STUFF((SELECT ', ' + Chart.ChartType FROM Chart
      WHERE rh.ReportId = Chart.Reportid FOR XML PATH('')), 1, 1, ''),'TABLE') as ChartType
      FROM ReportHeader rh WHERE rh.ReportStatusCode ='OPEN' AND rh.RoleCode !='SUBQUERY' AND rh.ReportName !='Draft Report'
      GROUP BY rh.reportid ) c on a.reportid = c.reportid
      LEFT OUTER JOIN (select s.reportid,count(1) count_calculated_fields from (
      select distinct a.ReportId, b.RefRltshpTypeCode, b.ValidationFunctionName, AssociatedReportId,'ReportField' UsedIn
      from reportheader a , ReportFieldTemplate b, Reportfield c
      where a.ReportStatusCode='OPEN' and a.viewid=b.viewid and c.reportid=a.ReportId and c.FieldTemplateId=b.FieldTemplateId
      and (b.ValidationFunctionName is not null or b.SQLClause is not null or (b.AssociatedReportId!=0 and b.AssociatedReportId is not null))
      union
      select distinct a.ReportId, b.RefRltshpTypeCode, b.ValidationFunctionName, AssociatedReportId, 'ReportFilter' UsedIn
      from reportheader a , ReportFieldTemplate b, ReportFilter c
      where a.ReportStatusCode='OPEN' and a.viewid=b.viewid and c.reportid=a.ReportId and c.FieldTemplateId=b.FieldTemplateId
       and (b.ValidationFunctionName is not null or b.SQLClause is not null or (b.AssociatedReportId!=0 and b.AssociatedReportId is not null))
      )s group by s.reportid ) d on d.reportid = a.reportid
      LEFT OUTER JOIN (select distinct reportheader.Reportid, count(reportassociate.childreportid) count_child_reports
      from Reportheader, reportassociate
      where reportheader.reportid=reportassociate.parentreportid and reportheader.Drillcode NOT IN ('NODRILL') AND
      reportheader.ReportStatusCode ='OPEN' AND reportheader.RoleCode !='SUBQUERY' AND reportheader.ReportName !='Draft Report' AND reportassociate.rltshptypecode in ('DRILL','CONDITIONALDRILL','CONDITIONALDIRECT','DIRECT')
      group by reportheader.Reportid ) e on e.reportid = a.reportid
      LEFT OUTER JOIN (select ReportAssociate.ParentReportId, count(distinct Reportheader.reportid) count_co_display
      from ReportHeader, ReportAssociate
      where ReportHeader.ReportId=ReportAssociate.ChildReportId
      group by ReportAssociate.ParentReportId
      ) f on f.parentreportid = a.reportid
      LEFT OUTER JOIN (select ReportId, count(1) count_dbfn from (
      select a.ReportId, a.ReportName, b.RefRltshpTypeCode, b.ValidationFunctionName, AssociatedReportId,'ReportField' UsedIn
      from reportheader a , ReportFieldTemplate b, Reportfield c
      where a.ReportStatusCode='OPEN' and a.viewid=b.viewid and c.reportid=a.ReportId and c.FieldTemplateId=b.FieldTemplateId
      and (b.ValidationFunctionName is not null or b.SQLClause is not null or (b.AssociatedReportId!=0 and b.AssociatedReportId is not null))
      union
      select a.ReportId, a.ReportName, b.RefRltshpTypeCode, b.ValidationFunctionName, AssociatedReportId, 'ReportFilter' UsedIn
      from reportheader a , ReportFieldTemplate b, ReportFilter c
      where a.ReportStatusCode='OPEN' and a.viewid=b.viewid and c.reportid=a.ReportId and c.FieldTemplateId=b.FieldTemplateId
      and (b.ValidationFunctionName is not null or b.SQLClause is not null or (b.AssociatedReportId!=0 and b.AssociatedReportId is not null))
      )s where upper(s.ValidationFunctionName) like '%DBFN%'
      group by s.reportid
      ) g on g.reportid = a.reportid
      LEFT OUTER JOIN (SELECT distinct ReportHeader.ReportId, STUFF((SELECT distinct ', ' + Reportgroup.ShortDescription FROM ReportGroup,ReportAssociate
      WHERE ReportGroup.GroupId= ReportAssociate.ReportGroupId AND ReportAssociate.ChildReportId = ReportHeader.ReportId and reportgroup.statuscode != 'ARCHIVED'
      FOR XML PATH('')), 1, 1, '') as Dashboard_Name FROM ReportHeader
      GROUP BY ReportHeader.reportid
      ) h on h.reportid = a.reportid
      LEFT OUTER JOIN (select Count(Broadcastid) as Broadcast_Count, Reportname ,Reportheader.Reportid
      from ReportBroadcast,ReportHeader,TaskSchedule
      where ReportBroadcast.ReportId=ReportHeader.ReportId
       AND ReportBroadcast.BroadcastId = TaskSchedule.ScheduleUnitId
      AND ReportStatusCode ='OPEN' AND ReportName !='Draft Report' AND RoleCode !='SUBQUERY'
      group by ReportHeader.ReportName,Reportheader.Reportid
      ) i on i.ReportId=a.ReportId
      left outer join
      (select r.reportid,
      r.reportname,
      max(b.eventdate) as lastaccesseddate
      from reportheader r
      left outer join
      (select contentid,eventtypecode,eventcode,eventid,eventdate from event union
      select contentid,eventtypecode,eventcode,eventid,eventdate from EventArchive) b
      on r.reportid=b.contentid and
      EventTypeCode='REPORT' and
      eventcode in ('RPTRUN','DASHRUN','RPTBROADCAST') and
      b.eventdate <= GETDATE()
      where r.ReportStatusCode ='OPEN' AND
      r.ReportName !='Draft Report' AND
      r.RoleCode !='SUBQUERY'
      group by r.reportid, r.reportname) ea
      on ea.reportid=a.reportid
      where a.ReportStatusCode ='OPEN' AND
      a.ReportName !='Draft Report' AND
      a.RoleCode !='SUBQUERY'
      order by a.reportid, a.reportname;
    • Sample query for the Oracle SQL database
      select distinct a.ReportId as "REPORTID", a.ReportName as "REPORTNAME", a.categorycode as "FOLDERNAME",a.subcategorycode as "SUBFOLDERNAME",
      CASE WHEN a.iplastmodifier=5 THEN 'OOTB Report' ELSE 'Custom Report' END as "OOTB_OR_CUSTOM",
      a.hitcount as "HIT_COUNT",
      CASE WHEN a.ExternalAccessCode='CORPORATE' THEN 'Public' WHEN a.ExternalAccessCode='PERSONAL' THEN 'Private' ELSE 'UNKNOWN' END as "PUBLIC_OR_PRIVATE",
      c.ChartType as "CHARTTYPE",
      NVL(d.count_calculated_fields ,0) "COUNT_CALCULATED_FIELDS",
      NVL(g.count_dbfn, 0) "COUNT_DBFN",
      NVL(e.count_child_reports,0) "COUNT_DRILL_CHILD_REPORTS",
      NVL(f.count_co_display,0) "COUNT_CO_DISPLAY_CHILD_REPORTS",
      h.Dashboard_Name as "DASHBOARD_NAME",
      NVL(i.Broadcast_Count,0) as "COUNT_BROADCAST",
      lastaccesseddate as "LASTACCESSEDDATE",
      x.FILTER_COUNT as "FILTER_COUNT"
      from ReportHeader a
      Left Outer Join Person b On a.Ipcreator = b.IpPerson
      LEFT OUTER JOIN (select RH.ReportName,RH.reportid, count(RF.fieldtemplateid) as FILTER_COUNT from ReportFilter RF, ReportHeader RH
      WHERE RF.ReportId = RH.ReportId AND RH.ReportStatusCode = 'OPEN' group by RH.ReportName,RH.reportid)x on x.reportid = a.reportid

      LEFT OUTER JOIN (select reportid, NVL(LISTAGG(ChartType, ',') within group (order by reportid ),'TABLE')  as ChartType from (
      select distinct reportheader.reportid,Chart.ChartType
      from ReportHeader left outer join Chart on Reportheader.ReportId = Chart.Reportid
      where ReportStatusCode ='OPEN' AND RoleCode !='SUBQUERY' AND ReportName !='Draft Report'
      ) C1 group by C1.reportid) c on a.reportid = c.reportid
      LEFT OUTER JOIN (select s.reportid,count(1) count_calculated_fields from (
      select distinct a.ReportId, b.RefRltshpTypeCode, CAST(b.ValidationFunctionName AS VARCHAR2(100)) as ValidationFunctionName, AssociatedReportId,'ReportField' UsedIn
      from reportheader a , ReportFieldTemplate b, Reportfield c
      where a.ReportStatusCode='OPEN' and a.viewid=b.viewid and c.reportid=a.ReportId and c.FieldTemplateId=b.FieldTemplateId
      and (CAST(b.ValidationFunctionName AS VARCHAR2(100)) is not null or b.SQLClause is not null or (b.AssociatedReportId!=0 and b.AssociatedReportId is not null))
      union
      select distinct a.ReportId, b.RefRltshpTypeCode, CAST(b.ValidationFunctionName AS VARCHAR2(100)) as ValidationFunctionName, AssociatedReportId, 'ReportFilter' UsedIn
      from reportheader a , ReportFieldTemplate b, ReportFilter c
      where a.ReportStatusCode='OPEN' and a.viewid=b.viewid and c.reportid=a.ReportId and c.FieldTemplateId=b.FieldTemplateId
       and (CAST(b.ValidationFunctionName AS VARCHAR2(100)) is not null or b.SQLClause is not null or (b.AssociatedReportId!=0 and b.AssociatedReportId is not null))
      )s group by s.reportid ) d on d.reportid = a.reportid
      LEFT OUTER JOIN (select distinct reportheader.Reportid, count(reportassociate.childreportid) count_child_reports
      from Reportheader, reportassociate
      where reportheader.reportid=reportassociate.parentreportid and reportheader.Drillcode NOT IN ('NODRILL') AND
      reportheader.ReportStatusCode ='OPEN' AND reportheader.RoleCode !='SUBQUERY' AND reportheader.ReportName !='Draft Report' and reportassociate.rltshptypecode in ('DRILL','CONDITIONALDRILL','CONDITIONALDIRECT','DIRECT')
      group by reportheader.Reportid ) e on e.reportid = a.reportid
      LEFT OUTER JOIN (select ReportAssociate.ParentReportId, count(distinct Reportheader.reportid) count_co_display
      from ReportHeader, ReportAssociate
      where ReportHeader.ReportId=ReportAssociate.ChildReportId
      group by ReportAssociate.ParentReportId
      ) f on f.parentreportid = a.reportid
      LEFT OUTER JOIN (select ReportId, count(1) count_dbfn from (
      select a.ReportId, a.ReportName, b.RefRltshpTypeCode, CAST(b.ValidationFunctionName AS VARCHAR2(100)) as ValidationFunctionName, AssociatedReportId,'ReportField' UsedIn
      from reportheader a , ReportFieldTemplate b, Reportfield c
      where a.ReportStatusCode='OPEN' and a.viewid=b.viewid and c.reportid=a.ReportId and c.FieldTemplateId=b.FieldTemplateId
      and (CAST(b.ValidationFunctionName AS VARCHAR2(100)) is not null or b.SQLClause is not null or (b.AssociatedReportId!=0 and b.AssociatedReportId is not null))
      union
      select a.ReportId, a.ReportName, b.RefRltshpTypeCode, CAST(b.ValidationFunctionName AS VARCHAR2(100)) as ValidationFunctionName, AssociatedReportId, 'ReportFilter' UsedIn
      from reportheader a , ReportFieldTemplate b, ReportFilter c
      where a.ReportStatusCode='OPEN' and a.viewid=b.viewid and c.reportid=a.ReportId and c.FieldTemplateId=b.FieldTemplateId
      and (CAST(b.ValidationFunctionName AS VARCHAR2(100)) is not null or b.SQLClause is not null or (b.AssociatedReportId!=0 and b.AssociatedReportId is not null))
      )s where upper(CAST(s.ValidationFunctionName AS VARCHAR2(100))) like '%DBFN%'
      group by s.reportid
      ) g on g.reportid = a.reportid
      LEFT OUTER JOIN (select Report_Outer.ReportId, LISTAGG( Dashboard_Name,',') within group (order by Report_Outer.ReportId) as Dashboard_Name from
      (select distinct ReportHeader.ReportId, Reportgroup.ShortDescription as Dashboard_Name
      from ReportGroup, ReportAssociate, ReportHeader
      where ReportGroup. GroupId= ReportAssociate.ReportGroupId AND ReportAssociate.ChildReportId = ReportHeader.ReportId
      and reportgroup.statuscode != 'ARCHIVED') Report_Outer
      group by Report_Outer.ReportId
      ) h on h.reportid = a.reportid
      LEFT OUTER JOIN (select Count(Broadcastid) as Broadcast_Count, Reportname ,Reportheader.Reportid
      from ReportBroadcast,ReportHeader,TaskSchedule
      where ReportBroadcast.ReportId=ReportHeader.ReportId
      AND ReportBroadcast.BroadcastId = TaskSchedule.ScheduleUnitId
      AND ReportStatusCode ='OPEN' AND ReportName !='Draft Report' AND RoleCode !='SUBQUERY'
      group by ReportHeader.ReportName,Reportheader.Reportid
      ) i on i.ReportId=a.ReportId
      left outer join
      (select rh.reportid,
      rh.reportname,
      max(b.eventdate) as lastaccesseddate
      from reportheader rh
      left outer join
      (select contentid,eventtypecode,eventcode,eventid,eventdate from event union
      select contentid,eventtypecode,eventcode,eventid,eventdate from EventArchive) b
      on rh.reportid=b.contentid and
      EventTypeCode='REPORT' and
      eventcode in ('RPTRUN','DASHRUN','RPTBROADCAST') and
      b.eventdate <= CURRENT_DATE
      where rh.ReportStatusCode ='OPEN' AND
      rh.ReportName !='Draft Report' AND
      rh.RoleCode !='SUBQUERY'
      group by rh.reportid, rh.reportname) ea
      on ea.reportid=a.reportid
      where a.ReportStatusCode ='OPEN' AND
      a.ReportName !='Draft Report' AND
      a.RoleCode !='SUBQUERY'
      order by a.reportid, a.reportname;
    • Sample query for the PostgreSQL database
      select distinct a.ReportId as "REPORTID",
                                  a.ReportName as "REPORTNAME",
                                   a.categorycode as "FOLDERNAME",
                                   a.subcategorycode as "SUBFOLDERNAME",
                                   rv.viewdescription as "VIEWNAME",       
                                   CASE WHEN a.iplastmodifier=5 THEN 'OOTB Report' ELSE 'Custom Report' END as "OOTB_OR_CUSTOM",
                                   a.hitcount as "HIT_COUNT",
                                   CASE WHEN a.ExternalAccessCode='CORPORATE' THEN 'Public' WHEN a.ExternalAccessCode='PERSONAL' THEN 'Private' ELSE 'UNKNOWN' END as "PUBLIC_OR_PRIVATE",
                                   c.ChartType as "CHARTTYPE",
                                   COALESCE(d.count_calculated_fields ,0) "COUNT_CALCULATED_FIELDS",
                                   COALESCE(g.count_dbfn, 0) "COUNT_DBFN",
                                   COALESCE(e.count_child_reports,0) "COUNT_DRILL_CHILD_REPORTS",
                                   COALESCE(f.count_co_display,0) "COUNT_CO_DISPLAY_CHILD_REPORTS",
                                   case when COALESCE(f.count_co_display,0) > 0 OR COALESCE(e.count_child_reports,0) > 0 THEN 'TRUE' ELSE 'FALSE' END "HAS CHILD OR CO-DISPLAY",
                                   case when COALESCE(f.count_co_display,0) > 0 THEN 'Has Co-Display' ELSE 'No Co-Display' END "HAS CO-DISPLAY",
                                   h.Dashboard_Name as "DASHBOARD_NAME",
                                   COALESCE(i.Broadcast_Count,0) as "COUNT_BROADCAST",
                                   lastaccesseddate as "LASTACCESSEDDATE",
                                   x.FILTER_COUNT as "FILTER_COUNT"
      from ReportHeader a
                    LEFT OUTER JOIN Person b on a.Ipcreator = b.IpPerson
                    LEFT OUTER JOIN reportview rv on a.viewid = rv.viewid
                    LEFT OUTER JOIN (select RH.ReportName,RH.reportid, count(RF.fieldtemplateid) as FILTER_COUNT from ReportFilter RF, ReportHeader RH
                                                                              WHERE RF.ReportId = RH.ReportId AND RH.ReportStatusCode = 'OPEN'
                                                                              group by RH.ReportName,RH.reportid
                                   )x on x.reportid = a.reportid       
                    LEFT OUTER JOIN reportfilter rf on a.reportid = rf.reportid
                    LEFT OUTER JOIN (select reportid, COALESCE(STRING_AGG(ChartType, ','),'TABLE') ChartType from (
                                   select distinct reportheader.reportid,Chart.ChartType
                                   from ReportHeader left outer join Chart on Reportheader.ReportId = Chart.Reportid
                                   where ReportStatusCode ='OPEN' AND RoleCode !='SUBQUERY' AND ReportName !='Draft Report'
                                   ) C1 group by C1.reportid) c on a.reportid = c.reportid
                    LEFT OUTER JOIN (select s.reportid,count(1) count_calculated_fields from (
                                   select distinct a.ReportId, b.RefRltshpTypeCode, b.ValidationFunctionName, AssociatedReportId,'ReportField' UsedIn
                                   from reportheader a , ReportFieldTemplate b, Reportfield c
                                   where a.ReportStatusCode='OPEN' and a.viewid=b.viewid and c.reportid=a.ReportId and c.FieldTemplateId=b.FieldTemplateId
                                   and (b.ValidationFunctionName is not null or b.SQLClause is not null or (b.AssociatedReportId!=0 and b.AssociatedReportId is not null))
                                   union
                                   select distinct a.ReportId, b.RefRltshpTypeCode, b.ValidationFunctionName, AssociatedReportId, 'ReportFilter' UsedIn
                                   from reportheader a , ReportFieldTemplate b, ReportFilter c
                                   where a.ReportStatusCode='OPEN' and a.viewid=b.viewid and c.reportid=a.ReportId and c.FieldTemplateId=b.FieldTemplateId
                                   and (b.ValidationFunctionName is not null or b.SQLClause is not null or (b.AssociatedReportId!=0 and b.AssociatedReportId is not null))
                                   )s group by s.reportid ) d on d.reportid = a.reportid
                    LEFT OUTER JOIN (select distinct reportheader.Reportid, count(reportassociate.childreportid) count_child_reports
                                   from Reportheader, reportassociate
                                   where reportheader.reportid=reportassociate.parentreportid and reportheader.Drillcode NOT IN ('NODRILL') AND
                                   reportheader.ReportStatusCode ='OPEN' AND reportheader.RoleCode !='SUBQUERY' AND reportheader.ReportName !='Draft Report' AND reportassociate.rltshptypecode in ('DRILL','CONDITIONALDRILL','CONDITIONALDIRECT','DIRECT')
                                   group by reportheader.Reportid ) e on e.reportid = a.reportid
                    LEFT OUTER JOIN (select ReportAssociate.ParentReportId, count(distinct Reportheader.reportid) count_co_display
                                   from ReportHeader, ReportAssociate
                                   where ReportHeader.ReportId=ReportAssociate.ChildReportId
                                   group by ReportAssociate.ParentReportId
                                   ) f on f.parentreportid = a.reportid
                    LEFT OUTER JOIN (select ReportId, count(1) count_dbfn from (
                                   select a.ReportId, a.ReportName, b.RefRltshpTypeCode, b.ValidationFunctionName, AssociatedReportId,'ReportField' UsedIn
                                   from reportheader a , ReportFieldTemplate b, Reportfield c
                                   where a.ReportStatusCode='OPEN' and a.viewid=b.viewid and c.reportid=a.ReportId and c.FieldTemplateId=b.FieldTemplateId
                                   and (b.ValidationFunctionName is not null or b.SQLClause is not null or (b.AssociatedReportId!=0 and b.AssociatedReportId is not null))
                                   union
                                   select a.ReportId, a.ReportName, b.RefRltshpTypeCode, b.ValidationFunctionName, AssociatedReportId, 'ReportFilter' UsedIn
                                   from reportheader a , ReportFieldTemplate b, ReportFilter c
                                   where a.ReportStatusCode='OPEN' and a.viewid=b.viewid and c.reportid=a.ReportId and c.FieldTemplateId=b.FieldTemplateId
                                   and (b.ValidationFunctionName is not null or b.SQLClause is not null or (b.AssociatedReportId!=0 and b.AssociatedReportId is not null))
                                   )s where upper(s.ValidationFunctionName) like '%DBFN%'
                                   group by s.reportid
                                   ) g on g.reportid = a.reportid
                    LEFT OUTER JOIN (select report.ReportId, STRING_AGG( distinct Reportgroup.ShortDescription,',') as Dashboard_Name from reportGroup,ReportAssociate,
                                   ( Select ReportId, ReportName from ReportHeader) AS Report
                                   where ReportGroup. GroupId= ReportAssociate.ReportGroupId AND ReportAssociate.ChildReportId = Report.ReportId
                                   and reportgroup.statuscode != 'ARCHIVED'
                                   group by report.ReportId
                                   ) h on h.reportid = a.reportid
                    LEFT OUTER JOIN (select Count(Broadcastid) as Broadcast_Count, Reportname ,Reportheader.Reportid
                                   from ReportBroadcast,ReportHeader,TaskSchedule
                                   where ReportBroadcast.ReportId=ReportHeader.ReportId
                                                                                                            AND ReportBroadcast.BroadcastId = TaskSchedule.ScheduleUnitId
                                                                                                            AND ReportStatusCode ='OPEN' AND ReportName !='Draft Report'
                                                                                                           AND RoleCode !='SUBQUERY'
                                   group by ReportHeader.ReportName,Reportheader.Reportid
                                   ) i on i.ReportId=a.ReportId
                    LEFT OUTER JOIN
                                   (select rh.reportid,
                                   rh.reportname,
                                   max(b.eventdate) as lastaccesseddate
                                   from reportheader rh
                                   left outer join
                                   (select contentid,eventtypecode,eventcode,eventid,eventdate from event union
                                   select contentid,eventtypecode,eventcode,eventid,eventdate from EventArchive) b
                                   on rh.reportid=b.contentid and
                                   EventTypeCode='REPORT' and
                                   eventcode in ('RPTRUN','DASHRUN','RPTBROADCAST') and
                                   b.eventdate <= CURRENT_DATE
                                   where rh.ReportStatusCode ='OPEN' AND
                                   rh.ReportName !='Draft Report' AND
                                   rh.RoleCode !='SUBQUERY'
                                   group by rh.reportid, rh.reportname) ea
                                   on ea.reportid=a.reportid
      where a.ReportStatusCode ='OPEN' AND
                      a.ReportName !='Draft Report' AND
                      a.RoleCode !='SUBQUERY'
      order by a.reportid, a.reportname;

      The attached sample report shows the result of running the query.

 

Tip: For faster searching, add an asterisk to the end of your partial query. Example: cert*

BMC Helix Dashboards 26.1