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.
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.
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]
If you insist to sort the results by the employees’ name, use the employee names as a string expression in the order() function.
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.
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).
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.
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] : "")
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:
- SSIS #109 – Wait for data with For Loop Container
- SSIS #96 – Everybody Needs Integration Services Expression Reference
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