Posts

Posts

  • Tim Welsh
    Setting relative dates in a filter for an OTBI AnalysisAnswered173.7
    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

  • 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  
  • Senthilrajan Vaithianathan
    Oracle Fusion OTBI Direct Database Query/Report95.0
    Topic posted February 4, 2019 by Senthilrajan VaithianathanBronze Trophy: 5,000+ Points, tagged Analyses, BI Publisher Data Models, Customizing Reports, Data Models, Discussion Forums, Fusion, OBI Answers, OTBI, Report Output, Reports, Setup/Administration, SQL in General OBIEE public
    Title:
    Oracle Fusion OTBI Direct Database Query/Report
    Summary:
    Querying Oracle Fusion OTBI feature is being deprecated for CLOUD environment from 19A releases
    Content:

    Creating Reports with Direct Database Query in Oracle Fusion OTBI is being deprecated for CLOUD environment from 19A releases.

    Refer Doc ID 2141412.1

    Regards

    Senthil

    Image:
    Code Snippet:
  • Rashmi Rao
    Can we have a OTBI analysis based on a BI Publisher...9
    Topic posted August 14, 2018 by Rashmi RaoBronze Trophy: 5,000+ Points, tagged BI Publisher, OBIEE Answers, OTBI, Report Delivery, Reports, SQL in General OBIEE public
    Title:
    Can we have a OTBI analysis based on a BI Publisher Report's output?
    Summary:
    Can we have a OTBI analysis based on a BI Publisher Report's output?
    Content:

    Can we have a OTBI analysis based on a BI Publisher Report's output?

    Regards,
    Rashmi

  • Nina Manns
    Using an SQL Date Formula in OBIEE Report84.0
    Topic posted September 20, 2018 by Nina MannsRed Ribbon: 250+ Points, tagged Reports, SQL in General OBIEE public
    Title:
    Using an SQL Date Formula in OBIEE Report
    Summary:
    How do I display previous 30 days of data
    Content:

    Could someone please show me how to tweak the below formula.  I’d like to pull data from the previous 30 days; however, it seems to be pulling for the future 30 days instead. 

                   timestampadd(sql_tsi_day, 30, current_date)

    When I change the formula to the below example it gives me an error and doesn't return any data:

                    timestampadd(sql_tsi_day, -(30), current_date)

    Thank you.

    Version:
    OBIEE 11.1.1.9.0
  • Rebekah Baker
    Is it Possible to Use an Alias for the Bursting Sender Info?8
    Topic posted March 20, 2018 by Rebekah BakerBronze Crown: 15,000+ Points, tagged BI Publisher, Fusion, Reports, SQL in General OBIEE public
    Title:
    Is it Possible to Use an Alias for the Bursting Sender Info?
    Summary:
    Is it Possible to Use an Alias for the Bursting Sender Info?
    Content:

    We engaged a vendor to write some BI Publisher reports, which use bursting to send out emails to employees.  The email address defined in the bursting SQL for the sender information is really not what we would like to show to our employees when it hits their inbox.  We like all of our Fusion emails to come from 'Anthem Human Resources' (and we had Oracle set up this alias on their side for the seeded, system-generated emails), so that it looks like a legitimate email (our employees get nervous and like to report odd-looking emails as SPAM).  But the vendor who created these bursting reports didn't know how to create an alias for the sender info.  Is there a way to do this, to assign an alias, so that instead of the sender info coming across as ***@***.com, it would look like 'Anthem Human Resources'?

  • Tim Welsh
    Customizing a Seeded Report - Editing the Data Model and...8
    Topic posted May 19, 2017 by Tim WelshBlue Ribbon: 750+ Points, tagged BI Publisher, BI Publisher Data Models, Customizing Reports, Data Models, Discussion Forums, Fusion, OBIEE Answers, Reports, SQL in General OBIEE public
    Title:
    Customizing a Seeded Report - Editing the Data Model and using Packages
    Summary:
    The Payable Invoice Aging Data Model uses package AP_APXINAGE_XMLP_PKG. I could use instructions on how to interact in SQL with this package.
    Content:

    I am searching for usage documentation on how to use Oracle packages, specifically AP_APXINAGE_XMLP_PKG.  I have not located anything about packages in the Oracle Enterprise Repository for Fusion Apps.  

  • Anish Goswami
    Building a BO subquery in SQLAnswered8
    Topic posted May 5, 2016 by Anish GoswamiGreen Ribbon: 100+ Points, tagged Analyses, Core HR, Data Models, OBI Answers, OBIEE Answers, Reports, SQL in General OBIEE public
    Title:
    Building a BO subquery in SQL
    Content:

    Below mentioned are BO subqueries, need to use them in OBIEE. Can anyone list out anyway to do it..

     

    Image:
    Document:
  • Maarten van der Burg
    What application do you use to write your SQL65.0
    Topic posted October 25, 2019 by Maarten van der BurgSilver Medal: 2,000+ Points, tagged BI Publisher Data Models, Data Models, Fusion, Reports, SQL in General OBIEE public
    Title:
    What application do you use to write your SQL
    Content:

    I'm very curious as to what other people use to write their SQL. Personally I am using NotePad++ (with the SQL language setting to help with syntax).

    I do feel that there could be more software assistance and there might be some more automated applications out there which allow searching the repository, automatic joins, etc. Having a tool like this would speed up my work tremendously.

    Would like to discuss in this topic what you use and what you like/dislike about it.

    Regards,
    Maarten

  • Rosenerie Castillo
    Help with Formula: most recent offerAnswered6
    Topic posted February 20, 2018 by Rosenerie CastilloGreen Ribbon: 100+ Points, tagged Analyses, OBI Answers, OBIEE Answers, Report Output, Reports, SQL in General OBIEE public
    Title:
    Help with Formula: most recent offer
    Summary:
    I need help creating a formula to pull in the most recent offer
    Content:

    I'm pulling together a list of YTD offers.  I am finding records where the candidate has accepted the offer then declined a few days later.  I would like to the report to only show the most current offer.  I can not use the "Current Step Name" as some of the candidates have moved to "Hire". 

    In the example below, I only want to see the Candidate Declined record

    Any help would be greatly appreciated.  Thanks in advance.

     

    Current Status Req. Identifier Submission Identifier Historical Step Name Historical Status Name Submission Historical Status Start Date Current Step Name
    Sourcing 1234567 9999999 *Offer Candidate Declined 2-7-2018 3:32:40 PM Hire
    Sourcing 1234567 9999999 *Offer Accepted 2-5-2018 2:17:13 AM Hire