#19 Employee Directory Sharepoint Webpart Pt 1 – Stored Procedure

Starting with a new proc to select data of off the EM, Employee Custom Tab Fields and a union with a Auxilary data table to add in such things as printers, conference rooms, local police,  fire etc. I will build on this stored procedure defining a datagrid with filter controls in a Sharepoint webpart.

Virtualized Dev Environment: SQL 2005, Windows 2003 server, Visual Studio 2008, Sharepoint Extensions

This proc allows wildcard searches on fields and does not filter if a null is passed.  The union to the auxiliary table poses some challenges so I created a UDF dbo.IgnoreNullParm to return ‘1′ or ‘0′ to effectively turn off the union ‘leg’ when we are passed a filter parm that does not exist for that table (i.e. Initials).

Proc Def:

Create Procedure [dbo].[searchEmployees]
(
@FirstName        nvarchar(50)
,@LastName        nvarchar(50)
,@Initials              nvarchar(03)
,@Phone               nvarchar(20)
,@Office                nvarchar(50)
,@Dept                  nvarchar(50)
,@EmpNum        nvarchar(04)
)
as
Select
EM.Employee
, isNull(EM.PreferredName,EM.FirstName) 		as FirstName
, (RTRIM(EM.LastName) + ' ' + isNull(Suffix,'')) 	as LastName
, EM.MiddleName
, EM.Org                                                    as PrimaryDept
, EM.Region
, EM.WorkPhone
, EM.WorkPhoneExt
, EM.MobilePhone
, ECTF.Cust5ULPhysical                                as Office
, ECTF.custSecondaryDepartment                as SecondaryDept
From
EM
Join EmployeeCustomTabFields ECTF on ( ECTF.Employee = EM.Employee )
Where
EM.Status = 'A'
And    (         EM.FirstName     like  Case When (@FirstName is null)    Then '%' Else RTRIM(@FirstName)    + '%' End
OR    EM.PreferredName      like  Case When (@FirstName is null)    Then '%' Else RTRIM(@FirstName)    + '%' End
)
And    EM.LastName         like  Case When (@LastName is null) Then '%' Else RTRIM(@LastName)    + '%' End
And    SUBSTRING(EM.FirstName,1,1)
+  RTRIM(Case When(DATALENGTH(EM.MiddleName) = 0) Then '' Else  SUBSTRING(EM.MiddleName,1,1) End )
+  SUBSTRING(EM.LastName,1,1)  LIKE Case When (@Initials is null) Then '%' Else  @Initials  End
And    (        EM.WorkPhone    like '%' + isNull(@Phone,'%') + '%'
OR    EM.WorkPhoneExt like '%' + isNull(@Phone,'%') + '%'
OR    EM.MobilePhone    like '%' + isNull(@Phone,'%') + '%' )
And ECTF.Cust5ULPhysical = isNull(@Office, ECTF.Cust5ULPhysical)
And ( EM.Org like '%' + isNull(@Dept,'%') + '%'
OR ECTF.custSecondaryDepartment like '%' + isNull(@Dept,'%') + '%'  )
And EM.Employee like  isNull(@EmpNum,'%') + '%'
 UNION ALL
Select  EmpNum                     as Employee
, FirstName
, LastName
, ''                            as MiddleName
, ''                            as PrimaryDept
, ''                            as Region
, Phone                        as WorkPhone
, PhoneExt                        as WorkPhoneExt
, ''                             as MobilePhone
, ECTF_Cust5ULPhysical            as Office
, ''                            as SecondaryDept
From OfficeAux OA
Where
OA.FirstName        like  Case When (@FirstName is null) Then '%' Else RTRIM(@FirstName)    + '%' End
And    OA.LastName         like  Case When (@LastName is null)     Then '%' Else RTRIM(@LastName)        + '%' End
And '1'    = dbo.IgnoreNullParm(@Initials)        <em> -- if parm is null ignore it otherwise this effectively 'turns off union' if parm is not null</em>
And '1'    = dbo.IgnoreNullParm(@Dept)           <em>-- if parm is null ignore it otherwise this effectively 'turns off union' if parm is not null</em>
And    (        OA.Phone        like '%' + isNull(@Phone,'%')  + '%'
OR    OA.PhoneExt    like '%' + isNull(@Phone,'%')  + '%')
And ECTF_Cust5ULPhysical = isNull(@Office, ECTF_Cust5ULPhysical)
And EmpNum  like  isNull(@EmpNum,'%') + '%'

UDF

Create function [dbo].[IgnoreNullParm]
(
@Field  nvarchar(50)
)
Returns bit
-- Sends back True '1' if Parm is null
-- Setup in where clause as Where '1' = IgnoreNullParm(@parm)
-- Allows you to effectively turn off a union 'leg' if a parm is not null and ignore it if not null
as

