Archive

Posts Tagged ‘IIF()’

MDX #41–Remove employees with less than $100,000 sales

May 30, 2014 Leave a comment

Table of contents

Need to remove employees who have zero sales amount
Create a calculated measure with the IIF() function
The NON EMPTY keyword will take care of the removal
Ordering results with both numeric and string expression
IIF() statement VS. Scope() statement in MDX Cookbook 2012

Need to remove employees who have zero sales amount

I recently helped a co-worker with a simple MDX query. All she wanted to do is to remove employees from the return set who has no sales or who has zero $ sales. She was using the NON EMPTY keyword on both the X and Y axis. That removed employees who have no sales, but the employees who have zero sales amount are still showing in the data set.

A quick and simple way to remove those employees with zero sales amount is to create a calculated measure in the query. This new calculated measure will simply use the IIF function to turn the sales amount into a NULL value, when the sales amount is zero. The NON EMPTY keyword on the Y axis will automatically take care of the removal of those employees with zero sales amount.

Create a calculated measure with the IIF() function

Suppose that this is our initial MDX query, where all employees (at leaf level of the Employees hierarchy) who have sales are returned.

 

image

Now you would like to only see employees who have more than $100,000 sales. In the following modified MDX query, I added this calculated measure. 

[Measures].[Reseller Sales Amount > 100,000]

This measure is simply identical to [Measures].[Reseller Sales Amount], except that it is set by the IIF function to be NULL, when it is less than 100,000.

The NON EMPTY keyword will take care of the removal

The NON EMPTY keyword on the Y axis will automatically take care of the removal of those employees with sales less than 100,000.

 

image

Ordering results with both numeric and string expression

To be sure that we get only the employee who have more than 100,00 sales amount, we can sort the results by the sales amount using the order() function with this numeric expression.

[Measures].[Reseller Sales Amount > 100,000]

 

image

 

If you insist to sort the results by the employees’ name, use the employee names as a string expression in the order() function.

[Measures].[Employee name]

 

image

IIF() statement VS. Scope() statement in MDX Cookbook 2012

In the book MDX Cookbook 2012, we have quite extensive examples on how to use the query-based IIF() statement, as well as the the cube-based Scope() statement.

You can check out the recipe Detecting a particular member of a hierarchy while you are using the book.

Categories: MDX Challenges Tags: , ,

SSIS #111 – Looking for Conditional IIF() in SSIS Expression?

July 31, 2012 Leave a comment

There is no IIF statement in SSIS scripting functions

If you are looking for IIF() for SSIS expressions, you’ll be disappointed.

Most of us are familiar with the IIF statement in SSRS expressions (or in many other scripting languages).

image

But in SSIS, any Program Flow functions are unmistakably missing. Here are all the function categories you can see in the Expression Builder in SSIS. Program Flow is not one of them.

image

We can achieve IIF() using ( Condition? Value_when_true  : Value_when_false)

There is a very good explanation for the missing IIF statement in SSIS. Before I go too far on this topic, I want to give you the good news first. The Integration Services did give us a tool to accomplish the same function as the IIF statement does, only in different disguise.

Here is what we can use:

( Condition? Value_when_true  : Value_when_false)

You can write any acceptable expression in the Condition part, but it’ll only make sense for the expression to include at least one variable in order to achieve the goal of dynamic as you set out to achieve with IIF in the first place.

The value_when_true and value_when_false part can obviously include variables too.

Here is an example. For a user variable varSourceServerPrefix, I want to set it to an alia name of the linked server in our development environment, but set it to blank in the production server.

( @[User::varProduction] == 0? @[User::varLinkedServer]  : "")

image

Processing flows are largely controlled by the Precedence Constraints

There is an even better reason why Integration Services are missing the entire category of Program Flow Functions.

In Integration Services, processing flows are largely controlled by the Precedence Constraints between tasks. These Precedence Constraints are almost completely controlled by conditions that are expressed using variables, while variables in Integration Services can be manipulated by Script Tasks.

For examples how variables can be manipulated by Script Tasks and example of Precedence Constraints:

Note: the parentheses are not really required. I don’t think the parentheses are required even in nested conditional expressions. It certainly doesn’t hurt to have the parentheses in nested conditional expressions for better readability.

Here is the MSDN link on this: http://msdn.microsoft.com/en-us/library/ms141680.aspx

%d bloggers like this: