Today’s #TipTuesday post is about troubleshooting an issue with the My Reports home page area/pane and how to remove SmartList favourites from there. The specific scenario here related to favourites that were “stuck” in MyReports after an upgrade to GP 2013 a few years ago. I was reminded of this when I saw this post on the Microsoft Dynamics community forum and someone asked me if I kept the scripts I used to fix this issue.
What’s the issue?
After a client’s GP 2013 upgrade, a couple of users noted that the SmartLists they had saved under their My Reports pane on the home page weren’t working. It turns out the underlying Favourite was missing, and we didn’t find out why this happened. Ultimately the important part was we could not remove the reference in My Reports using any out of the box functionality in Dynamics GP. If we clicked in the Edit pencil in MyReports, the items we saw there weren’t in the navigation list that came up so we had no way to remove them, within the normal functionality one might use to do so!
What would specifically happen if the user tried to use the shortcut, it would open Smartlist to the right Smartlist “object” (i.e. the “yellow folder level”) but not the specific favourite itself that it was linked to. From the user’s perspective, the link was broken, it only half worked.
What changed in GP 2013?
Starting with GP 2013, when a user creates a new Smartlist Favourite saved as “Visible To” User, i.e. themselves, it then automatically appears on My Reports. That reference can be removed from My Reports via the edit functionality and it can be removed by changing the Visible To in the Smartlist Favourite to something other than “User” as well.
However, for any items that were there BEFORE the upgrade to GP 2013, there was no option to remove them.
SQL to the rescue!
There are some times when you have no choice but to resort to SQL to address an issue and this is one of those times. The table that stores a user’s My Reports pane contents is the SY70700 table. This stores all types of reports, not just SmartLists. Here’s a simple query to view a list of what’s in the table:
SELECT USERID, CMPANYID, MyReportName, DEX_ROW_ID
This will return something like this, in my case, a regular GL trial balance report and 2 smartlists – one added as a favourite visible to User and one as a manually added MyReport.
If something is stuck, you can add a WHERE clause to the query to narrow it down specifically by USERID, and then cross check the exact “MyReportName” from a screenshot from the user vs. what’s in the table. My preference is to then use the DEX_ROW_ID as the filter for a DELETE script which may look like this, if I want to remove the “Accounts created this month” favourite from My Reports. (I also opt to include the USERID even though I shouldn’t need to as DEX_ROW_ID is unique).
WHERE USERID = 'sa' and DEX_ROW_ID = 2
If you’re using this script for real, you will replace the USERID ‘sa’ with the user in your system and one or more DEX_ROW_ID’s based on the first select script. My system database is the default DYNAMICS db but YMMV if you have something different, it’s the system database where this table is.
Of course, if you are doing this for real, my recommendation is test your WHERE clause with the select script first. If you are getting only the results you want to see with that, then you should be safe.
The last suggestion, since this is editing a table in the system database, and not all organizations have a separate standalone environment, please make a backup and/or use someting like this to make a quick “table” backup. That way if you delete more than you expect or someting doesn’t work, the data is there to script back in with an insert statement.
When you’re done, if all goes well, you can drop the backup table so you don’t have clutter in your database.
Hopefully someone out there finds this useful!