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.

What date is next Tuesday?

So I was working on a project, and for part of the project, I needed to know what date was the next specific weekday after a given date. Not only that, but the "specific weekday" could be a range of possible weekdays (e.g. "When is the next Monday or Thursday after today?"). After a bit of Googling, I couldn't find a ready-made solution to this, so I decide to take a crack at it.
The following User-Defined Function (since I wanted to make this code reusable) is the fruit of my labor:

CREATE FUNCTION dbo.cust_funct_nextdate 
(@startDate DATETIME, @weekdays VARCHAR(10))
RETURNS DATETIME
AS
BEGIN

 DECLARE 
 @dw INT
 ,@returndate DATETIME
 
 DECLARE @days TABLE (day_of_week INT)

 IF PATINDEX('%U%',@weekdays)
  INSERT INTO @days SELECT 1

 IF PATINDEX('%M%',@weekdays)
  INSERT INTO @days SELECT 2

 IF PATINDEX('%T%',@weekdays)
  INSERT INTO @days SELECT 3

 IF PATINDEX('%W%',@weekdays)
  INSERT INTO @days SELECT 4

 IF PATINDEX('%R%',@weekdays)
  INSERT INTO @days SELECT 5

 IF PATINDEX('%F%',@weekdays)
  INSERT INTO @days SELECT 6

 IF PATINDEX('%S%',@weekdays)
  INSERT INTO @days SELECT 7

 SET @dw=(
  SELECT MIN(day_of_week) 
  FROM @days 
  WHERE day_of_week>DATEPART(DW,@startDate))
  
 IF (@dw IS NULL)
  SET @returndate=(
   SELECT DATEADD(dd,7-(datepart(DW,@startDate)
    -(SELECT MIN(day_of_week) FROM @days))
    ,@startdate))
 ELSE
  SET @returndate=(
   SELECT DATEADD(dd,(
    SELECT @dw-DATEPART(dw,@startDate))
    ,@startDate))
  
 RETURN @returndate
END

As you can see, you pass it a date and a string field containing the letters representing the days of the week you want it to evaluate, following a simple "MTWRFSU" format for Monday through Sunday. It then evaluates the string value to determine what days of the week you want it to look for, and then manipulates the date input to be the next upcoming value in your supplied list of weekdays.

Just a handy tool I thought I'd share for anyone who needs it