nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Oracle Communities

Oracle Analytics and AI

Child Item

supressing a row in OBIEE 11

user1106547

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

Find more posts tagged with

Accepted answers

All comments

Gianni Ceresa

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

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

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

Make your data feed:

DISTINCT name appt date appt key

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

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

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

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*)