Is it possible to exclude filtered data from main list?

[metalfly]

Just wondering if it's possible to make a filter/conditional format to exclude a filtered data in Report Studio?

I have a set of special data within a main database which I can filter out to List, however I would like to be able to list my main database to exclude these special data? I've been trying a combination of detail filters and conditional statements but have had no luck, is it possible to exclude data in Report Studio?

 

[blom0344]

List my main database?

Difficult to get where you are after.
However every filter definition will
a. end up as part of the where clause of the SQL or:
b. added by the server as a special filter on a resultset (like with an aggregate filter)

Data is presented to you directly from the server..

Ties Blom

 

[metalfly]

Sorry, I'll try to be more clear.

Right now I have a main database which I can list all the attributes into a List easy enough.

However there are these sets of special data within the main database which I can obtain by making a couple of filters

I am able to list these special data in a separate List easy enough, but I haven't been able to list my main database without these special datas been included as well.

I have been able to sort them and conditionally format them to they standout in the main database List but I can't seem to be able to exclude them.

Is it possible to exclude these data which requires two separate filters?

 

[blom0344]

I think that you are after an except query..

Example:

Table 1

A
B
C
D
E
F

Table 2

A
C

Now if you want those entries in table 1 that are not present in table 2 then:

SELECT TABLE1.COLUMN FROM TABLE1
EXCEPT
SELECT TABLE2.COLUMN FROM TABLE2

yields:

B
D
E
F

Cognos 8 allows you to build such a construction quite easily from the query explorer where you select the except operator from Insertable Objects GUI



Ties Blom

 

[sraj1]

What will be the result for situation like this...

Table 1

A
B
C
D
E
F

Table 2

A
A
C

will the result when using Except be

B
D
E
F

or

A
B
D
E
F

 

[blom0344]

B
D
E
F

But shouldn't be too hard to test yourselves, isn't it?

Ties Blom

 

[sraj1]

I did try, but I am getting the result I mentioned above

A
B
D
E
F

Here is the scenario, Query 1 is pulling A,B,C,D,E and Query 2 with couple of additional criteria added and is pulling duplicate records. (Duplicate in the sense, when u pull the same number of fields as query1). (A,A,B,C,D,D,E)

 

[blom0344]

Each and every record is compared for all the fields fetched. So, if the distincts sets are slightly different between (field-wise) the 2 queries , then an except will not work as it should

Ties Blom

 

[sraj1]

Both the queries are pulling the same fields,, except that query2 has additional criteria defined, but this criteria field is not being pulled. Well here is the scenario

Query1: Select Col1, Col2 from tableA a, tableB b where a.ID = b.ID

Col1 COl2
A NY
B CT
C DN
D DE

Query2: Select Col1, Col2 from tableA a, tableB b, tableC c
where a.ID = b.ID and a.ID = c.ID and c.date > 2009-01-01

Col1 Col2
A NY
A NY
C DN

----------

Col1 Col2 Col3(date)
A NY 2009-01-10
A NY 2009-01-11
C DN 2009-01-10

So when I use quer1 ECXEPT query2

the result set is

Col1 COl2
A NY
B CT
D DE

when it should not pull row 1.

 

[blom0344]

Where is Col3(date)
coming from?

Ties Blom

 

[sraj1]

Col3(Date) is coming from tableC, but its used in the condition criteria only. This field is not being pulled in the select statement for query2. So when pulling fields from Query2, we have duplicates, but when you actually see at the col3(Date) at the database its not duplicate, it has a different dates on each record.

 

[blom0344]

Check the SQL that Cognos generates for each query. If the query contains no facts at all, then Cognos explicitly fetches a distinct set (by adding distinct to the SQL)

So,

A NY
A NY
C DN

puzzles me, I would expect Cognos to fetch:

A NY
C DN


Ties Blom