Re-assign a My List

A question recently came up of how to re-assign a My List to another user (or how to add a list directly to another user's My Lists page). The current functionality always reverts a list to the creator's control, so the easiest way to do this is to change the creator of the list in the database. Using a bit of reverse engineering of Nick Airdo's Edit Page functionality (Link Here), and combining it with an unorthodox use of the Report Grid From Query module, I think I have a solution to the problem:

1. Set the page ID (3186) in the SQL code below to your My Lists page.

CREATE PROCEDURE dbo.cust_sp_reassign_list
@reportid INT
,@newuser VARCHAR(50)
AS
  
  IF EXISTS (
    SELECT TOP 1 1 
    FROM secu_login 
    WHERE login_id=@newuser)
  BEGIN
    UPDATE list_save_reports
    SET created_by=@newuser
    WHERE report_id=@reportid
    
    SELECT 
    0 AS ordering
    , 'Report ID '
      +CONVERT(VARCHAR,@reportid)
      +' transfered to user '
      +UPPER(@NEWUSER) AS Result
    UNION
    SELECT
    1 AS ordering
    ,'<a href="default.aspx?page=3186">Click here to return to My Lists</a>' AS Result
    ORDER BY ordering
  END
  ELSE
    SELECT 
    0 AS ordering
    , 'User '
      +UPPER(@NEWUSER)
      +' not found!' AS Result
    UNION
    SELECT 
    1 AS ordering
    , '<a href="default.aspx?page=3186">Click here to return to My Lists</a>' AS Result
    ORDER BY ordering

2. Run the SQL code to generate the cust_sp_reassign_list stored proc.

3. Create a new page with a Report Grid From Query module in it, with the two parameters listed for the SP below, suppressing the "ordering" column.

4. Set the page ID (3292) in the javascript below to the page ID of the newly created page from step 3.

5. Put the following javascript in an Advanced HTML Text module (with Evaluate Query Parameters = True) on the Report View page. Don't forget to give security access to this new module to anyone you want to be able to reassign Lists.

<script language="javascript" type="text/javascript">
function reassignList()
{
  var newuser = prompt("Enter the new user ID:");
  window.location = "default.aspx?page=3292&reportid=##REPORTID##&newuser=" + newuser;
}
</script> <a onclick="reassignList()" href="#">Assign This List To A New User</a>

This will give you a link at the top of each report to allow you to re-assign that specific report to a new user. Note that you need to know the exact username of the new user, as the stored proc will reject an invalid username.