Challenge in Excluding Weekends in TFS Reports

I have a burn up report that runs fine in SSRS 2005. This report excludes weekends. The MDX code block that does that is:

{FILTER([Date].[Date].[Date].ALLMEMBERS,
[Date].[Year Week Date].CURRENTMEMBER.Properties( "Day of Week" )<> "0"
AND [Date].[Year Week Date].CURRENTMEMBER.Properties( "Day of Week" ) <> "6")
}

However when I was trying to make this report work on TFS 2010, which is running SSRS 2008, it didn't work. The MDX code block was:

{FILTER([Date].[Date].[Date].ALLMEMBERS,
[Date].[Year - Week - Date Hierarchy].CURRENTMEMBER.Properties( "Day of Week" )<> "0"
AND [Date].[Year - Week - Date Hierarchy].CURRENTMEMBER.Properties( "Day of Week" ) <> "6")
}

I got an error that says "Query (13, 9) The Day of Week dimension attribute was not found." After Googling around with no finding, I opened up the TFS 2010 SSAS database. Guess what, the Date demension doesn't have the "Day of Week" attribute. In TFS 2008 SSAS 2005 the Date dimension has the following attributes:
Date
Day of Month
Day of Week
Day of Year
Month
Month of Year
Week
Week of Year
Year

However, in TFS 2010 SSAS 2008 the Date dimension only has the following attributes:
Date
DateSK
Month
Week
Year

So we have to find out another to exclude the weekends. I used the WTD() function and came up with the following MDX code, which works:
{FILTER([Date].[Date].[Date].ALLMEMBERS,
count(WTD([Date].[Year - Week - Date Hierarchy].CURRENTMEMBER)) <> 1
AND count(WTD([Date].[Year - Week - Date Hierarchy].CURRENTMEMBER)) <> 7
)
}

Who Locked My Table?

I have a couple of big stored procs that process data to a data warehouse. Sometimes they lock up the destination table and makes the reports hang. So I need to find out who locked the table and kill the process. Our DBA is kind enough to provide a query to find that informatoin:

select CASE WHEN tl.resource_type = 'OBJECT' THEN object_name(tl.resource_associated_entity_id)
WHEN tl.resource_associated_entity_id = 0 THEN 'n/a'
ELSE object_name(p.object_id) END as 'entity_name'
, tl.*
from sys.dm_tran_locks as tl
LEFT JOIN sys.partitions as p
on p.partition_id = tl.resource_associated_entity_id
where resource_type <> 'DATABASE'

I love DBAs.

T-SQL Error Handling

http://www.sommarskog.se/error-handling-II.htmlc

Merged colums in Excel exporting

I ran into a situation today that when I export a report to Excel there are unwanted merged columnns. After some digging I came across this post. It was very helpful.

http://blogs.msdn.com/chrisbal/archive/2006/07/08/659545.aspx

Compare Strings in MDX

"using the MDX filter () function and the vba text functions you have plenty of search possibilities

filter (DimName.children, instr(DimName.currentmember.Properties("Name"),"MyValue")>0"

The full post is at:
http://www.developmentnow.com/g/112_2004_3_0_0_407650/MDX-equivalent-to-SQL-LIKE-operator.htm

Warehouse and Cube Changes in TFS 2010

The over view:
http://blogs.msdn.com/sunder/archive/2009/05/16/team-foundation-server-2010-relational-warehouse-and-cube-schema-changes.aspx

How the existing reports are affected:
http://blogs.msdn.com/aaronbjork/archive/2009/05/18/team-foundation-server-2010-where-are-my-reports.aspx

Reports update from 2008 to 2010:
http://www.socha.com/blogs/john/2009/05/upgrading-visual-studio-team-foundation.html

Pass Parameters via URL

I had to do some research to find out why my URLs did work. In short, the URL should point to http://localhost/ReportServer/ instead of http://localhost/Reports/, which is the Report Manager. Here is the post that helped:
http://dobrzanski.net/2008/08/11/reporting-services-problem-with-passing-parameters-directly-in-the-url/

Here is another good post about passing multivalued parameters:
http://bobp1339.blogspot.com/2007/10/passing-ssrs-report-parameters-in-url.html