Audit of existing queries, reports, dashboards

Options
Milena Damianova
Milena Damianova
Fifth Anniversary Photogenic First Comment
in Blackbaud Raiser's Edge NXT®: General Discussion
In a period of transition on the team I was tasked with doing an inventory of reports, queries, lists, dashboards, etc. that we have accumulated in RE over 10+ years, most of these not used for 2+ years and created by staff long gone. How can I export the complete list of queries, reports, etc. with their create date, create user, modify date, modify user, last run date, last run user. Any best practices around archiving old reports? We use the hosted environment so SQL unfortunately is not an option (from my understanding). Thanks in advance. 

 
Tagged:

Comments

  • Dennis Ladnier
    Dennis Ladnier
    Eighth Anniversary 25 Likes 10 Comments Photogenic
    Milena, I had a good solution until I read you were hosted (as you noted, no SQL access) [insert sad face]
  • JoAnn Strommen
    JoAnn Strommen Community All-Star
    Ancient Membership 2,500 Likes 2500 Comments Photogenic
    For Query, Reports and Export you can right click on the list in a folder and export the list to excel.  Have all the column you want visible and they will export. 

    Downside is you have to do it for each folder so you may want to name file with type and folder name.  And Reports does not have a last run date. To me issue is that name really doesn't tell much either. 


    NOTE: For Query the last run date may not be accurate.  I was very surprised to find when I've gone in and run predecessors queries the date does not update UNLESS I make a change to something in the query.  

    I'm looking at a similar clean up project in the future - will be tough because most have name only, no description. I have hundreds that based on last date run are over 5 years old.  BUT I found that one report admin assist uses several times a week is one of those 'old' queries.  It was set up for her to run and date does not change as she makes no changes to it. I'd be in big trouble if I had deleted it. 


    Don't think it works for Dashboards. You can certainly do screen shots of ones you can't export grid for. 


    I've been thinking about asking all users to Save As the queries they are using so they will be created by current staff with current date but I know some are only used once a year for reports. 


    Will be following your post to see if anyone has some good suggestions.
  • Alan French
    Alan French
    Tenth Anniversary 1,000 Likes 500 Comments Name Dropper
    dennis ladnier‍ would you mind sharing your solution, for the benefit of those of us who do have SQL access? Our queries and reports are in desperate need of pruning after 6 years.

     

    JoAnn Strommen:


    NOTE: For Query the last run date may not be accurate.  I was very surprised to find when I've gone in and run predecessors queries the date does not update UNLESS I make a change to something in the query. 

    I've found the same is true of imports - I have one that I run in validation mode every week yet it has a 'last run' date of 2 years ago. I've put a huge note on it saying "THE DATE LAST RUN IS INACCURATE - DO NOT DELETE" just in case anyone gets any ideas about doing some spring-cleaning! ?

  • Rachel Cavalier
    Rachel Cavalier Community All-Star
    Seventh Anniversary 500 Likes 500 Comments Name Dropper
    I've been thinking about this, even though we've only been using RE for a little over a year. So far, I've been thinking that at some point, armed with a detailed list of all the queries and what folders they're in, I'll then move the ones that don't seem to get used into a "To Delete + date" folder to just...hang out there for a while and then, after enough time where no one has said "oh hey, where's that query I was using for this thing?" it'll be deleted. I suspect it might have to hang out in that pending delete folder for a year, just to make sure.


    When we do it, we'll let all our users know that this is happening and what to do if one of the queries they use sporadically has been marked for deletion.
  • JoAnn Strommen
    JoAnn Strommen Community All-Star
    Ancient Membership 2,500 Likes 2500 Comments Photogenic
    LOL - don't know about your office. I'd need to be ready for dealing with frustrated staff when their report/export/queue/other process doesn't run because query can't be found. 


    I be sure to let them know you're not deleting it, just moving it and it can be moved back. 
  • Rachel Cavalier
    Rachel Cavalier Community All-Star
    Seventh Anniversary 500 Likes 500 Comments Name Dropper
    Haha oh for sure! An email maybe a month before I think about doing it, to encourage them to delete some of their own queries that they know can go. Another a couple of weeks later to remind them and then a few when the deed's done!


    Handily, there's that box at the bottom that says what reports and exports use the query, which will help and I expect I'm going to be picking a "volunteer" to go through the queries in their team's area.

Categories