RyanDev.com

I have already posted the SQL code you can use to get a list of all open work items across all projects within Team Foundation Server 2008 (TFS).  Now I will walk you through one way to create a SQL Reporting Services Report using Microsoft SQL Server 2005’s SQL Server Business Intelligence Development Studio.  Then we will see how to schedule it.  Note: We will not cover how to configure SRS.  This is assuming that SRS is already setup with email settings.

This sample was created directly on the Microsoft Team Foundation Server.

1. Open SQL Server Business Intelligence Development Studio

2. Click Create Project.  There are several ways to create the report and in this example choose the template named Report Server Project Wizard.  Provide a name of TFSItems and save the project anywhere.

3. The Welcome to the Report Wizard screen is displayed.  Click Next.

4. Create a new data source and name it dsTFSItems.  Click Next.

5. Click Edit and provide connection information to your SQL server.  Set the database to TfsWorkItemTracking.  Click OK.

6. The data source connection string is now filled in. Click Next.

7. Copy and paste the SQL statement from my earlier post.  Note: you will probably have to correct the single quote characters because they will probably not paste accurately and you will get syntax errors when clicking on Next.  Click Next.

8. Select the Tabular report type and click Next.

9.  Move all of the fields into the Details section.  Click Next.

10. Choose a table style.  In this case, I chose Slate.  Click Next.

11. Make sure the report server field is correct.  Leave the deployment folder with the default value.  Click Next.

12. The final screen of the wizard is displayed showing a summary of what the wizard will do.  Click Finish.

13. You now have a TFS report in Visual Studio.

14. We want to be able to select a specific person and see just the tasks that are assigned to them.  To do that we need to have a second dataset.  Click on the Data tab and then under Dataset choose <New Dataset…>

15. Name the dataset dsAssignedTo and paste the following SQL into the Query String field.  Note: You may have to adjust the single quotes after pasting.

SELECT Person, Email FROM TFSWarehouse.dbo.Person WHERE Email IS NOT NULL AND EMAIL <> ” AND EMAIL LIKE ‘%@%’ ORDER BY Person

16. Now we need to add a report parameter.  To do so, click on Report, Report Parameters in the menu.

17. Create a report parameter named AssignedTo and select from the dsAssignedTodataset as shown below.

18. Now we need to use this report parameter in our main dataset.  Select the dsTFSItemsdataset and add one more WHERE clause: “AND w1.[Assigned To] IN (@AssignedTo)”

19. The report is done.  We now need to deploy it to the SRS server.  To do so, right click on the project name in the Solution Explorer and choose Deploy. 

20. Likely near the bottom of your screen will be the Output window and it should indicate the deployment was successful.

21. Now, open Internet Explorer and browse to the report server at http://localhost/Reports.  You will notice a folder named TFSItems which was created when we did the deploy. 

22. Open the TFSItems report folder and you will see the report we created, TFSDailyItems.

23. Click on the report and you will see that we have a drop down list of various people in TFS. 

24. Select a name and then click on View Report and you will see their open items.

25. To schedule this report, click on the New Subscription button.  Fill out who you want the report sent to and how often.  Then select their name from the AssignedTo report parameter.

26. To see which subscriptions have been setup, click on the Subscriptions tab of the report.

That’s it.  Now every day at the time you specified an email will be sent with their open TFS items from all projects.

I have been playing around with a way to have Microsoft SQL Server Reporting Services (SRS) send a list of open items from Microsoft Team Foundation Server 2008 (TFS) to anyone who has open assigned TFS tasks within all projects.  The database structure is not entirely clear nor does it appear that there are stored procedures to retrieve this information.

While doing SQL traces I discovered that TFS uses in-line SQL statements and builds dynamic SQL statements instead of executing stored procedures.  As such, I have tested the SQL statement below and it appears to work correctly.  One thing I am struggling with is any project created using the eScrum template.  It does not store Areas and Iterations of Sprint Backlog Items the same way.  If you have figured it out, please post a comment, and I’ll take a look at it some more later.

Often when writing XML files to disk as text files developers do not include any formatting.  As such, if you need to open the file and examine it in Notepad, for example, and make changes it may be very hard to read.  There is an easy way to fix this.  You can download the executable file hereor just use the following code in Visual Studio.  NOTE: This is a console application expecting a file name as a command line argument.  You can drag your text file onto the executable file and a new file will be created with the same name as the file name with “-formatted.xml” appended.

