Count distinct in cognos Fm

[jahnavi]

Is count distinct works in cognosFM?

[Steve]

Yes, it does.

[jahnavi]

could you please explan me how can we do that in FM or in cube level

[cognostechie]

Which version are you using?

In 8.4, you can use a package from FM as the data source in Transformer. When you add the package , double-click on the properties of the Query subject in Transformer, go to General Tab, you will see a property called 'Auto-Summarize'. That one automatically add the distinct keyword to the query. It will remove the duplicates.

You can also create a Query subject in FM and use the word distinct in it.

Example -

You have a table to show Claims like this -

Claim ID  Claim Suffix   Claim Amount
----------------------------------------------

C01          001                 100
C01          002                  200
C01          003                   50
C02          001                 200

Essentially, there are only 2 claims so the Claim Count should be 2. So in the FM query, you
can hand type the SQL as:

Select distinct
      ClaimID
from database.dbo.Claim

After this, add a calculated column and type 1, Name it Claim Count. This will be the result:

Claim ID     Claim Count
------------------------------

C01                 1
C02                 1

Then just use the Claim Count as a regular measure in Transformer.       

[cognostechie]

Also check the properties of the measure. The 'Category Count' can also do this.

[jahnavi]

sorry for late reply.its tranformer 7.3

[cognostechie]

In that case, you can do the above in FM and export the Query Subject as an IQD to be used in Transformer.