Begin

Declare @bReturn as  bit

Select  @bReturn = Case When (@Field is null) Then '1' Else '0' End

Return @bReturn

End

Auxilary Table:

CREATE TABLE [dbo].[OfficeAux](
[ID] [uniqueidentifier] NOT NULL,
[ECTF_Cust5ULPhysical] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[XName] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[EmpNum] [char](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FirstName] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LastName] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PhoneExt] [nvarchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Phone] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MobilePhone] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[isActive] [bit] NULL,
[lastUpdated] [datetime] NULL,
[lastUpdater] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
End

#15 Workflow to Custom Method calls

1. Use Visual Studio 2003 to create the standalone 1.1 framework DLL

2. Copy the dll generated (located under the /bin directory) to your webserver hosting Deltek:
Program Files\Deltek\Vision\Workflow

3. Once the dll is loaded it is visible to Deltek’s WF. You can then configure your WorkFlow by using the  left navigation:: Configuration | WorkFlow | User Initiated Workflow

4. Select Projects from the Application dropdown to get the Project Info Center WFs

5. WF has a top level and sublevels so you can group things anyway you want – we have a few top categories and many sublevels under each categories separated by  New & Edit  which are the 2 distinctions since these are actually DB triggers (at least that is what I think launches these)  executed on insert, update and delete commands in SQL.  Lots here see normal doc and that pdf from the Insight Class.

6. Select Invoke Custom Method from the choices and you will see the following dialog box which “sees” all the dlls in that Workflow directory.

a. Select the dll which is labeled Assembly

b. Select the class from that dll

c. Select the method call from that class

7. The rest is mapping parameters which are all send via strings (ie. no Boolean objects or primitives here checkboxes just send ‘Y’ or ‘N’)

a. To add a parm you cannot just type in the field name like you’d expect . First slide the vertical line between name and value to the left a little so you can see the ellipsis (…) on the far right of each row in the parm grid. Click on that and you get yet another dialogue box. Select your field and make sure you click the Add Column button then OK. You can type in the expression box directly just be careful. If you do this a lot you can save off the field names in ‘[:name]‘ format in text file & cut/paste.

Lastly check the Invoke after all changes are saved checkbox to ensure all your parms are good.

I have noticed timing issues on parms that are filled in from other workflows so show care there. Also be aware these WF can significantly slow down save performance.

Stored Procedures ::

1. Create a viable SP

2. WF “sees” all the SPs so no other migration is needed

3. Select Stored Procedure & load parms the same way as above.

#12 Abbreviating Memo/Text data with an ellipsis

You can do this on the raw SQL query for a GridView (asp.net) or the data tab on SQL Reporting Services (RDL definition)’

Examples:

