How can I tell if a particular report is being used on any dashboards or homepages?

[Danielle25]

We are trying to do some cleanup on reports that haven't been run in the last year. I'm trying to avoid accidentally deleting some reports that may be used elsewhere such as on a Dashboard or Homepage.

I know that when a dashboard or homepage is run, it updates the "last run" field on the report stats; however, there is a case where the dashboard might not have been looked at in the last year as well. I'd like to avoid deleting reports on these aged dashboards if at all possible.

[Janel]

You're going to have a tough time without using a background script.  You may be able to get what you need from a database view or a mashup of PA sources/breakdowns, but that is not my wheelhouse.

Thanks to @Matthew Glenn for providing that script.  I did a little updating and found a few shortcuts, but it totally helped me find out how to see if a report is on a dashboard (I need to cleanup the script before posting it). 

Here are the manual steps to figure out if a report is on a dashboard.  If you're a PA master maybe you can figure out a way to make this pretty.  🙂

1. Searching by the report's name/title may result in duplicate reports (how many do you think are out there named "my groups work" or "my approvals"?) so we need to go by sys_id.
2. Go to Reports > Administration > All and find the report you want to lookup.
3. Copy the sys_id of the report.
4. Go to the "sys_portal_preferences" table.
1. Here we will need to find a Portal Section's page's sys_id so we can lookup what dashboard tab the report may be on.
2. I think this is the widget and container and page that resides on a dashboard tab.  I'm pretty sure we're figuring out what container the report is on (that thing you can drag around), what page that container is on, and then what dashboard tab that page lives on.
5. Make sure you have the Portal Section, and Value columns on the list.
6. Search for the report's sys_id in the Value column.
7. You should get 1 result.
1. If you don't get any results then the report isn't being used on a dashboard.
2. 
8. Now we have to dot-walk up through a few tables to find the sys_id of the Page it is on.
9. Click on the sys_id that appears in the Portal Section column (it is a reference!) to open the Portal Section record.
1. The same as opening the record and then opening the record for the Portal Section field.
10. Open the record for the Page field.  You will now be on a sys_portal_page record.
11. Copy that sys_id.
12. Go to the table "pa_m2m_dashboard_tabs".
13. Make sure you have the Dashboard column displayed on your list.
14. Search for Tab => Tab fields > Page => Portal Page fields > sys_id IS the sys_portal_page sys_id you copied earlier.
15. The dashboard that the report resides on will appear in the Dashboard column.

[Janel]

You're going to have a tough time without using a background script.  You may be able to get what you need from a database view or a mashup of PA sources/breakdowns, but that is not my wheelhouse.

Thanks to @Matthew Glenn for providing that script.  I did a little updating and found a few shortcuts, but it totally helped me find out how to see if a report is on a dashboard (I need to cleanup the script before posting it). 

Here are the manual steps to figure out if a report is on a dashboard.  If you're a PA master maybe you can figure out a way to make this pretty.  🙂

