MDX Questions

I will take a MDX training in mid November. Here is the list of questions I'd like to bring up during the training. I will be posting answers along the way.

1. The Report Designer auto generated MDX queries have additional 'DIMENSION PROPERTIES' and 'CELL PROPERTIES'. Why are they in the MDX query and how are they used in the report? i.e. (See text in blue)
SELECT NON EMPTY { [Measures].[Build Details Count] } ON COLUMNS, NON EMPTY { ([Date].[Date].[Date].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [Build] CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

2. I got an error message saying "The Team Project hierarchy already appears in the Axis2 axis." Does it mean that the Where clause only takes demensions that are not in the Select clause?

SELECT {[Measures].[Work], [Measures].[BizStoryValue]} ON COLUMNS,
{[Work Item].[System_Id].[System_Id]} ON ROWS,
{[Team Project].[Team Project].[Team Project]} ON 2
FROM [Current Work Item]
WHERE ([Team Project].[Team Project].&[11])

3. In our TFS process template, there are two fields in Sprint Details work item that has reportable property set to "detail". How to get their value in MDX?
-I found the answer for this question before the training. "Use reportable="detail" to add a column to the fact table without adding anything to the cube."

4. Which is better, select from (Select ....) automated MDX or filter?

Report Manager Printing Issue

The issue: Cannot Print SSRS 2005 report after installing KB956391

The fix:
1. Uninstall KB956391 on the client PCs.
Or
2. Install SQL Server 2005 x64 Edition Service Pack 2 (KB954606) on the data server (The one that has SSRS service running). More details on http://www.microsoft.com/technet/security/Bulletin/MS08-052.mspx

Thanks to the following discussion:
http://forums.microsoft.com/TechNet/ShowPost.aspx?PageIndex=1&SiteID=17&PageID=1&PostID=4006172

Interactive Sorting When Table Has Groups

To sort correctly on a table that has multiple groups, the correct scope needs to be specified. The default selection for the scope is 'Detail Scope'. Often times, this needs to be changed to a different grouping.

SUM() Function Trick

When using Iif() in SUM() function, make sure data types match. In the example below, if cDec() is not used, you will get an error.

=sum(iif(Fields!Sort_Order.Value = "E1", cDec(Fields!hours_m2.Value), cDec(0)))

The orginal post is at http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1507530&SiteID=1

SQL: Conditional WHERE clause

I encountered a situation that the WHERE clause in a SQL query changes based a parameter. When the parameter is '_All', there is no restriction. When the parameter is any other value, an AND section in the WHERE clause is needed.

After some digging, I found an elegant solution. Using
(@parameter = '_All' OR Field_X=@parameter). It greatly simplied the SQL query and fixed the problem of NULL value is hard to handle.

The post I saw is: http://dotnet.org.za/ilo/archive/2005/05/31/21280.aspx

Jump to URL Opens a New Window

Use Java script to open a new window on a link of a report. If the dynamic value is part of the URL, be aware of the data type. Use CStr() function if the dynamnic value is not String data type.

This method only works when the report is displayed within a browser (I only tested IE7).

Here is an example:

"javascript:void(window.open('http://StaticProtionOfTheURL" & CStr(DynamicValue) & "','_blank', 'location=no,toolbar=no,left=100,top=100,height=600,width=800'))"

Useful Links

Here are the links that I found useful:

MSDN Forum:
http://forums.microsoft.com/msdn/default.aspx?SiteID=1

MDX:
MDX Function Reference (MDX)
http://msdn.microsoft.com/en-us/library/ms145970.aspx
MDX Cheatsheet
http://gumper.com/mdx.html
Format_String
http://msdn.microsoft.com/en-us/library/ms146084.aspx

Expressions:
Expression Functions (SQL Server 2008)
http://msdn.microsoft.com/en-us/library/cc281391.aspx
VB Script
http://msdn.microsoft.com/en-us/library/3ca8tfek(VS.85).aspx
VB Functions:
http://msdn.microsoft.com/en-us/library/32s6akha.aspx

T-SQL Referrence
http://msdn.microsoft.com/en-us/library/bb510741.aspx

SQL Server Analysis Services 2005/2008 Articles
http://www.ssas-info.com/analysis-services-articles

TFS Data Warehouse
http://msdn.microsoft.com/en-us/library/ms244674.aspx#DataStops

Use JOIN() Function to Display All Members of A Multivalue Parameter

Sometimes you want the report to display all the pamameters that are applied, especially when the parameter section is hided. To do that, you can simply use a text box and and point the value to the parameters. However, to display all members of a multiple value parameter, you will need to use the JOIN() function.

For example, if you have a parameter called Area that filters work items by the Area field, the following expression will allow you to display all the selected areas on the report.
="Area: " + JOIN(Parameters!Area.Label, ", ")

Thanks to a post I found on bokebb.com. Here is the link to it:
http://www.bokebb.com/dev/english/2008/posts/2008112686.shtml

About This Blog

I will be blogging the tips and lessons I learned about SSRS, mostly on TFS related reports, SQL and MDX queris.