Posts

Posts

  • Karthik K
    Report on BIP Catalog Folders details65.0
    Topic posted September 13, 2017 by Karthik KGold Medal: 3,500+ Points, tagged BI Publisher, Core HR, Fusion, Reports, SQL in General OBIEE public
    Title:
    Report on BIP Catalog Folders details
    Summary:
    Report on BIP Catalog Folders details like Roles and Permission etc.
    Content:

    Dear Team,

    How to report on BIP Catalog Folders details like Roles and Permission etc.

    Need to understand if any report or relevant table names available in Fusion HCM in order to extract list of all roles and permission associated with all the folder/subfolders/reports in BIP catalog.

     

    Thanks

    Karthik

  • Peter Kirwan
    report on user account details - Role Names43.0
    Topic posted May 12, 2017 by Peter KirwanBlue Ribbon: 750+ Points, tagged BI Publisher, BI Publisher Data Models, BI Publisher Report Templates, Core HR, Data Models, Fusion, OBI Answers, OBIEE Answers, OTBI, Reports, SQL in General OBIEE public
    Title:
    report on user account details - Role Names
    Summary:
    SQL for reporting on employees roles
    Content:

    Can someone see how to stop the multiple rows from returning in below SQL?

    select  "PER_USERS"."USERNAME" as "USERNAME",
      "PER_ROLES_DN_VL"."ROLE_NAME" as "ROLE_NAME",
      "PER_ALL_PEOPLE_F"."ATTRIBUTE3" as "ATTRIBUTE3",
      "PER_PERSON_NAMES_F"."FIRST_NAME" as "FIRST_NAME",
      "PER_PERSON_NAMES_F"."LAST_NAME" as "LAST_NAME",
      "PER_ALL_PEOPLE_F"."EFFECTIVE_START_DATE" as "EFFECTIVE_START_DATE",
      "PER_ALL_PEOPLE_F"."EFFECTIVE_END_DATE" as "EFFECTIVE_END_DATE"
     from "FUSION"."PER_PERSON_NAMES_F" "PER_PERSON_NAMES_F",
     "FUSION"."PER_ALL_PEOPLE_F" "PER_ALL_PEOPLE_F",
     "FUSION"."PER_ROLES_DN_VL" "PER_ROLES_DN_VL",
     "FUSION"."PER_USER_ROLES" "PER_USER_ROLES",
     "FUSION"."PER_USERS" "PER_USERS"
     where   "PER_USERS"."USER_ID"="PER_USER_ROLES"."USER_ID"
     and  "PER_USER_ROLES"."ROLE_ID"="PER_ROLES_DN_VL"."ROLE_ID"
     and  "PER_USERS"."PERSON_ID"="PER_ALL_PEOPLE_F"."PERSON_ID"
     and  "PER_ALL_PEOPLE_F"."PERSON_ID"="PER_PERSON_NAMES_F"."PERSON_ID"
      and   "PER_ALL_PEOPLE_F"."EFFECTIVE_END_DATE" >CURRENT_DATE
    order by "PER_USERS"."USERNAME" ASC

  • Kim Welker
    Return Candidate Name only for Specific CSW Selection...114.0
    Topic posted March 2, 2017 by Kim WelkerBlue Ribbon: 750+ Points, tagged Analyses, Customizing Reports, OBI Answers, OBIEE Answers, SQL in General OBIEE public
    Title:
    Return Candidate Name only for Specific CSW Selection steps/statuses
    Summary:
    How to modify my SQL statement to remove redundancies
    Content:

    Hi!  I've created a report to return all Open Requisitions as well as Filled Requisitions for the past year.  The report filters work well, however I have additionally created a field using the following SQL statement in an attempt to display Candidate Name only when they match specific Steps and Status conditions:

    CASE WHEN "Submission CSW Status - Current"."Current Step Name" IN ('Offer', 'Pre-Hire') AND "Submission CSW Status - Current"."Current Status Name" IN ('Accepted', 'Extended', 'Approval in Progress', 'Approved', 'Draft', 'Offer to be made', 'Candidate Record to be updated', 'Integration To WorkDay') THEN "Candidate Identification"."Name" ELSE ' ' END

    The names are populating as desired, however I am getting an additional row for Requisitions that not only have an Offer/Hired Candidate against them, but also have other Candidate submissions which simply aren't named based off the above SQL statement.  Basically the extra row is representing a placeholder for all the other Candidates that have applied but do not meet the conditions of the SQL statement. 

    Any ideas on how to suppress those extra blank rows?  If a Req does not have anyone in the Offer/Hire process, then I would still want the Req. to be displayed.  In that case the Candidate Name should be blank.

    Below is a sample of what's happening, however the attached doc is a littler easier to read.  Thanks in advance for any advice!

    Requisition Number_Position Number Name
    0044262_P0048293 John Smith
    0044262_P0048293  
    0044360_P-0019999 Jane Doe
    0044360_P-0019999  
    0044365_P0048452 Charlie Johnson
    0044366_P0048453 Sally Thomas
    0044367_P0048454 John Doe
    0044372_P0048457 Jeff Johnson
    0044372_P0048457  
  • Aaron Leggett
    Security Console TablesAnswered5
    Topic posted July 20, 2017 by Aaron LeggettSilver Trophy: 7,500+ Points, tagged BI Publisher, BI Publisher Data Models, BI Publisher Report Templates, Data Models, Fusion, OBI Answers, OBIEE Answers, Report Output, Setup/Administration, SQL in General OBIEE public
    Title:
    Security Console Tables
    Content:

    Hi Everyone 

     

    Quick question, is there a table that I can query in BIP that will show me the Roles and Roles Description? We have been struggling to assign the right roles to the right people throughout our implementation and I feel this report will give us a much better understanding for our programme team to see what each role gives access too. Below is a screenshot from the Security Console showing what I would like to extract (Role Name, Role Code and Role Description...if possible the type of role too, such as Duty, Data etc.). 

     

    Image:
  • Manish Pesswani
    Security In Data Model25.0
    Topic posted May 8, 2017 by Manish PesswaniBronze Medal: 1,250+ Points, tagged BI Publisher, Reporting and Analytics, Sample Reports, Setup/Administration, SQL in Reporting and Analytics for SCM public
    Title:
    Security In Data Model
    Summary:
    How to restrict user by not getting data of other BU in BIP Report
    Content:

    Hi Champs,

    Let's say my user 'A' has access to only 'X' Business Unit.

    How should BIP data model be configured so that only 'X' Business unit data is accessed by User 'A' ??

    In Ebiz on premises, we achieve same by using synonyms or initializing sessions

  • Tim Welsh
    Setting relative dates in a filter for an OTBI AnalysisAnswered173.0
    Topic posted April 28, 2017 by Tim WelshBlue Ribbon: 750+ Points, tagged Analyses, OBIEE Answers, OTBI, Reports, SQL in General OBIEE public
    Title:
    Setting relative dates in a filter for an OTBI Analysis
    Summary:
    In an OTBI Analysis is there a way to add into a relative date range into a filter?
    Content:

    I need an Analysis with a filter to pick up dates within the last week.  I don't see a way to do this in the normal filter properties and I'm getting errors when i add the below code into "Convert this filter to SQL"  Is there a way to do this either with the regular filter or with the Advanced SQL filter?  

     BETWEEN (SYSDATE - 7) AND SYSDATE

  • Gal Shani
    Show report results in dashboard with promptAnswered15.0
    Topic posted October 18, 2015 by Gal ShaniSilver Medal: 2,000+ Points, tagged Analyses, Customizing Reports, OTBI, Report Output, Reports, Sample Reports, Setup, Setup/Administration, SQL in General OBIEE public
    Title:
    Show report results in dashboard with prompt
    Content:

    Hello,

    I know there is an option to show the report results immediately within dashboard without clicking on the apply button of the prompt (after defining default value).
    if someone can share his knowledge regarding this issue on how to do it it will be great!

    Thank you,

    Gal.

     

  • Cassandra Kurth
    Something off in SQL expression for criteria1
    Topic posted June 8, 2016 by Cassandra Kurth, tagged Analyses, SQL in General OBIEE public
    Title:
    Something off in SQL expression for criteria
    Summary:
    Trying to capture last month's data
    Content:

    The report functions with the expressions below; however, It doesn't seem to actually capture the last day of the previous month. I run the data for May and know there are results for 5/31 but only captures up to 5/29. What is wrong with the expressions. The Beginning of Previous Month expression seems to capture from 5/2 and I have verified that is correct.

    Beginning of Previous Month

    TIMESTAMPADD(SQL_TSI_MONTH, -1, TIMESTAMPADD(SQL_TSI_DAY, DAYOFMONTH(CURRENT_DATE) * -(1) + 1, CURRENT_DATE))

    End of Previous Month

    TIMESTAMPADD(SQL_TSI_DAY , -(1), TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH(CURRENT_DATE) * -(1) + 1, CURRENT_DATE))

     

    Thank you in advance for any assistance!

    Cassie Kurth

  • Aaron Leggett
    SQL IS NULL error3
    Topic posted February 14, 2017 by Aaron LeggettSilver Trophy: 7,500+ Points, tagged Analyses, Core HR, OBI Answers, OBIEE Answers, OTBI, Public Sector, Reports, SQL in General OBIEE public
    Title:
    SQL IS NULL error
    Content:

    Hi 

    We're trying to create multiple reports where we want to bring back a column with no data in (e.g. Missing Bank Details where the employee does not have a bank account number). When we run the report without a filter, we get back all the results as expected (records who have bank details and records who do not have bank details). When we apply the IS NOT NULL filter, we get back all the results as expected; records whom have bank details. However, when we apply the IS NULL filter, we get an error and don't get back any results when we should be getting back around 300. 

    Currently we're having to run the report without a filter and then apply filters in excel to find out which records are missing bank details. Is there any other formula we could try other than IS NULL? 

  • Peter Kirwan
    Stats1
    Topic posted February 13, 2017 by Peter KirwanBlue Ribbon: 750+ Points, tagged Analyses, BI Publisher, BI Publisher Data Models, BI Publisher Report Templates, Core HR, Data Models, Fusion, OBI Answers, OBIEE Answers, OTBI, Report Delivery, Report Layouts, Report Output, Reports, Sample Reports, SQL in General OBIEE public
    Title:
    Stats
    Summary:
    Stat Report
    Content:

    is it possible to report where you have a count of the number of employees in a age range - the age ranges would be columns and then rows would be the Department Grouping they are in?

    I would then section by Grade