Select   CASE WHEN (DATALENGTH(Activity.Notes) > 250) THEN  SUBSTRING( dbo.ScrapeText(Activity.Notes),1,250) + ' ...' ELSE LTRIM( dbo.ScrapeText(Activity.Notes)) END as ActivityNotes
from Activity

  
 The example above incorporates the UDF ScrapeText to clean up the HTML tags (see Post #7 )

You can also do this in the table cell on SQL Reporting:

=iif((Len(Fields!ActivityNotes.Value) > 250), Left(Fields!ActivityNotes.Value, 250) & " ...", Fields!ActivityNotes.Value )

The result of all of this sets a maximum length on a table cell which can provide a more uniform table look when you get unusually large data.  You can also wrap that ellipsis with a view more link (anchor tag) to show more detail (i.e.) <a href=’http://server/viewMore.aspx?id=xxxx’ target=’_blank’> … (more)</a>  To do this you will want to use the SQL Reporting cell edits to install the query string variable (id).

#11 Union with SQL memo (text) field query error

If you create a Stored Procedure or View which does a Union where one part of the union has a text field – referred to having a Deltek data type of “memo” [ Configuration | User Defined Tabs (Fields tab) ]  which is labeled as “text” in SQL.   You may get the following error message: ‘The text data type cannot be selected as DISTINCT because it is not comparable’

If you cast the text (memo) field as ‘nvarchar(max)’ this will correct the error.

Select Cast(oldField as nvarchar(max)) as oldFieldName …

#07 SQL UDF to Clean Up HTML tags

When using SQL Reporting to report on text fields that are formatted with html tags this user defined function UDF (a modified copy of a post by William McEvoy) is helpful. It does not clean up all but does a decent job on most common tags. You can add offending tags and code to the replace special character’s list (ie.   below)

Create FUNCTION  [dbo].[ScrapeText]
(
  @string varchar(8000)
)
returns varchar(8000)

AS
BEGIN
---------------------------------------------------------------------------------------------------
-- Title:        ScrapeText
--
-- Date Created: April 4, 2006
--
-- Author:       William McEvoy
--
-- Description:  This function will attempt to remove rtf formatting from a string. This is
--               accomplished by concetenating all text contained between greater than and less
--               than signs within the formatted text.
--
-- Example:      <P>This text will be parsed and returned but not the P's</P>
---------------------------------------------------------------------------------------------------
-- Date Revised:  5/3/08
-- Author:       D Gorman
-- Reason:      Minor HTML tags & codes added to replace
---------------------------------------------------------------------------------------------------

declare @text  varchar(8000),
        @PenDown char(1),
        @char  char(1),
        @len   int,
        @count int

select  @count = 0,
        @len   = 0,
        @text  = ''

-- Add tokens
select @string = '>' + @string + '<'

-- Replace Special Characters
select @string = replace(@string,'&nbsp;',' ')
select @string = replace(@string,'<P>',char(13))
select @string = replace(@string,'</P>',char(13))
select @string = replace(@string,'&amp;',char(38))
select @string = replace(@string,'&copy;',char(169))

-- Parse out the formatting codes
select @len = len(@string)
while (@count <= @len)
begin
  select @char = substring(@string,@count,1)

  if (@char = '>')
     select @PenDown = 'Y'
  else
  if (@char = '<')
    select @PenDown = 'N'
  else
  if (@PenDown = 'Y')
    select @text = @text + @char

  select @count = @count + 1
end

RETURN @text
END

You would use this ‘upstream’ on the query/view or stored procedure used by the SQL Reporting report dataset definition.  In the report def you’d just reference the NewName field reference.

Select  dbo.ScrapeText(FieldX)  as NewName From ProjectCustomTabFields Where WBS1=’xxxxxx’

#06 SQL View & Employee Search Stored Procedure

If you need to connect to Deltek via some ODBC connection but do not want to show all. I suggest creating a restricted SQL View like this one we use for limited Employee information. If you allow this view read access via SQL authentication and a SQL login you can safely restict the user’s access.   From the SQL management console you can create the view (use xxVW_ as a naming convention to steer around any others there – where xx is the abbreviation of your company or something consistent)

Create VIEW [dbo].[xxVW_FilteredEmployee]
AS
SELECT     Employee, FirstName, MiddleName, LastName, Status, Org, EMail
FROM         dbo.EM

– Grant Access to your SQL login

Grant Select on [xxVW_FilteredEmployee] To DeltekUserR

On a separate idea – If you want a Stored Procedure that allows search parms to be ignored if not present here is an example (more on how this may fit in & be used later)

Create PROCEDURE [dbo].[xxSP_searchEmployee]
(
   @FirstName  nvarchar(255)
 , @LastName   nvarchar(255)
 , @Supervisor  nvarchar(255)
)
AS
BEGIN
 SET NOCOUNT OFF;

 SELECT   Employee, FirstName, MiddleName, LastName, Status, Org, EMail, Supervisor
 FROM     EM
 WHERE   (EM.FirstName LIKE CASE When @FirstName is null THEN EM.FirstName  ELSE  @FirstName + ‘%’  END )
 AND  (EM.LastName  LIKE CASE  When @LastName is null THEN EM.LastName  ELSE  @LastName + ‘%’  END )
 AND  (EM.Supervisor = CASE  When @Supervisor is null THEN EM.Supervisor    ELSE  @Supervisor    END ) 
END

– Grant Access to your SQL login

Grant Execute on [xxSP_searchEmployee] To DeltekUserR

You can test by submitting this query

xxSP_searchEmployee, d,g,null :: which will wildcard first name starting with D and last name with G ignoring the supervisor. 

#02 Simple PopUp

On all Deltek info center pages you have – as an admin – the ability to add a help icon and insert text.  If you put in html code in the HELP TEXT property as one line without any line breaks you can introduce a popup window that feeds data from somewhere else. Where I work we use it do provide a job number generator using asp.net and SQL Server. The user selects some defining criteria related to their job off of some dropdowns and a transactional stored procedure grabs and formats the next available number. From this point there is some simple javascript to copy to the clipboard via a button and then to the project name field in the project info center via a simple Control-V command.

Note: Our Job/Project Number generator was installed prior to going to 5.0 - I understand that some of this functionality is available now ‘out of the box’.

Sample code put in help text Deltek info center screen: (one line no breaks)

<html><head></head><body><iframe src="http://intranet/somepage.aspx" width=500px height=500px scrolling=no /></body></html>

javascript:

<script>
function CopyToClipboard()
{
document.frmVisible.txtJobNumber.focus();
document.frmVisible.txtJobNumber.select();
CopiedTxt = document.selection.createRange();
CopiedTxt.execCommand("Copy");
alert('Number has been copied. Use Control-V to paste into new project screen.');
}
</script>  

js Credit Due: http://www.geekpedia.com/prog_ttrls_print.php?id=126