Passing Null value via drill through

[amit_pjoshi]

Hello Expert,

Is this possible to pass the null value via drill through from main report to detail report because we have got multi select training parameter having Yes and Null values respectively in the database and while displaying the parameter we shows 'No' in case it has null but does not filter the value in detail.
I also tried the following filter condition in query filter:
case
when (?p_Training? = 'No') THEN ( [Attended Training] = null)
when (?p_Training? = 'Yes') THEN  ([Attended Training] = 'Yes')
else
([Attended Training] = 'Yes' OR  [Attended Training] = null )
endCan you please help me please its very urgent?

Regards,
Amit

[Ammus1234]

Try this filter in query.

(?p_Training? = 'No' and  [Attended Training] is null )
or
(?p_Training? = 'Yes' and  [Attended Training] = 'Yes')

[amit_pjoshi]

Hello,
Thanks for your quick response.
Following condition works when I select either 'Yes' or 'No' but when I do multi select then it fails because for 'No' its passing value is null.
code:-
case
when (?p_Training? = 'No') THEN ( [Attended Training] = null)
when (?p_Training? = 'No') THEN ( [Attended Training] = 'Yes')
else ( [Attended Training] in ?p_Training?)
end
What should I write in else statement of case when user selects both (Multi select) Yes and No from parameter please?
Note:- We have to pass both 'Yes' and Null together to get both the result
Regards,
Amit J

[navissar]

Hi Amit,
Let me make sure I get this straight:
Your source data item returns either NULL or Yes. You want to filter your results based on this selection, which could be either Yes, No or both.
A general comment: it's a whole lot of work for nothing. If your prompt only has two values, make it single select and optional. Instead of selecting both, the user could just not select anything, and have the data unfiltered.
Having said that, here are several ways to do it your way:

1. Filter to be set to:
('No' in (?p_training?) and [Attended Training] is null)
OR
('Yes' in (?p_training?) and [Attended Training]='Yes' )

--What will this do?
It looks in the result string of p_training. When "No" is in there, it will filter null. When "Yes" is in there, it will filter Yes. When both, it will filter both.

2. Filter to be set to:
(case when [Attended Training] is null then 'No' else [Attended Training] end) in (?p_training?)

--What will this do?
It creates a calculation based on [Attended Training], replacing nulls with "No" so the parameter and the data item are on equal grounds, and then just checks if the row value for that calculation is in the result set for ?p_training?.

3. Change [Attended Training] in the query:
case when [source].[of].[field].[Attended Training] is null then 'No' else 'Yes'
Then filter on this field.


One more tip: I wouldn't preface my parameter names with "p_". If you ever have to create a dynamic URL to that report, you'll have to add p_ before each prompt name. Meaning, in a URL, you'll have to call your parameter value like so: p_p_training - Which is uncomfortable.
Good luck!

[amit_pjoshi]

Hi Nimrod,

Thank you so much I tried your 1st way it was working fine with all the combinations  :)

Thanks again.

One more question please:

While setting up the Drill Through from Detail to Dashboard report, after clicking on the Edit parameters button it does not display check mark on some multi select parameter even they are multi selected but it shows the same while making this from Dashboard to Detail report.

Note:- Detail and Dashboard have same parameters.

Please find the attached Image for your reference:

[velniaszs]

can it be that in filters you use = instead of IN?

[amit_pjoshi]

Hi,

Thanks your response help me find out the reason... actually I'm not using those parameters in the Dashboard but using only in detail report to get filter and I think this might be the reason.


Regards,
Amit J