#17 Thinking of converting to VS 2008 ?

### Update ###

The SQL 2008 Business Intelligence Development Studio (VS subset) that ships with SQL Server 2008 imports the 2005 VS  rdl files without any problems. I have not tested this extensively but all looks same-same.

D Gorman 1/14/09

####

(see update above) Be aware that your VS 2005 Sql Reporting Projects don’t convert.  I just ran a conversion (after backing up the code and solution files) and it just drops the SRS (.rptproj) project. I tried opening a .rdl file and it does not handle it correctly.  I will have to look at what is next in regards to this but for now you have to keep VS2005 around for SRS reporting and/or figure out VS 2008 new process for handling BI (BIDS).  Post a comment if you know a better way.

After searching around I found these posts:

•http://geekswithblogs.net/juang/archive/2007/12/12/visual-studio-2008-will-not-support-sql-server-2005-reporting.aspx

• http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=3336504&SiteID=1

Apart from this the other features in VS2008 are very nice & the ability to switch frameworks (sans the usual 1.x)  works flawlessly. I moved a web project from 2.0  to 3.5 and down to 3.0 without a problem.

#16 Flipping a SQL report from/to Prod. & Test systems

To flip an existing report definition (stored in rds/rdl files) via Visual Studio:

1. Create an alternate (shared) data source (.rds) for the other system.

2. Take the existing report and copy it off  and rename it (control-C & control-V/ rename should do that)

3. Open this new report definition (step #2) & create a new dataset (part of the .rdl) using this new data source (step #1)

4. Copy the old query or stored procedure reference into this new dataset.

5. Clean up any direct dataset references in the cell expressions.

#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.

#14 Code Posts – WordPress.com and VB.net

If you need VB.net code from the C# you can use Telerik’s code converter at http://converter.telerik.com

Update: WordPress does allow a nice format for presenting code :

http://support.wordpress.com/code/#posting-source-code

Wrap your code in these tags:

[sourcecode language='css']

your code here

[/sourcecode]

Notes:

1. The quotes are messed via posting on WordPress.com which does not allow nice plug-ins to help display code better.
2. I will see about converting better first but be aware the of the double quotes converting to odd characters.
3. You can copy C# code into a nice text editor like Textpad or Notepad++ and do a change all & then slap it into the code converter to get VB.net code.
4. If anyone has a better idea please post a comment.

#13 A Look At Integrating Employee Training History

This grid (Telerik RadGrid) summarizes for department manager their employee’s CEUs. We house all the data as a custom tab grid within the Employee Info Center.  Note some image features are intentially blocked out for privacy.

#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 …

#10 List of Some Things We Have Integrated

• Employee Directory - custom filter, Visual Directory fed from EM and Employee Custom Fields tables

• Training Request System - fed from VE Vendor table, merged with custom data and one-click auto loaded back into Deltek EM Custom grid table to store training history. Heavy use of Telerik radgrid to group PDH/CEU credits for date ranges. Other features include nag emails, SRS reports and a review process on sponsor and event.

• Training History Bulk Loaded - Define the event (title,dates, hours) - select participants and  bulk load the training history for these employees.

• Client Files Tab Bulk Loaded - define the file name, UNC path (not lettered drive) & bulk load the clients file tab of all clients with a link. We use this to link back to an asp.net page so this ‘loader’ (as an option) changes the ending parm on the link to the internal Client CL table ID.

• Client Profiles - Over 100 fields are stored on client to keep track of their preferences. This is loosely integrated via the client Files Tab link mentioned above.

• Birthday List- simple asp.net user control display Employee name, birth Month & Day (not Year ) on our intranet with links to their picture. Easily moved to Sharepoint via SmartPart

• SRS Phone List- 2 multi-column reports displaying employees ext, direct, mobile and office rendered automatically as a PDF via SQL Report Services link using EM and custom tables.

• File Folders- simple method call to create file folders for projects on our servers.

• Outlook Folders- Not entirely integrated but sends support staff an email with a link to an asp.net page which creates Outlook Folders with the proper permissions. The WF that sends and email could be replaced with a call to this underlying code but we have not done it yet since that page was written in the asp.net 2.0 framework.

I have created numerous stored procedures, views, UDFs and some custom methods to make Work Flow work better for us. To be honest you can do lots out of the box.  In some cases we used some of these more hands on methods because we had not upgraded to 5.0 at that point (i.e. Project Number Generator SP and asp.net page (see post #02 Simple Popup) which we installed while on 4.0)

In some scenarios we could have used a very complex stored procedure but chose to use a series of SQL Views to simplify and layer in a solution that would allow us to view intermediate results. The end product of all these calculations were pushed(via a Stored Procedure) into a custom field on the ProjectCustomTabFields table.

I will add more to this list as I get time. If you have questions and/or would like more details please post a comment or contact me directly.

#09 Simple Alternate Background Row Color

Overwrite the row background color property with this expression.

=iif(RowNumber(Nothing) Mod 2, “WhiteSmoke”, “White”)

For old timers – this creates the Green Bar shading effect on a SQL Reporting  Services Report when applied to a detail row on a table.

I have not tried it but you can probably use the hex color values like #990000 instead of the key words (ie Maroon)

Credit Due:  Professional SQL Server 2005 Reporting Services by Turley, Bryant …