Area Chart - Need to Show Empty Area instead of Zero

It's been awhile since I created my last report. Recently, I had to pick this up and create a burn down area chart. The remaining work data is set to something like "=iif(fields!date <= today(), sum(fields!remainingwork.value), nothing). I was expecting to see a nice cut off line of the burn down chart on the current day, where future days is blank. However, the report zeros the data on the next date and the chart ended up with a sharp line from the remaining work of the current day down to zero on the next day. After some digging around and playing around, I found out that this issue can be fixed by simply setting the EmptyPoint\Color to "No Color". Here are the steps: 1. Select the data series on the chart design section. 2. On the properties panel, expand the EmptyPoint node and set the color to "No Color". Simple as that.

Deploy a Web Service

I tried to deploy a web service that allows me to use TFS queries as data source on some of the reports. The benefit of using TFS queries vs. the data warehouse is that you don't have the delay of data update from the operational data store to the data warehouse. Anyway after getting the code snippet from this post and tweaked it a little bit. I'm ready to deploy it to a IIS server. I ran into an error that complains about the permission of to the global assembly cache. After some digging online the following post helped me fixing the issue, specifically step #5. Thanks Dan! Great job writing up the post! http://blog.hmobius.com/post/2006/06/30/Problems-with-IIS5-dotNet-v11-to-v2-migration-woes-part-5.aspx Thanks to Ewald as well for providing a great solution in SSRS.

Alternating Background Color by Groups

=IIF(RunningValue(Fields!Biz_Story_ID.Value,COUNTDISTINCT,NOTHING) MOD 2 = 0,"PaleGreen","White")

Be Careful When Apply Restrictions on Outer Joins

This query doesn't work as expected.
select

rd.System_Id as BizStoryID ,0 as TechStoryID ,0 as TaskID from


#UpdatedWorkItemRawData rdleft join #ItemHierarchyPrep ihon rd.System_Id = ih.BizStoryIDand rd.System_WorkItemType = 'eScrum Product Backlog Item'and rd.TeamProjectSK = @ProductBacklogProjectNodeSKwhere ih.BizStoryID is null

To get what I need, the following query is needed.
 
select rd.System_Id as BizStoryID ,0 as TechStoryID ,0 as TaskID from

#UpdatedWorkItemRawData rdleft join #ItemHierarchyPrep ihon rd.System_Id = ih.BizStoryIDwhere rd.System_WorkItemType = 'eScrum Product Backlog Item'
and rd.TeamProjectSK = @ProductBacklogProjectNodeSKand ih.BizStoryID is null

How to Pass Multi-Valued Parameter to SSRS Sub Reports

I'm working on a report that contains about 10 subreports. Some of the subreports need multi-valued parameter passed from the main report. After struggling with it for awhile I came across a good post that explains three different ways to do that. All three worked from me.
http://social.msdn.microsoft.com/forums/en-US/sqlreportingservices/thread/ae5ac615-1076-428d-9fcf-6c27e5fee1f0/

Create a Report of the Reports

After some digging, I found this post that talks about how to get report usage information:
http://p2p.wrox.com/content/articles/creating-report-server-usage-report-sql-server-reporting-services

SQL Server 2008 Email Notification Setup

I was trying to set up an email notification for a SQL Server agent job. I set up the Database Mail profile under Management, created operators under SQL server Agent, selected an operator under the notification section of a job. After the job is completed, guess what, there is no email. After diggin around I found this post(http://social.msdn.microsoft.com/Forums/en/sqltools/thread/839b5c27-9bc5-4467-a9d4-e78f9934a761). I tried restarting the agent serice and then realized that the mail profile was not enabled under SQL Server Agent Properties -> Alert System. After enabling that and restarting the agent service, it worked. The key here is to enable the mail profile first, then restart the agent service.