Oracle Analytics Cloud and Server

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Analytics Cloud and Server› Oracle Analytics Cloud and Server

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

supressing a row in OBIEE 11

Received Response
31
Views
7
Comments
user1106547
user1106547 Rank 2 - Community Beginner
edited Aug 13, 2024 6:00PM in Oracle Analytics Cloud and Server

hi have a simple report that shows patient appointments.

some patients have more than one appt on any given day.

the analysis gets extracted to SMS provider so we send duplicate messages if patient has more than one appt on same day.

name    appt date      appt time   appt key
smith     1/10/17          09:30          1234

jones     1/10/17         10:00          1235

jones     1/10/17          10:30          1235

so in example above just what to show jones once - suppress or exclude the bottom row...

any ideas?

thanks  

Answers

  • Gianni Ceresa
    Gianni Ceresa Mod

    Hi,

    To exclude a row you must be able to identify that row and filter it ...

    A possible way would be numbering rows by "name" (or "appt key" depending on your business logic) and only keep row number = 1, so all the others will be filtered.

  • Syedsalmancs110
    Syedsalmancs110 Rank 6 - Analytics Lead

    To suppress the value you can give below method a try:

    Keep the column "appt time" at the last in Criteria and Result as this is the only column with different value between two rows and make sure for rest of the columns(name ,appt date  and appt key) properties within "Column Properties"--->"Column Format"-->"Value Suppression" is set to "Suppress"(its the default value anyways until modified).

  • Gianni Ceresa
    Gianni Ceresa Mod

    Plan B similar to first option about row numbering: add an aggregation on your "appt time". Something like MAX or MIN depending on the one matching your logic the best. This will return you a single row and not 2 anymore.

  • Thomas Dodds
    Thomas Dodds Rank 8 - Analytics Strategist

    Make your data feed:

    DISTINCT  name    appt date      appt key

    OR

    name   appt date   appt time   appt key     rank() over (partition by appt key order by appt time) as rnk

    WHERE rnk = 1

    Using OBIEE as a data pump is sub-optimal ... there are much better ways to do this.

  • user1106547
    user1106547 Rank 2 - Community Beginner

    thanks for all the suggestions - got it to work using formula below then filtering for first rows only

    rcount( "Appointment (details)"."Pre-op date/time" by "Patient"."Medical record number" )

  • Gianni Ceresa
    Gianni Ceresa Mod

    Good it works for you, as it has been suggested as solution ...

    Can you please close the thread? This question is Not Answered.

  • Sravan Gereddy
    Sravan Gereddy Rank 3 - Community Apprentice

    Based on the sample data above, I can suggest you to use MIN or MAX functions for appt time column grouping other columns.

    Syntax : MIN[or MAX](appt time BY *all other columns*)