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

Title: IF Statement to limit a list for One item of another List
Post by: razprod on 16 Nov 2011 12:46:52 PM

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 "<>".

Title: Re: IF Statement to limit a list for One item of another List
Post by: pricter on 16 Nov 2011 01:20:03 PM

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?

Title: Re: IF Statement to limit a list for One item of another List
Post by: razprod on 16 Nov 2011 01:25:50 PM

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.

Title: Re: IF Statement to limit a list for One item of another List
Post by: pricter on 16 Nov 2011 01:34:52 PM

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?

Title: Re: IF Statement to limit a list for One item of another List
Post by: razprod on 16 Nov 2011 02:11:39 PM

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

Title: Re: IF Statement to limit a list for One item of another List
Post by: pricter on 16 Nov 2011 02:26:25 PM

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

Title: Re: IF Statement to limit a list for One item of another List
Post by: razprod on 16 Nov 2011 03:26:56 PM

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!

COGNOiSe.com - The IBM Cognos Community

IBM Cognos 10 Platform => Cognos 10 BI => Report Studio => Topic started by: razprod on 16 Nov 2011 12:46:52 PM