Recently it happened to me to manage a complicated SQL report out of AX. Regardless of the query, one of the big challenges was dealing with multi value parameter for SSRS. I faced a problem where I was unable to find an reasonable answer over the net either and the time was matter. All solutions are about to how to split or join the selected values of a multi value parameter and then pass it to SQL query or so on;Well, I'm not going to dig into Joint or Split functions in here. It's not so easy to make a range values to filter the report group or make a colored cell by the user discretion options when you do not know how many options are going to be selected and the order of the selections are important too. In this case you need to recognize the user selection first, but this time it's not as easy as a simple Joint to match against a dataset field. You need to pars each value, compute and then compare. So perhaps you need to use from either IIF or SWITCH to catch user selection and traverse between selected values to compute and make a range among them. The obstacles here comes up by an unknown error (#Error!) inside IIF where keep it from moving forward. In order to catch the user selection we need to check what the index of a selected option is :
Array.IndexOf(Parameters![NAME].Value, [VALUE])
It's the only way to use from the above expression within IIF or SWITCH when you want to check to see whether the Parameter has valid index for a given value and then proceed to fetch its value :
IIF(Array.IndexOf(Parameters![NAME].Value, [VALUE]),
Parameter![NAME].Value(Array.IndexOf(Parameters![NAME].Value, [VALUE]),
nothing)
however it raised an unknown error due to the index is out of range ( = -1)! Am I missed something? It seems IIF never checked for the condition and it went ahead to execute the expression instead! The answer is both right and wrong.Right because the expression evaluates before checking the condition and wrong because it was not actually executed , by the way we got that error! It's weird because if it's only an internal evaluation process then we shouldn't have got that error.
Indeed the reason is :
Conditional functions purposely evaluate the expression before they actually check the condition part for performance intensions, reported by Microsoft SQL developers team. So this is a bad news because sounds like you will never be able to guess the selected option and parse it using nested IIF.So I have got to figure it out to find out a workaround. I realized that there are two practical solutions to overcome this issue however I chose the second one :
1. Using from custom code to manipulate the values and as far as we're using from VB there is no obstacles to use from nested IF or Switch to compute the values. Downside is, per my MCITP in business intelligence and big companies job interview questions, the best practice suggests keep from using assembly or custom codes in reports as much as you can due to code promotion, upgrade and portability situations.
2. I created report Variables per Parameter values, have the variables to fetch the index of every prospective selection (it might include -1 as well which it means not selected):
=Array.IndexOf(Parameters![NAME].Value, [VALUE])
then by using the variables value I was able to :
1. Recognize the selected option/value.
2. Compare a dataset field against every selections that the user has made to see if it is a match
=switch(Variables![NAME].Value <> -1
switch(Fields![NAME].Value >= 0 AND Fields![NAME].Value < 100, "Green")
Conclusion:
Reminding that, considering this solution for a complicated circumstances where you do not know how many selections the user makes and you need to make a range from the selection(s) and ultimately compare every selection against the report data (not database or query) or whatsoever situations that you're being asked to compute multi-value parameter values, so this is the practical way to catch the selection without getting "Index out range" error or #Error when using nested IIF - to catch the index - due to the reason that how IIF does pre-evaluates the expression purposely for performance intentions.
Array.IndexOf(Parameters![NAME].Value, [VALUE])
It's the only way to use from the above expression within IIF or SWITCH when you want to check to see whether the Parameter has valid index for a given value and then proceed to fetch its value :
IIF(Array.IndexOf(Parameters![NAME].Value, [VALUE]),
Parameter![NAME].Value(Array.IndexOf(Parameters![NAME].Value, [VALUE]),
nothing)
however it raised an unknown error due to the index is out of range ( = -1)! Am I missed something? It seems IIF never checked for the condition and it went ahead to execute the expression instead! The answer is both right and wrong.Right because the expression evaluates before checking the condition and wrong because it was not actually executed , by the way we got that error! It's weird because if it's only an internal evaluation process then we shouldn't have got that error.
Indeed the reason is :
Conditional functions purposely evaluate the expression before they actually check the condition part for performance intensions, reported by Microsoft SQL developers team. So this is a bad news because sounds like you will never be able to guess the selected option and parse it using nested IIF.So I have got to figure it out to find out a workaround. I realized that there are two practical solutions to overcome this issue however I chose the second one :
1. Using from custom code to manipulate the values and as far as we're using from VB there is no obstacles to use from nested IF or Switch to compute the values. Downside is, per my MCITP in business intelligence and big companies job interview questions, the best practice suggests keep from using assembly or custom codes in reports as much as you can due to code promotion, upgrade and portability situations.
2. I created report Variables per Parameter values, have the variables to fetch the index of every prospective selection (it might include -1 as well which it means not selected):
=Array.IndexOf(Parameters![NAME].Value, [VALUE])
then by using the variables value I was able to :
1. Recognize the selected option/value.
2. Compare a dataset field against every selections that the user has made to see if it is a match
=switch(Variables![NAME].Value <> -1
switch(Fields![NAME].Value >= 0 AND Fields![NAME].Value < 100, "Green")
Conclusion:
Reminding that, considering this solution for a complicated circumstances where you do not know how many selections the user makes and you need to make a range from the selection(s) and ultimately compare every selection against the report data (not database or query) or whatsoever situations that you're being asked to compute multi-value parameter values, so this is the practical way to catch the selection without getting "Index out range" error or #Error when using nested IIF - to catch the index - due to the reason that how IIF does pre-evaluates the expression purposely for performance intentions.
No comments:
Post a Comment