How Alerts can work for Overdue Issues

Right now SharePoint does not allow a built in alert for overdue
issues, so a different solution has been made. Currently on an Issue
list the "Assigned to" field can be setup to notify the Assigned to
if there is a change in the Issue status, not if there are overdue
Issues. This Solution involves:

1. Adding a choice list for every Issue list in the Sharepoint sites.
a. Add a list called "Escalate to" to every Issue list and
the List should be composed of all of the Assigned to users. This list
will be in the same nvarchar column in the Userdata table for every
Issue List on the SharePoint Sites.

2. Exporting a Table from the SharePoint Portal SITE database via a
DTS package to the SQL report server (The only reason why this was done
is because SQL Reporting Services does not do well with SQL servers
that were installed only with Windows Authentication. SQL Reporting
services works better with SQL Authentication

3. Making a custom view of the data that is needed on the report
server

4. Making a new SQL Report for SQL Reporting Services via Visual
Basic.NET

5. Schedule the Daily notification for the Assigned Users via the
Escalation Users


I. Exporting the table (you only need to do this if you installed the
SQL server using Windows Authenication only)
a. The Table is Userdata (All of the Issue Lists are in this
table). This table is in the SITE database on the SharePoint server
b. Make a DTS package
c. Right click the table and choose All Tasks/Export Data
d. Choose a different destination SQL server and choose the
database that you would like to copy it to.
e. You will want to save the DTS package, Run Now, and set it to
run once a day
f. Confirm that the table was copied to the other SQL server
g. You can rename the table in the destination database if you
would like, but you will need to edit the DTS package to make sure
that the name name is correct in the script and that the destination
connection also reflects the change
h. After this is done you will need to edit the DTS package, so
that every time that it is ran it will drop the destination table and
then remake it.
1. Open up SQL Enterprise Manager and go to the table that
was exported and right click it and choose All Tasks/Generate SQL
Script.
2. Choose the Options tab and check the boxes:
i. Script object-level permissions
ii. Script Indexes
iii. Script PRIMARY keys, FOREIGN keys, defaults, and
check constraints
iv. Windows text (ANSI)
3. Click OK and save the script - name the file to one
that you will remember, maybe the table name
4. Browse and open up the newly created file
5. Copy the contents of the file
6. Open up SQL Enterprise Manager and go to the Database
Transformation Services\Local Packages and Double click your DTS
package.
7. Delete the Create Table SQL Task
8. Drag over a new Execute SQL Task and paste in the
contents of the sql script
9. Change the Existing connection to the destination -
IMPORTANT - IF YOU CHOOSE THE SOURCE TABLE YOU MAY ACCIDENTALLY
DELETE THE WRONG TABLE!!!!
10. Rename the Execute SQL Task accordingly - Click ok when
done
11. Hold you CTRL key down and click on the Execute SQL Task
and the first Connection, then choose Workflow on the Menu and choose
"On Success"
12. Reopen the Execute SQL Task to confirm that you have the
correct Connection (the destination table)

II. Setup a View in the Desination Database
a. Make a new user in the Destination SQL Database, something
like "reports" and a farily easy password. This user will be the one
that will have access to the SQL reports so you will want that user to
have limited rights.
b. Browse to the database and Create a new View under the Views
option
c. Add the new table from SharePoint and select the fields (You
will need to confirm each field before choosing, just to make sure that
the you have chosen the correct fields.
1. Nvarchar1 = the "Active, or Closed, etc..." of the
Issue that was made
2. Nvarchar3 = The Title of the Issue
3. Datetime1 = The Due Date of the Issue
4. Tp_IsCurrent = Is the field that keeps track of the latest
version of the Issue. The latest version has a "1" in it.
5. Ntext2 = The body of the Issue
6. Nvarchar5 = A custom field added to the Issue list that
was named "Escalate to". This is a choice drop down of the users
that can be notified of the overdue issues.
d. Set the permission on the view by right clicking the view and
choose properties
e. Click on the permissions button and give the newly made SQL
account SELECT

III. Setting up a SQL Report via VB.NET
a. Make a new Business Intelligent Project
b. When selecting the data source enter your server name,
database, and authentication credentials. You will need to save the
password or every time you run the report it will ask you for the
password. This is why you needed to make a SQL account that could be
used that did not have access to anything, but the view.
c. When you edit the SQL Query String you will need to add the
new view and the fields mentioned above.
d. Configure the rest of the report settings
e. Add the WHERE statement (WHERE (datetime1 < { fn NOW() })
AND (nvarchar1 = 'Active') AND (nvarchar5 = @User) AND (tp_IsCurrent =
'1'))
1. Datetime is set to bring back dates less than today
2. Nvarchar1 is set to bring back only Active issues
3. Nvarchar5 is set to bring back the issues related to the
Escalated user (this is a variable that has to be entered) - This
will bring back all of the Escalated users in every Issue List on the
SharePoint Sites
4. Tp_IsCurrent is set to bring back the latest of each issue
5. Save the file then publish it to the SQL Reporting Server
IV. Setup subscriptions on the Report
a. Go to the report that was published and choose the
Subscriptions TAB
b. Add a new Subscription and enter the following information
c. Add the email addresses that you would like the overdue issues
to be sent to
d. Customize the Subject line (add "for username")
e. Fill in the User field of the name that you want (this is the
Escalation field)
f. Set the schedule - typically if you run the DTS package once
a day you can then run this subscription once a day
g. You may want to make one subscription per Assigned to
personnel.



Let me know if this helps you! :-)

.