General OBIEE

Get Involved. Join the Conversation.

Topic

    Ian Schroeder
    How do you make columns show distinct values in a table in...
    Topic posted April 1, 2016 by Ian SchroederGreen Ribbon: 100+ Points 
    1784 Views, 11 Comments
    Title:
    How do you make columns show distinct values in a table in BI Publisher
    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.

    Comment

     

    • Arun Raj

      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.

    • Arun Raj

      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. 

    • Ian Schroeder

      Workforce Management - Worker Assignment Event Real Time

      Thank you for speaking with me by the way.

    • Arun Raj

      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?

      • Arun Raj

        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

       

      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