How to count distinct

[JPCogAdmin]

Hi,

Using Cognos 8, I have a one-to-many relationship.
How do I count the distinct from one table? My primary keys are a combination of two columns?

Thanks.

-J

 

[blom0344]

The distinct what?
A distinct always applies to a specific field or a composite object. If you want to count distinct combinations of 2 fields, then create a new object that concatenates the 2 fields and apply the distinct clause against this new object.
A primary key that is composed of 2 fields is already a distinct one. In that case the number of distinct values is the number of records in the table.

Ties Blom

 

[JPCogAdmin]

Hi Ties,

YEs, well, what I have is like this:

Table1 has a combination of 2 fields that makes a record unique.

Table2 has many of the 2 field combinations (making the relationship from Table1 to Table2 a one-to-many).

Table3 also has a relationship with Table1 which is -- table1 to table3 a one-to-many.

Now, I need to provide at the top of my report a total number of rows based on table1 with criteria from table2 and table3. ie.

table1 column1, column2 = table2 column1, column2 AND
table1 column1, column2 = table3 column1, column2 AND

table2 column6 ='P' AND
table3 column6 ='L' AND
table1 column6 => some date and column6 < some other date.

In report Studio, how could I do this?

Thanks

-J

 

[blom0344]

Looks to me that you may have a modelling issue, a model that consists of a many-to-one-to-many is one of the classic no-no's in modelling land. It is one of the causes of returning incorrect data from the many objects.

The many-objects (facts) need their own star in Cognos modelling terms to be able to return correct data.
Search for 'multi-fact' queries to understand it from a modelling point of view.



Ties Blom

 

[JPCogAdmin]

Ties,

Thanks for your note. Yes, I'm actually trying to figure out how to best accomplish this. I know the model is not the best -- I didn't do it, but now that I'm working on it I have to come up with something. I was thinking of doing an intersect (not too familiar with is). What do you think?

-J

 

[blom0344]

Intersect is one of the set operators. It yields the common set from 2 sets, like:

A
B
C
intersect
C
D
E

would yield C as result.

Back to your question.
Let us assume table has a primary key consisting of fields
XXX and YYY
It is unique for table 1, so counting the primary key is equivalent to counting the rows.
The moments you are going to add conditions through other tables (with many relation to table1) you will get what is called a chasm trap.
For instance , if for a given key the many-one-many
is 5-1-6 then (without additional conditions) no less than 30 rows will be returned.
The easiest way out of this,in your case is to simply add a query that fetches the distict count of the primary key of table 1, like:

SELECT COUNT (DISTINCT XXX||YYY) FROM TABLE 1
WHERE << joins>>
<< conditions>>

Another possible solution is to do this within the report, but there may be issues with counting objects and placing them at the top of the report.

Ties Blom

 

[JPCogAdmin]

Ties,

That's exactly what I just did:

SELECT COUNT (DISTINCT XXX||YYY) FROM TABLE 1
WHERE << joins>>
<< conditions>>

However, the user will have to select the facility through a prompt menu. When I wrote the native SQl query I don't think there is a way to pass the variable "Parameter1" to this query is there?

-J

 

[JPCogAdmin]

Ok,

Say I create a star schema. that that mean that one of those tables (table3) will have to taken out? Because a star schema will have all the rows in the fact table and only one per row for the surrounding dimensions.
ie. dim1 to fact1 is one-to-many.

-J

 

[blom0344]

A star schema contains one fact object (many) connected to dimensional objects (one)

To answer the prompt question. It should be perfectly possible to create prompts on whatever fields from either table 1,2,3.

So, even with an improper datamodel you should still be able to return the correct number of rows by means of the distinct count

Ties Blom

 

[JPCogAdmin]

Ties,

There are distinct count based on the two fields ie.
count ( distinct xxxx||yyyy )
xxxx is a columns that identifies a facility. If the user select xxxx1 or xxxx2 and I do a count on xxxx then they will get the wrong number. Ties, how would I then pass the 'Parameter1' to the SQL piece that counts directly with count ( distinct xxxx||yyyy ) where <criteria>...

-J