Is count distinct works in cognosFM?
Yes, it does.
could you please explan me how can we do that in FM or in cube level
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.
Also check the properties of the measure. The 'Category Count' can also do this.
sorry for late reply.its tranformer 7.3
In that case, you can do the above in FM and export the Query Subject as an IQD to be used in Transformer.