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; } } }

Sorting by column within a Group

ERadk

if this posts twice, I apologize.

Hi all! I have a BIRT question that has had me stumped for a couple of days now. I'll try and describe the scenario as best I can.

I have my SQL which collects a listing of events and attendees. Every event has multiple attendees, each attendee has a different denotation or title, example; attendee 1 = Captain, attendee 2 = Private.

When I run my SQL I get all the events listed and all the attendees along with their titles for the event. The report needs to display the event once and the person how is denoted with a specific title, like Captain. If there is no one denoted as Captain, the event must still be listed. Here are two example rows.

Group Header Row: Date Location EventType EventSubtype
Detail Row: Attendee Name (if this attendee is denoted as Captain)

Because every event can have multiple attendees, the attendees are listed in a bridge table consisting of the primary key for the attendee and the event. My SQL returns all attendees and it will list an event multiple times. I put a group in BIRT on the event primary key and the attendees are in the detail row that will only be visible if the attendee is denoted as a Captain. The report now has to be sorted by the first column in the header, Date, ascending.

My question; I have the group forming based off of the primary key and each group will only have a detail row based on how many people are denoted as captain. How can I sort all the entries for a group based off of the date column in the group header row? If I have 30 entries for the event group, they need to be ascending based off of the date. I seriously appreciate any help that can be given and if you read this far, thanks!

Find more posts tagged with

Comments

Tubal

I don't think I understand your question exactly, or if being a "captain" is relevant?

But you can sort within a group by editing the group properties.

The group window has an option for sorting.

ERadk

Thanks for the reply. I know of the group sorting, but sorting within the group only works rows inside the group, not the group itself. The whole bit about Captain is that I have a one to many relationship. The attendees all have an attribute that is rank, Captain, Lieutenant, Private, etc. There is usually only one captain in as an attendee, but sometimes there are more, sometimes there are zero.

Therefore, I cannot filter the event and only get captains because some events do not have any captain-denoted attendees or it has many. Because of this my SQL will have an event listed in many rows with a different attendee in each one. So I made the event a group. Now I need to get these event group to sort ascending. Here's a pic. I have the group based on the primary key for the event. The detail only only staff name if that staff member is denoted as a captain. I need to sort in an ascending order off of keydate1. Every row output of the group will only have one keydate1.

ERadk

Picture didn't post, sorry. I've attached several times, but I don't see it in the preview post view.

Tubal

I'm still not quite understanding your grouping. 
 
So you're saying you'd have something like: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>Event (sorted by date)
Attendee (only show if rank = Captain)</pre>
 
If this is how you want it, I would do the following: 
 
1. Group on Event, sorted by date. 
2. Set up your detail however you want it. 
3. Select your detail row so it's highlighted. 
4. Set the visibility on your detail row to hide if Rank != Captain. 
 
Your visibility script would be something along the lines of 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>dataSetRow["myRank"] != "Captain"</pre>
 
This should show ever group header, and only show a detail if the rank = "Captain".