Conditional Parameter in Where Clause

I'm trying to figure out a way to filter a dataset using a parameter only when the user enters a value for the parameter and to not apply the filter if the parameter is left blank (or null) by the user. I would like to do this within the WHERE clause of the SELECT statement to minimize the size of the dataset whenever possible. Is there such a thing as a default parameter value that equates to "any value"?

Nothing I have tried works (but I'm new to SQL, Report Server and the Visual Basic Development Environment).

Thanks in advance,

Chris

Rather than leaving the parameter unselected, you need to add an option with a value of NULL and text that matches your scenario e.g. blank, "All", "N/A", "Unspecified" etc. To do this you'll need to modify the query for the paramter dataset to:

SELECT id = NULL, name = 'All'
UNION ALL
SELECT id, name
FROM param_table

Then update your main query with the following WHERE clause

WHERE id = ISNULL(@.param, id)

so when the null option is selected the WHERE clause equates to id=id which is always true and hence all rows are returned.

Hope this helps.

|||

Thanks Adam,

I was not familiar with ISNULL. I got it to work sort of like I wanted it to by checking the "Allow Null Value" checkbox and making the default value NULL in the Report Parameters dialog box and then putting this in the WHERE clause:

WHERE LITEM.SIZE = ISNULL(@.Input_Size, LITEM.SIZE)

However, I could not figure out where to put the following statement (everything I tried resulted in an error - but I'm probably missing something obvious):

SELECT id = NULL, name = 'All'
UNION ALL
SELECT id, name
FROM param_table

...and therefore, the user must uncheck the NULL checkbox in order to enter a filter value and it's not real obvious that when NULL is checked, that the filter is not applied.

Thanks again for pointing me in the right direction!

Chris

|||

By your response it seems like your parameter is a textbox the user types into, is that correct?

My prerred way is to present the user a list of options i.e. a dropdown. In that case you don't get a null checkbox. The options in the dropdown can either be typed in on the paramter screen or can come from a dataset. The SELECT statement I provided is meant as an example of query used to populate such a dataset i.e. it includes a NULL option.

If you wish to use a textbox then you could alter your SQL query and rather than using ISNULL you could use an OR in your WHERE clause as follows

WHERE LITEM.SIZE = @.Input_Size
OR @.Input_Size = '' -- empty string

If LITEM.SIZE and @.Input_Size are integers then it gets a little more complicated. You'll need to experiment.

|||

Adam,

Thanks! It's now working just the way I wanted it to!

Chris Heitman

Conditional MDX ?

I've got a solution where I need to apply some adjustment to a measure for a given scope:

Scope ([Organization].[Organization].&[13], [Product].[Division Temperature].&[Frozen], [Measures].[FOB]); This = [Measures].[FOB] - 1.25; End Scope;

This works fine, but given that this solution uses role based security to limit access to specific dimension data, there are some roles that don't have access to the [Organization].[Organization].&[13] and the MDX fails parsing.

Is there any way to make a conditional MDX that I can use to only apply the given MDX SCOPE when the role has access to the dimensions used in the statement ?

Trond

I guess it is safe to ignore the entire SCOPE for the users who have no access to Organization 13. One quick way to do it, is to change the ScriptErrorHandlingMode to IgnoreAll (although this will start ignoring all other errors). More details here: http://www.sqljunkies.com/WebLog/mosha/archive/2005/05/02/13315.aspx

HTH,

Mosha (http://www.mosha.com/msolap)

|||

Note that ScriptErrorHandling has some limitations (in RTM and SP1) whereby certain kinds of script errors are not ignored. This will be fixed in SP2.

|||

Thank you for the input - ScriptErrorHandling did indeed work but I ended up with a different solution in the end.

I guess I was so into using SCOPE that I didn't see any other tool/way to do it. Once I took a new look at it, I ended up with not using SCOPE at all, but rather a CASE:

Case When [Organization].[Organization].CurrentMember.Name = "Norway" And [Product].[Division Temperature].CurrentMember.Name = "Frozen" Then ([Measures].[Net Sales] / [Measures].[Volume]) - 1.25 Else ([Measures].[Net Sales] / [Measures].[Volume]) End

Memo to self: Read up on MDX before the next SQL 2005 project !

|||

Another approach that appears to work (at least in Adventure Works) is to first test the member using IsError(), like:

Create Set CurrentCube.[OrgSet] As iif(IsError(StrToMember("[Organization].[Organization].&[13]")),
{}, {[Organization].[Organization].&[13]}); IF [OrgSet].Count > 0
THEN ([Measures].[FOB], [Product].[Division Temperature].&[Frozen],
[OrgSet]) = [Measures].[FOB] - 1.25 END IF; |||

Please note, however, that this solution is far less efficient then the one using SCOPE. Likewise, Deepak's suggestion of using IF/END IF is also less efficient.

Probably you would get the best performance by combining Deepak's idea of building named set with SCOPEs, i.e.

CREATE SET orgs = ...;

SCOPE (orgs);

...;

END SCOPE;

|||But I couldn't figure out how to build the set, such that the assignment wouldn't update any cells when the targeted member is not accessible to the role/user. The empty set that I substituted for the secured dimension seemed not to narrow the scope, so all accessible members got updated...|||Deepak, the empty set in the SCOPE translates to the empty subcube, and therefore no cells are affected by the assignmets inside this SCOPE. Therefore, since for the user with dimension security the named set will end up empty - it will behave as that portion of MDX Script didn't exist.|||

Mosha, thanks for clarifying that - looks like my version didn't work because I also included the measure in the scope, like:

>>

Scope({[Measures].[FOB]}, [OrgSet]);

This = [Measures].[FOB] - 1.25; End Scope; >> Instead, if only the Org set is specified in the scope, then the empty set defines an empty subcube. So '({[Measures].[FOB]}, {})' isn't empty, but '({})' is:
>> Scope([OrgSet]); ([Measures].[FOB]) = [Measures].[FOB] - 1.25; End Scope; /* OR */

Scope([Measures].[Order Quantity]);

Scope([OrgSet]);

this = [Measures].[Order Quantity] * 2;

End Scope;

End Scope;

>>

Conditional Formatting on a table

Hi all,

Any ideas how to apply conditional formatting depending on the value of a particular cell in a table. Basically i want to say that if a value is less than 0, then that figure should have a red background, if the figure is = 0, then leave it white, and then if the figure is more than 0, then put a green background on it. Any ideas how to do this??

Miles

Here is an article that may help you outhttp://blogs.msdn.com/swisowaty/attachment/661446.ashx

About half way into the article it talks about conditional formating with colors.