As a follow-up to a previous post I wrote, Top 10 Reason To Work For A Small Company , I will make it clear that there are also disadvantages to working for a small company.  These disadvantages are not limited to or unique to small companies.  Small is a relative term and can be thought of as 50 or fewer employees. 

10. Less stability (usually) - It used to be that when you finished college and got a job with a large company you would work there for the rest of your life and then retire with a pension plan.  It was nearly a guarantee.  Those days are long gone; however, large companies still tend to have better stability than small companies.  When you work for a small company your risk of losing a job is greater.

9. On your own - At a small company there is no one to hold your hand while you get up to speed and learn what is necessary to do your job.  Some people thrive in this environment but most workers tend to do better when they are told what to do, how to do it, and when to do it.  At a small company there is no room for hand-holding.

8. Smaller clients - Small companies tend to have small clients.  As such you will not gain experience in highly critical environments.  For example, suppose you are an IT company who builds and designs databases.  Small clients will typically have lower transactions and less complex databases.  There is a substantial difference in a database that serves as the back-end for a website that processes 1000 transactions a day and the databases that process search results for Google.  Working for a larger company exposes you to more complex situations.

7. Too much diversity - When working for a small company you are involved in many more areas and functions of the company than what may be in your job description.  Although this wide array of experience is very valuable it does prohibit you from becoming very great at one particular aspect or function.  You have to be good at many things.  You do not have the time to focus and specialize.

6. One bad apple can ruin everyone - Everyone in a small company has an enormous amount of pressure to make the company successful.  One employee who says one wrong thing to one important client can have such a negative impact on the company.  It is too easy for one employee to ruin the company.  As such, it is imperative that you hire appropriately.

5. No hierarchy - To some people this IS a reason to work for a small company; however, because there is very little hierarchy there is very little room for advancement in job title.  If you want to become a manager you have to wait for the current manager to leave, or, if you are lucky enough the company will grow and create new management positions, but at that point, it would not be a small company anymore.

4. All of your eggs are in one basket - Small companies are thrilled to have 1 large client and a large project.  But what happens to the company if that project goes sour or if the client does not pay as expected?  One bad client can have a devastating impact on a small company.

3. No vacation or time-off - For some small companies “no vacation” is an exaggeration but very often there is only one person who knows about a specific process or a specific project and when they go on vacation they must still be checking email and even working because there is no one else who can do their job.

2. Less benefits - Small companies are not able to offer the same health benefits or investment matches that a large company can.  Many small companies offer a 401k investment deduction from your paycheck but do not match any percentage of it.  This can add up significantly.  For example, suppose you earn $50,000 in yearly salary and you contribute 5% to your 401k.  That will result in $2,500 invested per year.  If you worked for a large company they may match the first 3% of your contribution at 100%.  In this example your company would also contribute $1,500 per year to your 401k.  Factor in compound interest over the life of a career and that can total over $400,000 assuming 12% interest in mutual funds over 30 years.  That equates to over $14,000 a year that a large company would pay towards retirement. 

1. Lower salary - Small companies offer their products and services at a discounted rate compared to other larger companies to remain competitive and although a smaller company will have much less overhead costs it still results in lower salaries.  Salaries will generally be on the low end of the range for what the market is paying for a particular position.  This is not to say that the salary is well below market but in certain cases it is significant.

These issues are not unique to small companies and some even apply to large companies.   My preference is to work for a company that is somewhere in-between too small and too big.  What is that number?  No one knows but companies big and small can learn from these points and take the best of them and leave the rest. 

My hope is that during these tough economic times the information I have provided in this post and in Top 10 Reason To Work For A Small Company may help someone decide what they will do with their career. 

When you have a SQL Reporting Services report developer who is developing reports on a different Microsoft SRS server than where you are deploying the reports it can be very common to get this error: “The report server cannot process the report.  The data source connection information has been deleted.  (rsInvalidDataSourceReference)”

A developer’s first instinct may be to open the report in Visual Studio and make sure the data source is set correct and even possibly deploy the data source and the report again through Visual Studio to the SRS server.  Unfortunately, this often does not fix the issue.

There is; however, an easy fix to this.  There is a GUID stored in the rdl file for the data source. Since the developer is working on a different install than the production server the GUIDs are different.  There are ways to overcome this, such as create the production server and then use a backup of it to develop on, but I will not get into those details now. 

