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.
Table of contents
|IsNumeric() funciton is too forgiven|
|Use Pattern Matching with LIKE keyword|
|LIKE keyword in T-SQL doesn’t support regular expression|
|Replace white spaces with empty characters|
IsNumeric() funciton is too forgiven
One of my SSIS packages failed recently with the following error message.
“…. failed with the following error: "Error converting data type varchar to bigint….”
It turned out that a field contained some white spaces (such as tab, line feed and carriage return control characters), and the cast (col as bigint) has failed.
I can guarantee that all ETL developers have used the IsNumeric() function to check if any strings such as ‘123456’ are true numbers or not. The function seems to be very convenient and easy to use. However, more often than not, you will get errors in your ETL packages when loading these fields into your data warehouse where these fields are expected to be Integer (or bigint or any other numeric ) data type.
You are sure that you have used the IsNumeric() function to exclude those that are not numeric. So what went wrong?
It turned out that the IsNumeric() function is a very forgiven function, while the Cast() (or convert() or the implicit conversion) function is not.
Use Pattern Matching with LIKE keyword
Check out this MSDN article, Pattern Matching in Search Conditions. There are 4 wildcards that we can use with the LIKE keyword.
Using the last one [^], together with the first one %, the following pattern would only allow numbers 0-9 and a decimal point in the string.
I ran the following test to compare the IsNumeric() function with a pattern matching using the LIKE keyword.
The last one with two decimal points has passed the pattern test. Feel free to experiment to see if you can use pattern matching to eliminate it.
LIKE keyword in T-SQL doesn’t support regular expression
The LIKE (or the CHARINDEX) keyword in T-SQL doesn’t support regular expression, but the pattern matching can be a good alternative when the IsNumeric() function is just not enough.
Replace white spaces with empty characters
You can also try to combine the pattern matching with replacing white spaces with empty characters. Tab, line feed and carriage return control characters have the values CHAR(9), CHAR(10) and CHAR(13) respectively.
cast(REPLACE(REPLACE(REPLACE(your_number_column,CHAR(9),”), CHAR(10),”),CHAR(13),”) as bigint)
Some SSRS reports have a large number of parameters
I recently had a chance to help a co-worker to modify an existing SSRS+Cube report. The first thing that caught my eye is the large number of parameters in the report.
Although I never really developed any reports that have more than, let’s say, 6 parameters, I can understand why developers sometimes need to use, let’s say more than 20 parameters. This happened most likely because, the report developer is lack of authority to re-design the cube, or because the report has unnecessarily complex design. Another possibility is that the developer had made conscious decision to avoid writing MDX queries and shifting the coding to the Reporting Services.
A bug in SSRS 2008
Here is a scenario where developers sometimes might need to resolve to using a hidden parameter, that sits in the middle of two parameters. In the diagram below, the middle parameter will take the user input from parameter 1, and have some IIF statement to transform the input, and then the dynamically set value will be the input for the dataset 3 which feeds parameters 3.
I’ve tested this type of "cascading" parameters (with a middle parameter that has a dynamically set default value). The short conclusion is this: in SSRS 2008, the parameter 3 failed to reflect user’s section in parameters 1; in SSRS 2012, all is good.
So I had to say that this is a bug in SSRS 2008.
Remove the middle parameter to work around the bug
To work around the bug, fortunately, we have a pretty simple solution. Simply remove the middle parameters, AND then code the IIF logic in the query parameter for dataset 3 (which feeds parameter 3).
The only drawback I can see in this workaround is that if the transformation logic needs to be used for another dataset, then you have no choice by repeating it.
Longer version of the solution
The following is the longer version of the solution.
My Original Suggestion
- Use a middle parameter to do the transformation, with nested IIF statement in both Available Values and Default Values.
- Then pass the middle parameter to the cascading dataset; the middle parameter is passed directly to the dataset without the IIF statement.
- The solution failed in SSRS 2008: the cascading dataset DataSet_Employees would never get refreshed even when the middle parameter is refreshed correctly when ever a new Department is selected by the user.
- Reason for the failure: it might have to do with how the value for the middle parameter is set programmatically with the nested IIF statement, rather than by user selection.
- Good news: this solution worked in SSRS 2012 in my test.
Here are two screen shots from our failed solution.
New Solution That Worked
- Remove the middle parameter entirely.
- Pass the parameter Departments to the cascading dataset, DataSet_Employees, instead of the middle parameter.
- The Departments parameter needs to be modified to perform the transformation; use the same nested IIF statement we used in the middle parameter.
- Voila. It works.
- Reason for the success: by removing the middle parameter and coding the nested IIF in the parameter that is passed to the cascading dataset, Reporting Services made no mistake, but had to refresh the cascading dataset based on the user selection.
Here are a few screen shots from our successful solution.
The nested IIF statement is now coded in the parameter expression.