IF Statement to limit a list for One item of another List

[razprod]

I am trying to limit results in a report which in its current state is working fine.

I have to now limit based on the following:

[Location] and [Sale Type]

Both these data items contain multiple entries, ie:

[Location] has: Toronto, Montreal, Ottawa, Quebec, etc
[Sale Type] has: Open, Closed, Virtual, Off-Site, etc

I am creating a detail filter that contains the following:

IF ([Location] = 'Toronto')

THEN ([Sale Type] <> 'Open')

ELSE ([Sale Type])


Note: I have substituted the '<>' with Not/Not In with no sucess

I get the following errors:

UDA-QOS-0006 Error
UDA-SQL-0358 Line 32: Syntax error near "<>".

QE-DEF-0459 CCLException
RQP-DEF-0177 An error occurred while performing operation 'sqlPrepareWithOptions' status='-16'.
UDA-SQL-0358 Line 32: Syntax error near "<>".

[pricter]

If I understood correct you want to display
All the sales types expect the ones that are open in Toronto

So create a new data item with the following expression
if ( ([Location] = 'Toronto') AND ([Sale Type] <> 'Open')) then (0) else (1)

Add a filter that filters the above data item = 1

Does this is what you are looking for?

[razprod]

Not completly sure if I understand that one completely.

But let me expand what this filter does to the rest of the report.

the main report shows total sold units from [Sale Count] in a table against each Location.

so I am trying to limit the [Sale Type] to filter out 'Open' when it displays the total sold units.

[pricter]

Could you provide with an example what you want to achieve

For example

Location Sales Count
Toronto          10 (includes open)
Montreal          5 (includes open)
Total               12 ( does not include open)

Second question is this a list or a cross-tab?

[razprod]

Here is an example: (You had it right)

Location      Sales
Toronto        12 (7 Open, 5 Closed)
Montreal       15 (Total Open and Closed)

Total             20


This is in a List format not a cross-tab

thanks

[pricter]

I do not have a report studio in front of me but I suppose this will work.

Create a data item "Sales Count wo Open"  as
if ([Sale Type] <> 'Open') then ( [Sale Count] ) else (0)
Add this data item to your list and aggregate so you will have something like this

Location      Sales  Sales Count wo Open
Toronto         12                  5
Montreal       15                    15

Total            27                   20

And then select box type none to the bold ones

So I suppose that you have the following list
Location      Sales
Toronto         12
Montreal       15

Total             20

Please inform me if it works

[razprod]

Thank you for your assistance.

Your first respond (now that I played with it in the environment) was actually correct.

Report is working just fine now.

thanks again!