We recently received a case where user was not able to create a summary gadget, we have had similar cases in the past.
If you ever encounter an issue like 'There is no data to show' or 'error loading data' while creating a gadget, more likely it is due to Advanced Finds.
When you try to create a gadget, tomcat runs a select statement on the advanced finds table for saved searches.
So I identified that the issue was with some Advanced Finds record causing the 'No data to show' and 'Error loading data' while creating a Summary Gadget.
Best way to be ensure if Advanced Finds is preventing you from creating a gadget is to soft delete all the advanced finds records after keeping a DB backup with you.
Something like this:
--set AdFi_Deleted = 1
--where AdFi_FindID = 7
After following the above mentioned steps, do a metadata refresh and check if you are able to create a gadget.
If yes, you know issue lies with Advanced Finds.
Next step is to find the record in Advanced Finds table which is causing the error, best way to do that is to soft delete the record one at a time and keep checking if you are able to create a gadget. The moment you are able to create a gadget, you know that the latest record you soft deleted is the problem area.
If in case you have a huge list of Advanced Finds, I would prefer using 'Merge and Sort' or 'Divide and Conquer' approach.
Next step is to see why that particular record(s) is causing issues. Reason could be any in this case.
For example, you can check if it is because of the AdFi_SQLText column? You can check that by running the query text and see if the query is broken and not returning a sensible outcome.
NOTE: Please keep in mind the impact of soft deleting the records if you are using Advanced Finds.
Hope this article helps you with your problem!