Schultz’s PowerBuilder Notes

Groups and Grouping


How do I dynamically change a group property?

ll_color = RGB(200, 200, 500)

dw_1.Modify(“DataWindow.Header.2.Color=” + String(ll_color))

dw_1.Modify(“DataWindow.Trailer.2.Height=500”)

How do I expand hidden rows in a group?

In this example, a DataWindow hides the detail rows and shows only the group summaries and headers. If a checkbox “Explode Details” is clicked, the following script is fired:

integer  li_height

if this.Checked = TRUE then
   li_height = 64
else
   li_height = 0
end if

dw_report.Object.DataWindow.Detail.Height = li_height

How do I get the value of the items in a group trailer or header?

The clicked event gives the following arguments: x, y, row, and dwo. Because clicking on a trailer returns a row of 0, you can not directly do a GetItem(). Instead, after you interrogate the dwo to determine that you are in the trailer, use the function GetBandAtPointer(). Here are the results:

Band

Location of pointer

Associated row

detail

In the body of the DataWindow object

The row at the pointer. If rows do not fill the body of the DataWindow object because of a group with a page break, then the first row of the next group. If the body isn’t filled because there are no more rows, then the last row

header

In the header of the DataWindow object

The first row visible in the DataWindow body

header.n

In the header of group level n

The first row of the group

trailer.n

In the trailer of group level n

The last row of the group

footer

In the footer of the DataWindow object

The last row visible in the DataWindow body

summary

In the summary of the DataWindow object

The last row before the summary

Returns a string that names the band in which the pointer is located, followed by a tab character and the number of the row associated with the band (see the table in Usage). Returns the empty string (“”) if an error occurs.

If you have the last row in a group, and you need to find the first row in the group, cycle through the rows with FindGroupChange().

What is FindGroupChange(long, integer)

long dwcontrol.FindGroupChange ( long row, integer level )

The first argument is the row you wish to start your search (use 0 to start at the very beginning). The second argument is the group number. The return value is the row where the group changes.

Here is an example from PB Help:

This statement searches for the first break in group 2 in dw_regions. The search begins in row 5:

dw_regions.FindGroupChange(5, 2)

Example of finding the range of rows for a group

If the user clicks on a group trailer band, the following script will call the function to calculate which rows that group begins and ends, and then expands them. Note: the detail band begins with a height of zero and a calculated column called EXPAND_LEVEL with the value of empty string. This column is hidden behind another column and has its autosize attribute set to TRUE. EXPAND_LEVEL begins with a height of zero.

/////////////////////////////////////////////////////////////////////////
// Event: dw_Report:Clicked
//
/////////////////////////////////////////////////////////////////////////////
//   Revision History
//
// Date      By   Description/Comments
// --------  ---  -------------------------------------------
// 12/20/02  fjs  Initial Version
//
//////////////////////////////////////////////////////////////////////////////

boolean  lb_continue = true
double   ld_indirect_hrs
integer  li_band, li_pos_grp, li_pos_tab, li_group
long     ll_range_begin, ll_range_end, ll_last_row_in_group, ll_row
string   ls_band, ls_group, ls_last_row_in_group
w_frame  lw_frame

ls_band = this.GetBandAtPointer()  //trailer.1[tab]5  

if Mid(ls_band, 1, 7) <> "trailer" then
   lb_continue = false
end if

if lb_continue then
   li_pos_grp = Pos(ls_band, ".")
   if li_pos_grp > 1 then
      li_pos_grp ++
   else
      lb_continue = false
   end if
end if

if lb_continue then
   li_pos_tab = Pos(ls_band, "~t", li_pos_grp)  // ~t = [tab]
   if  li_pos_tab < 1 then
       lb_continue = false
   end if
end if

if lb_continue then
   ls_group = Trim(Mid(ls_band,li_pos_grp, li_pos_tab - li_pos_grp))
   if IsNumber(ls_group) then
      li_group = Integer(ls_group)
   else
      lb_continue = false
   end if
end if  

if lb_continue then
   ls_last_row_in_group = Trim(Mid(ls_band, li_pos_tab + 1))
   if IsNumber(ls_last_row_in_group) then
      ll_last_row_in_group = Integer(ls_last_row_in_group)
   else
      lb_continue = false
   end if
end if

if lb_continue then
   if wf_GetGroupRange(ll_range_begin, ll_range_end, ll_last_row_in_group, li_group) <> SUCCESS then
      lb_continue = false
   end if
end if

if lb_continue then
   lw_frame = gnv_app.of_GetFrame()
   lw_frame.SetMicroHelp("Expanding Group...")
   this.SetRedraw(False)
   SetPointer(HourGlass!)
   for ll_row = ll_range_begin to ll_range_end
      ld_indirect_hrs = this.GetItemNumber(ll_row, "indirect_hrs")
      if ld_indirect_hrs > 0 then
         dw_report.SetItem(ll_row, "EXPAND_LEVEL", "Y")
      end if
   next
   this.SetRedraw(True)
   lw_frame.SetMicroHelp("Ready")
end if

This is called from the DataWindow clicked event if the user clicks on a group trailer:

////////////////////////////////////////////////////////////////////////
//
//   Function: wf_GetGroupRange
//
//   Access: Protected
//
//   Description:
//
//   Arguments: long    al_group_begin        ref
//            long    al_group_end          ref
//            long    al_last_row_in_group  ref
//            integer ai_group
//
//   Returns:   integer
//
//////////////////////////////////////////////////////////////////////////////
//   Revision History
//
// Date      By   Description/Comments
// --------  ---  -------------------------------------------
// 12/18/02  fjs  Initial Version
//
//////////////////////////////////////////////////////////////////////////////

integer li_rc= SUCCESS, li_indx
long ll_grp_end[], ll_next_grp

al_group_begin = 0
al_group_end   = 0

if li_rc = SUCCESS then
   if dw_report.RowCount() < 1 then
      li_rc = NO_ACTION
   end if
end if

if li_rc = SUCCESS then
   if al_last_row_in_group > dw_report.RowCount() then
      li_rc = FAILURE
   end if
end if

if li_rc = SUCCESS then
   ll_next_grp = 1
   do while ll_next_grp < al_last_row_in_group
      li_indx ++
      ll_next_grp = dw_report.FindGroupChange(ll_next_grp + 1, ai_group)
      if ll_next_grp > 0 then
         ll_grp_end[li_indx] = ll_next_grp - 1
      else
         ll_grp_end[li_indx] = dw_report.RowCount()
         ll_next_grp         = dw_report.RowCount()
      end if
   loop
   al_group_end = ll_grp_end[li_indx]
   if li_indx = 1 then
      al_group_begin = 1
   else
      al_group_begin = ll_grp_end[li_indx - 1] + 1
   end if
end if

Return li_rc

Looping through a group

ll_count = dw_rpt.RowCount()
ll_first_row_in_group = 1
ll_last_row_in_group = 0
li_sum_row = 0

do while ll_last_row_in_group < ll_count
   ll_next_group = dw_rpt.FindGroupChange(ll_last_row_in_group + 2, 1) // row, group
   if ll_next_group = 0 then
      ll_next_group = ll_count
      ll_last_row_in_group = ll_count
   else
      ll_last_row_in_group = ll_next_group - 1
   end if
   li_group_cnt = 0
   ld_shed_hrs = 0
   ll_cutoffs  = 0
   ls_week     = dw_rpt.GetItemString(dw_rpt.FindGroupChange(ll_first_row_in_group, 1), "c_week")
   ls_loc      = dw_rpt.GetItemString(dw_rpt.FindGroupChange(ll_first_row_in_group, 1), "location_name")
   ls_res_grp  = dw_rpt.GetItemString(dw_rpt.FindGroupChange(ll_first_row_in_group, 1), "v_resrc_nm")
   for ll_row = ll_first_row_in_group to ll_last_row_in_group
      ld_shed_hrs += dw_rpt.GetItemNumber(ll_row, "util_hrs")
      ll_cutoffs  += dw_rpt.GetItemNumber(ll_row, "ctff_qty")
      li_group_cnt ++
   next
   li_sum_row ++
   dw_util.InsertRow(0)
   dw_util.SetItem(li_sum_row, "week",           ls_week)
   dw_util.SetItem(li_sum_row, "location",       ls_loc)
   dw_util.SetItem(li_sum_row, "resource_group", ls_res_grp)
   dw_util.SetItem(li_sum_row, "sched_hrs",      ld_shed_hrs)
   dw_util.SetItem(li_sum_row, "cutoffs",        ll_cutoffs)
   if ld_shed_hrs > 0 then
      dw_util.SetItem(li_sum_row, "rate", ll_cutoffs/ld_shed_hrs)
   end if
   dw_util.SetItem(li_sum_row, "util_pct", (ld_shed_hrs/24)/li_group_cnt)
   ll_first_row_in_group = ll_last_row_in_group  + 1
loop
dw_util.SaveAs("", Excel5!, TRUE)

My Groups seem to be working fine, except they are not sorted.

This was a killer problem I faced in Dec ’99 at QG. I wanted to have my groups appear in YEAR, Month order, but they were sometimes jumbled. My SQL needed an order by clause. Note, when you create or edit a group (Rows>Create Group) there is a sort by tab. Beware: when you drag the columns you want to sort by, you may actually drop the SUM(<col> FOR GROUP 1).

Advertisements

May 25, 2008 - Posted by | Datawindow PowerScript, Presentation Styles | , , , , , , , , , , , , , , , , ,

4 Comments »

  1. Hello,

    Thanks for helping this site

    Comment by Chandresh Mesvaniya | June 25, 2008 | Reply

  2. Hi,
    I have problem I want To haw create Group by In datawindow But Code script !!!!!!!

    Comment by Eng.mmohsen | March 15, 2009 | Reply

  3. Hi,

    How can I manually populate the details when I create a group data window?

    Here’s a sample table:

    DepartmentName1

    Name Address Age2
    Name Address Age2

    DepartmentName2

    Name Address Age2
    Name Address Age2

    DepartmentName3

    Name Address Age3
    Name Address Age3

    Thanks

    Comment by nicegurl | April 2, 2009 | Reply

  4. Hi,

    How can I change the property of the border for each row?

    for example I want the border of the first row to none and the second row to border.

    thanks.

    Comment by nicegurl | April 16, 2009 | Reply


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: