Archive for January, 2013

MDX #24 – Leave out a dimension member using Except() Function

January 17, 2013 Leave a comment

Functions such as NonEmpty(), Filter() and Exists() must use a numeric expression

Many of the MDX functions, such as NonEmpty(), Filter() and Exists(), seem to be easy enough to use, but they present quite a challenge for people with T-SQL background. The common theme among them is that they all claim to return dimension members, such as a list (or a set of tuples in MDX lingo) of countries, but they must use a fact measure (a numeric expression in another word) as the condition, such as the sales amount must be more than $10K. We are just not used to using fact measures.

Check out this post for an example of the NonEmpty() function.

MDX #1 – Why should we always provide set_expression2 in NonEmpty() function

Use Except() function to leave out a dimension member

In many situations, we just simply want to leave out a dimension member, such as one of the Product Categories, no matter what the sales amount is.

Run this simple query, and you will get the Order Quantity for all the Product Categories for each month.

SELECT    [Date].[Month of Year].Children  ON COLUMNS,
               [Product].[Product Categories].[All].Children ON ROWS
FROM      [Adventure Works]
WHERE   ([Measures].[Order Quantity])


What if I simply just want to leave out the Components in my query? Components are not the “final” products. Clothing, Bikes and Accessories are. So I have quite a good reason to leave it out of the query.

Now run this query with the help of the Except() function. Components is no longer in the result.

SELECT    [Date].[Month of Year].Children  ON COLUMNS,
            [Product].[Product Categories].[All].Children ,
  [Product].[Product Categories].[Components]
        ) ON ROWS
FROM    [Adventure Works]
WHERE   ([Measures].[Order Quantity])


The two sets must be from the same hierarchy and at the same level

The basic syntax of the Except() function is:

Except(Set_Expression1, Set_Expression2)

It returns SET 1, minus the SET 2. Please make sure that both sets are:

1) from the same hierarchy, and

2) at the same level

If you want to leave out Brakes, the following query will not work, because the two sets in the Except() function is not at the same level.


(The following would not work.)

SELECT    [Date].[Month of Year].Children  ON COLUMNS,
[Product].[Product Categories].[All].Children ,
[Product].[Product Categories].[Subcategory].[Brakes]
        ) ON ROWS
FROM    [Adventure Works]
WHERE   ([Measures].[Order Quantity])

This query should work because both sets are at the Subcategory level.

SELECT    [Date].[Month of Year].Children  ON COLUMNS,
  [Product].[Product Categories].[Subcategory].members ,
[Product].[Product Categories].[Subcategory].[Brakes]
        ) ON ROWS
FROM    [Adventure Works]
WHERE   ([Measures].[Order Quantity])

The Except() function does not involve any numeric expressions

So hopefully, it’s easier for people to understand with T-SQL background.

Categories: MDX Challenges Tags: , , ,

SSRS #73 – Use Calculated Field to Dynamically Set Default for Parameters

January 4, 2013 3 comments

Calculated Field has existed in Reporting Services since version 2005. SSRS 2005 did not have the most attractive user interface design, so this pretty useful feature stayed hidden from me until version 2008.

Even in SSRS 2008, I did not pay much attention to it until I started to write reports in MDX queries on SSAS cubes.


Once I started using them, I find myself like it more and more.

One of the challenges in SSRS reports is to set parameter defaults dynamically. It’s an even bigger challenge if you want to dynamically set parameter defaults to multiple values, such as (Select All). Don’t read this statement wrong. Setting parameter defaults to multiple values, such as (Select All), is not hard. What is hard is to do it dynamically, meaning the situation where you want to set the default or not depends on the selection of another parameter.

I recently ran into this issue when working on a Data-Driven Subscription project. The idea is that we’d use just one SSRS report for both online ad-hoc reporting, and for a daily data extraction that can be sent to users via E-Mail.

For the online ad-hoc reporting, we certainly do not want to default all parameters to (Select All). For the daily data extraction though, that’s exactly what we’d like to do.

Since I am using Data-Driven Subscription and a SSIS package to trigger the subscription, you would think that I can manipulate the parameter defaults in either SQL code and/or in SSIS scripting. But I highly recommend against that if your goals are

1) To use just one SSRS report for both online ad-hoc reporting, and for a daily data extraction

2) Not to replicate all the queries you already have in the SSRS report

3) Not to waste your valuable time to wrestle with Reporting Services. When it comes to parameters with defaults or cascading parameters, Reporting Services is not very forgiving in terms of dynamic settings. If you have tried that before, you probably know what I meant.

Using Calculated Field feature in SSRS 2005 and above is what I’d recommend.

Suppose you have a hidden parameter, Subscription.


Parameter Subscription has two values, 1 and 0.


Its default is 0 (not the 1 shown).


When Subscription is 1, we want to set the default of parameter Geography to (Select All).

We’d create a Calculated Field for DataSet_Geography. Call it GeographyKey_Dynamic.


In the Expression Editor for GeographyKey_Dynamic, enter an IIF() expression. This expression will basically use the GeographyKey value if the Subscription is 1, otherwise, it will be Nothing.


Now we are ready to use this new Calculated Field GeographyKey_Dynamic as the default value for parameter Geography.


DataSet_Geography is the same dataset for Available Values. The only difference is that in Available Values, we’d always use GeographyKey, which is a SQL column. For the Default Values, we’d use GeographyKey_Dynamic instead.



1) Watch out when you like the Calculated Fields too much and use them excessively. SSRS reports will typically go through Retrieval, Processing and Rendering phases during run time. If you have queried a Report Server database, you will find that the Processing and Rendering phases take more time than you’d like. Having too many Calculated Fields will no doubt increase the Processing time. So think about distributing the burden across queries on servers and calculations in memories.

2) For data-driven subscription, see my blog at

SSRS #60 – Steps to implement a data-driven subscription

%d bloggers like this: