Setting Filter condition in Query

[spattewar]

Hi,

I am using a single check box to on the prompt page to get the user input and use that input to set a filter on one of the column of the query.

The paramter that I use while creating the prompt was Prompt_IncludeCash and I have put a sinle value in the static choices list with the value as 'IC'. I am assuming that when the user checks the check box the value that will be passed will be 'IC'. I want to use that value to create a data filter and I am using it in the way below but am not able to get it working.

IF ParamDisplayValue('Prompt_IncludeCash') = 'IC' THEN
[MajorAssetTypeDesc] <> 'TTTTTT'
ELSE
[MajorAssetTypeDesc] <> 'Cash Balance'
END IF

Your help on this will be very helpful

Thanks and Regards,
$wapnil

 

[blom0344]

Look again at your IF construction. You are using <> instead of =. Assigning a value should in any case be based on positive selection. How else would you expect a value to be returned?

Ties Blom

 

[spattewar]

Actually the condition that I want is

1) When user selects to include cash
[MajorAssetTypeDesc] IN ('Equities','Fixed Incomes',...,'Cash Balance')
2) If cash is not to be included
[MajorAssetTypeDesc] IN ('Equities','Fixed Incomes',...)

any suggesstion on how to compile a condition for this.

 

[spattewar]

any help on this will be greatly appreciated. I am kind of stuck with this issue.

Thanks again.
$wapnil

 

[Nuffsaid]

Hi,

Not sure what product you're using so I'll assume Report Studio.

Create a prompt with 2 static choices such as;

?Include Cash? Yes / No

then your filter should be;

(
?Include Cash?='Yes'
and
[MajorAssetTypeDesc] in ('Equities','Fixed Incomes',...,'Cash Balance')
)
OR
(
?Include Cash?='No'
and
[MajorAssetTypeDesc] IN ('Equities','Fixed Incomes',...)
)


BTW, you cannot use if then else or case statements in filters.

You have to work with AND / OR logic.


Nuffsaid.

 

[spattewar]

Hey Nuffsaid.....it worked. Thanks a lot! you were of great help. I added 2 static choice as suggested and used the OR condition instead of the if then else statement and it worked.

The only small issue here is that when I added 2 static choices
1) it displayed 2 radio button on the prompt page. I wanted a single check box on the prompt page for this. Because the user says that if he checks the check box then the condition is include cash otherwise no cash.
2) Why are the checkboxes converted to radio buttons? Does the reportal studio check the condition and then renders the prompt?

Thanks again.
$wapnil