How do you make columns show distinct values in a table in BI Publisher

Ian Schroeder

Content

I am creating a report in BI publisher and it shows duplicate values for employee ID's.  Employee ID should be a distinct value and should not be returning duplicate values.

rajArun

Are you using custom data model? If yes, then you will have to check the data model to make sure the SQL query is correct.

Thanks,

Arun

Ian Schroeder

I am not exactly sure if I am using a custom data model or not.  I have simply pulled fields from a subject area and for some reason my PersonID column is showing duplicates.

rajArun

Ok. So you are creating a OTBI report. I was checking if you were creating a BI publisher report.

Try adding some filters in your analysis.

Ian Schroeder

Filters would be perfect.  But I do not know how to create a filter for distinct values in this program. 

rajArun

Can let me know the subject area you are using?

Ian Schroeder

Workforce Management - Worker Assignment Event Real Time

Thank you for speaking with me by the way.

rajArun

Happy to help.

Since it is HR / Employee data, you might want to look at any date columns to make sure you are only picking the most recent / active record. If it is a SQL we mostly look the Effective Date columns. Please check if you can utilize any Effective Date columns in your filter.

For example, an employee can have multiple assignments, however only 1 might be active. 

Ian Schroeder

That is a good idea.  I am currently filtering by Assignment Status Type equal to Active.  However, I am struggling to think of what date column to use for a filter.

Suggestions?

rajArun

May be look at the below? 

• Worker : Termination Date
• Location : Effective End Date
• Job : Effective End Date

There could be more. I am sorry I cant give you a straight forward answer without seeing the analysis being created. Hope this helps.

Ian Schroeder

Those are apparently fields we do not use.  I have looked through quite a few date fields and cannot find one worth filtering by.  I am surprised that there is not a way to SELECT DISTINCT on the field of PERSON NUMBER.

Rachel Martorelli-Oracle

 

Here's a blog that might help.   It has the xdo distinct function which can be used in bip templates.

https://blogs.oracle.com/xmlpublisher/entry/counting_distinctly