1. Searching by the report's name/title may result in duplicate reports (how many do you think are out there named "my groups work" or "my approvals"?) so we need to go by sys_id.
2. Go to Reports > Administration > All and find the report you want to lookup.
3. Copy the sys_id of the report.
4. Go to the "sys_portal_preferences" table.
5. 1. This table holds the "preferences" of the dashboard widget.  When you click "Edit widget" on a dashboard widget and get the little settings dialog (show border, center title, follow interactive filter, etc.).  One of the preferences that it stores is the sys_id of the report we want to find (which obviously isn't on the dialog).
6. Make sure you have the Portal Section, and Value columns on the list.
7. Search for the report's sys_id in the Value column.
8. 1. You will get a result for each widget the report appears in.
   2. This does include homepages too!
   3. If you don't get any results then the report isn't being used on a dashboard or homepage.
   4. 
9. Now we have to dot-walk up through a few tables to find the sys_id of the Page it is on (think of the sys_portal_page as the layout of all the widgets on the dashboard tab).
10. Click on the sys_id that appears in the Portal Section column (it is a reference!) to open the Portal Section record (sys_portal).
11. 1. Think of this as the widget container that you can drag around on the dashboard.
12. Open the record for the Page field.  You will now be on a "sys_portal_page" record.
13. 1. Think of this as the entire page of the dashboard tab.  Where all the widgets on the tab live.
14. Copy that sys_id.
15. Go to the table "pa_m2m_dashboard_tabs".
16. Make sure you have the Dashboard column displayed on your list.
17. Search for Tab => Tab fields > Page => Portal Page fields > sys_id IS the sys_portal_page sys_id you copied earlier.
18. The dashboard that the report resides on will appear in the Dashboard column.

[Janel]

This is the script I ended up writing.  It does take a very long time to run (probably that while within a while within a FOR ;_;) and I just haven't had the chance to go back and speed it up.  But we do have a ton of data to go through on our instance.

The script is just to find report data and at the end is an example of one way to break down the data (depending what you want to do).

Note there is a setLimit(20) in that first query!

var usedReport = []; //This array will contain the names & sys_id's of the reports that we want to see are on a dashboard.  We will add more information to each element as we figure out where it is in use.  You could replace this with a static list or a query from any other table, as long as the element structure if the same as what gets initially pushed below ({"name": reportName, "sys_id": reportsys_id})

/* The encoded query for the table containing the reports I want to find.
 * Make sure you adjust the query table and fields below to match the table (sys_report, report_stats, etc.) and name and sys_id fields. 
 */
var runQuery = "sys_updated_onONToday@javascript:gs.beginningOfToday()@javascript:gs.endOfToday()";
//Find reports that haven't been ran in X amount of months from the report_stats table.  I'm using "reports ran today" to give myself a smaller list for testing.

//var runQuery = 'created_by_user.active=false^sys_created_bySAMEASsys_updated_by^user!=group^ORuser=NULL^user!=GLOBAL^ORuser=NULL';
//Find reports on sys_report that were created by and last updated by, an inactive users, and that are not shared with anyone.


//Find all the reports that meet our query.  This could be any table or set of data.  Just make sure the field names match the table you're using.
var reportQuery = new GlideRecord("report_stats"); //Get the report names and sys_ids from this table
reportQuery.addEncodedQuery(runQuery);

reportQuery.setLimit(20); //For testing.  Make sure you aren't printing out anything in the loops if you comment this out!

reportQuery.query();
while (reportQuery.next()) {
	usedReport.push({
		"name": reportQuery.getDisplayValue().toString(),
		"sys_id": reportQuery.sys_id.toString(),
		"dashboards": []
	});
	/* for report_stats
        usedReport.push({
            "name": reportQuery.report_sys_id.getDisplayValue().toString(),
            "sys_id": reportQuery.report_sys_id.toString(),
            "dashboards": []
        }); */
}
var startT = new GlideDateTime(); //So we can track runtime from a scheduled job
var dur1 = new GlideDuration();
dur1.setValue(startT);
gs.log("Number of reports to check if they are on a dashboard: " + usedReport.length, 'ReportUsage');

var reportOnly = []; //This array will keep track of reports that are in our initial query, but are not on any dashboards.  If the intention were to delete unused reports, this is the array to go against.
var dboardCount = 0; //Some analytics.  How many total dashboards a report appears on

//For each report that matched our query, figure out where it may be used and gather more information about it.
for (var r = 0; r < usedReport.length; r++) {
	var portalPref = new GlideRecord("sys_portal_preferences"); //These are the little things you can edit on the widget.  Like show border, text alignment, title color, etc.  This is the only place that holds the report's sys_id.
	portalPref.addQuery('name', 'sys_id');
	portalPref.addQuery('value', usedReport[r].sys_id);
	portalPref.addNullQuery('portal_section.page.user'); //If this is populated then it is a homepage, so we are ignoring them.
	portalPref.query();

	if (portalPref.hasNext()) {
		var usedon = "\nFound " + portalPref.getRowCount() + ' portal pref records with the sys_id of the report "' + usedReport[r].name + '" (' + usedReport[r].sys_id + ')\n"' + usedReport[r].name + '" is being used on these dashboards:';

		while (portalPref.next()) { //Sometimes has a duplicate page due to homepages
			if (!portalPref.portal_section.page.sys_id.nil()) { //If there is not a value under portal_section.page.sys_id then it was used somewhere else.  Possibly an orphan...  Or is a homepage?

				var dboard = new GlideRecord('pa_m2m_dashboard_tabs');
				dboard.addQuery('tab.page.sys_id', portalPref.portal_section.page.sys_id);
				dboard.orderBy('dashboard.name');
				dboard.query();
				while (dboard.next()) {
					dboardCount++;
					usedon += '\nDashboard: ' + dboard.dashboard.getDisplayValue() + ' (' + dboard.dashboard.sys_id + ')\n  --Tab: ' + dboard.tab.getDisplayValue() + ' (' + dboard.tab.sys_id + ')\n    --Page ID: ' + dboard.tab.page + ' (' + dboard.tab.page.sys_class_name + ')\n';
					usedReport[r].dashboards.push({
						"dashboardname": dboard.dashboard.getDisplayValue(),
						"dashboardid": dboard.dashboard.sys_id.toString(),
						"portalpage": dboard.tab.page.toString()
					});
				}
			} else {
				//We could probably just skip these, but I'm not sure if these are orphaned records or what their deal is
				//gs.log("\n\nI'm empty!  portalPref.portal_section.page.sys_id is blank.  Find me by sys_id on the table sys_portal: " + portalPref.portal_section + ".  \nI'm a portal_section this report and I don't go anywhere: " + usedReport[r].name + ' (' + usedReport[r].sys_id + ' \n', 'ReportUsage'); //Maybe just push them to a new list and figure out something to do with them later?
			}
		}
		/* Uncomment the line below to get some details about the report that is on a dashboard (and which dashboard).
         * Be mindful if you are looking at a lot of data! 
         */
		//gs.log(usedon, 'ReportUsage'); //If there is a count for "being used on" and no dboards, it is a homepage, but we filtered those out right away.

	} else {
		/* Uncomment the line below to see the name of the report from our query that is not used on a dashboard.  
         * Be mindful if you are looking up a lot of data!
         */
		//gs.log("This report is not being used on a dashboard: " + usedReport[r].name, 'ReportUsage');

		reportOnly.push({
			"name": usedReport[r].name.toString(),
			"sys_id": usedReport[r].sys_id.toString()
		});
		usedReport.splice(r, 1); //Remove the report that isn't on a dashboard from our list.  It now lives in "reportOnly"
		r--; //Adjust the index
	}
} //End of for
var endT = new GlideDateTime();
var dur2 = new GlideDuration();
dur2.setValue(endT); //.getDisplayValue());
var answer = dur2.subtract(dur1);
gs.log("\nReport finder finished finding reports.  Runtime was " + answer.getDisplayValue() +
    "\nThis many reports from the query are not on a dashboard: " + reportOnly.length +
    "\nThis many reports from the query are on a dashboard: " + usedReport.length +
    "\nThis many dashboards are using reports from the query.  This may include the same report on multiple dashboards:  " + dboardCount, 'ReportUsage');
//Now it is up to you to decide what to do with the information.
//The usedReport array contains a bunch of information about reports that are being used on a dashboard from the original query.  There is an example below on how to get that data out and see what dashboards those are used on.
//The reportOnly array contains the name and sys_id of reports from the original query that are not being used on a dashboard.  
//So if I were targeting reports that haven't been used in 6 months (from original query) to delete, I'd delete the items found in reportOnly.  Then I'd use the items in usedReports to investigate next steps (if I were looking for reports that haven't been executed and they are on dashboards, the dashboards I found are probably dead dashboards and should be deleted too).  This would be wonderful if I were looking for reports created by inactive people or to find what public reports are on dashboards.

/*
for (var k = 0; k < usedReport.length; k++) {

	//If the report appeared on multiple dashboards you will need another FOR loop to access that information (or you can manually specify the indexes)
	if(usedReport[k].dashboards.length > 1){
		gs.log("This dashboard is on a homepage: " +usedReport[k].name, 'ReportUsage');
		for(var index in usedReport[k].dashboards){ //index is just a number
			gs.log("\nusedReport[k].dashboards[index]:\ndashbaordname: " +usedReport[k].dashboards[index].dashboardname+ '\ndashboardid: ' +usedReport[k].dashboards[index].dashboardid+ '\nportalpage: ' +usedReport[k].dashboards[index].portalpage, 'ReportUsage');
		}
	}else{ //You can just use 0 if there is only 1
		gs.log("\nusedReport[k].dashboards[index]:\ndashbaordname: " +usedReport[k].dashboards[0].dashboardname+ '\ndashboardid: ' +usedReport[k].dashboards[0].dashboardid+ '\nportalpage: ' +usedReport[k].dashboards[0].portalpage, 'ReportUsage');
	}

} //End of For
*/

[Anonymus]

Hi, Are you still using this ? Is there any update on this script?

[Janel]

Sorry, no.  But I may have a use case soon to rework it and use it again.