Technical FAQs

Ask a Question

EEM v5.0 - SQL Query to Find Saved Reports in Folders

Issue
When attempting to locate a specific saved report or reports, it can often be difficult to find the particular saved report(s) if there are many folders. 

Product
ION EEM 5.0

Environment
ION EEM 5.0 Installation

Cause
Navigating through the EEM web interface can be time very consuming when there are many saved reports within the reporting folder structure.

Resolution
*Warning: Irreparable database damage can occur. This procedure should only be performed by users familiar with SQL Server Management Studio. Databases should be backed up prior to performing this procedure.*

The following query will obtain the folder location of the reports along with any of their associated subscription information.
NOTE: This query will not work for versions prior to EEM v5.0

USE ION_EEMData  
 
SELECT ReportID, DisplayName  
INTO #FolderNames       
FROM RPT_Report  
WHERE IsSubFolder = '1'  
 
SELECT    #FolderNames.DisplayName AS "FolderName"  
          ,RPT_Report.DisplayName AS "ReportName"  
          ,RPT_Subscriptions.Name AS "SubscriptionName"  
          ,RPT_Subscriptions.Frequency AS "ReportFrequency"  
          ,RPT_SubscriptionRender.OutputFormat AS "ReportFormat"  
          ,RPT_Subscriptions.Frequency  
          ,RPT_Subscriptions.SchedDescription AS "Schedule"  
          ,RPT_EmailSubscriptionDelivery.DistributionListCSV AS "Recipients"  
                       
INTO #ReportSubscriptionList  
FROM RPT_Subscriptions  
LEFT JOIN RPT_Report  
      ON RPT_Subscriptions.ReportID = RPT_Report.ReportID  
LEFT JOIN #FolderNames  
      ON RPT_Report.SubfolderID = #FolderNames.ReportID  
LEFT JOIN RPT_SubscriptionRender  
      ON RPT_Subscriptions.SubscriptionRenderId = RPT_SubscriptionRender.SubscriptionRenderId   
LEFT JOIN RPT_EmailSubscriptionDelivery  
      ON RPT_Subscriptions.SubscriptionDeliveryId = RPT_EmailSubscriptionDelivery.SubscriptionDeliveryId  
 
SELECT  ReportName  
       ,FolderName  
       ,ReportFormat  
       ,Frequency  
       ,Schedule  
       ,SubscriptionName  
       ,Recipients  
FROM #ReportSubscriptionList  
ORDER BY Frequency, ReportName, FolderName  
 
DROP TABLE #ReportSubscriptionList  
DROP TABLE #Foldernames


** see attachment containing the SQL script text
 
Was this helpful?
What can we do to improve the information ?