You need to use the SRS web interface to resolve the GUID issue.  Refer to the following screenshots.

1. Go to the Properties tab and you will quickly notice the problem in red.

2. Click on Browse and expand the tree under Data Sources and select the correct data source and click OK.

3. Now, a very important and easily forgotten step, click Apply.

That should be it.  It has updated the report to point to the correct data source with the correct GUID.  Click on the View tab to see your report.

I have worked for a huge company, a tiny company, and some in-between and working for a small company has advantages and disadvantages.  We’ll explore 10 advantages for working at a small company.  Small is a relative term and in this case it can be thought of as 50 or fewer employees. 

10. Not just stuck in a cubicle - At a small company you simply cannot show up for work, sit in your cubicle, focus on just your tasks and be very successful.  It requires collaboration and communication with other teams within the company.  Some people are scared of this interaction and would rather camp in their cubicles until the clock strikes 5:00 PM.  If so, do not go to work for a small company.

9. More exposure to various area of the company - Even if you do not have direct input or collaboration to each area of a company you will have the opportunity to learn things from each area including HR, Sales, Marketing, Support, etc.  Often, you will even be able to contribute to some extent to each area of the company.

8. Common goals among team members - In a small company everyone is dedicated to making the company successful.  You are not likely to meet someone who is just trying to skate through to retirement.  Each employee cares about the success of the company and is willing to work hard to keep it successful.

7. Not as much “red tape” (Agile) - When I worked for a large company (125,000+ employees) it never ceased to amaze me how slow we were to respond to requests that were out of the ordinary.  We often lost business as paperwork would get stalled with the Legal department.  A small company is much faster to respond and to adapt to a customer’s need or to the marketplace demands.  A small company has to remain agile to be competitive.

6. Greater trust and freedom - The owners of a small company have to be more careful to hire trustworthy employees and as such managers within small companies tend to take a “hands-off” approach to management as they trust that they have hired a competent employee.  The most important characteristic to look for when hiring someone is character.  Skills can be taught but character is something that cannot be taught by managers.  Small companies need to trust their employees or else they will spend too much time micromanaging their employees.  This gives each employee the ability to make decisions.

5. Ability to influence policy- Many small companies do not have policies laid in stone due to the fact that they must be agile and ever adapting to the market place and as such do not have many policies worked out.  Some small companies do not even have formal PTO policies but rather everyone works as much as they can and when things slow down they may take some time off.  At a small company, you have the privilege of helping to establish policies.  If your small company begins to grow you will need policies in place before you get too big or you will have management nightmares trying to make sure everything and everyone is able to work together using the proper tools and procedures.

4. Greater opportunity to expand skills - When working for a small company you often have to contribute in a variety or roles and areas of the company.  For example, you may be involved with sales in some fashion even if you are a Software Developer.  That may mean that you assist in pre-sales support by facilitating demos of your software to potential clients.  You may have the opportunity to develop your marketing skills somewhat as often times a small company will not have a formal marketing department.  You have the ability to bring your talents to the company and also develop many other abilities and talents.

3. More responsibility for success of company- When I worked for a very large company it was frustrating to realize that no matter how great I did at my job the company was still the same.  On the contrary, I could have done a horrible job and still not had a significant impact on the success of the company.  At a small company a tremendous amount of responsibility rests on each employee for the success or failure of the entire company.  Some people shy away from this pressure and if so, small companies are not for them.  Large companies provide safe havens for people who like to push buttons and not be responsible for anything critical.

2. You matter - Not only do you matter in the sense that you have a tremendous amount of pressure on you for the future of the company but you also matter as an individual.  You are not just an employee ID; you are a person with a name and a face and a family.  The small company culture resembles family.

1. No golden parachutes nor million dollar bonuses from federal bailout money - No need to worry that your fearless leader is going to be given millions of dollars for failing at their job. 

Any company of any size will benefit by implementing these approaches to their environment.  The most important principle is to treat employees as individuals, no matter the size of the company.

For a different point of view, see Top 10 Reasons Not To Work For A Small Company.

There is a problem with the audit entity from my original post that is causing an error when using the plug-in.  Please download the updated copy here.

Also, I have created a more packaged version of this which is available from http://blogs.engage2day.com/?p=90.