Archive

Posts Tagged ‘filter’

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])

image

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,
      
Except(
            [Product].[Product Categories].[All].Children ,
         
  [Product].[Product Categories].[Components]
        ) ON ROWS
FROM    [Adventure Works]
WHERE   ([Measures].[Order Quantity])

image

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.

image

(The following would not work.)

SELECT    [Date].[Month of Year].Children  ON COLUMNS,
        Except(
          
[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,
        Except(
         
  [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 #40 – Use Table Filter to Conditionally Show Rows

March 30, 2011 Leave a comment

I recently had a need to show three different view in a employee productivity report with the same data set, two summary views and one account level detail view.

For the two summary views, employees are listed regardless they have data or not in the date range. This will allow the report to do “forced ranking. I’ll blog more about “forced ranking” later. Because I had to show all the FTEs in different grouping, I had to create some dummy rows. I’ve written a post, SSRS – Drill down or Drill through ‏ 2? discussing the approach.

Now if I use the same data set to show the data in account level details, there is no need for forced ranking any more. Now I want to hide those dummy rows.

This is a simple task in SSRS. There is no need for a new data set. Create a new table in SSRS, using the same data set, with a filter.

image

The Expression for the table filter is this:

=IIF(IsNothing(Fields!DECISION_DT.Value),"HideRow","ShowRow")

Note: IsNothing is VB’s equivalent to IsNull in T-SQL.

With the above filter, only rows that have a DECISION_DT will be shown in the account level detail view, and all the dummy rows will not.

%d bloggers like this: