Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2444
  • Last Modified:

Powerbuilder Datawindow Group Problem

Thanks in advance for your help.

I am attempting to use groups to display data from 4 different tables and I am having trouble eliminating some rows and making other rows appear where I want them.

This is what I get now:

COMPANY X
                AGENCY A
                              VENDOR A
                              VENDOR B
                              VENDOR C
                AGENCY B
                              VENDOR A
                              VENDOR B
                              VENDOR C
COMPANY Y
                AGENCY C
                              VENDOR D
                              VENDOR E
                              VENDOR F
                AGENCY D
                              VENDOR D
                              VENDOR E
                              VENDOR F

Here is what I am looking for:

COMPANY X
                AGENCY A
                AGENCY B
                              VENDOR A
                              VENDOR B
                              VENDOR C
COMPANY Y
                AGENCY C
                AGENCY D
                              VENDOR D
                              VENDOR E
                              VENDOR F

So I really want the agencies grouped together and I want to get rid of the duplicate vendors shown.  I tried sorting the contents of the datawindow by COMPANY|AGENCY|VENDOR and then adding the following into the datawindow control filter:

dw_1.SetRedraw(false)
//Sort the rows based on the vendor name column
dw_1.SetSort("vendor_vendor_name A")
dw_1.Sort()
//Define the appropriate Filter
dw_1.SetFilter("isnull(vendor_vendor[-1]) OR vendor_vendor[-1] <> vendor_vendor")
dw_1.Filter()
//Finally turn back on the datawindow redraw
dw_1.SetRedraw(true)
 
0
pemurray
Asked:
pemurray
  • 5
  • 5
  • 3
  • +2
1 Solution
 
SylvainPouliotCommented:
Can you give some informations about your Table and Sql Statement you use...

0
 
pemurrayAuthor Commented:
Right.  Here is the sql.  Also, it occurs to me that the 'duplicates' are really not duplicates at all as the there are different rows for each instance of the agency and the vendor (or service_provider) as shown in the SQL.  I use the EFFORT table to tie together an implementation effort that includes one company, one or more agencies and one or more service providers.

SELECT DISTINCT effort.effort_id,  
         effort.e_priority,  
         effort.e_effort_type,  
         company.c_company,  
         company.c_estimated_yr_trans,  
         agency.a_agency_name,  
         agency.a_channel,  
         agency.a_forms_sent,  
         agency.a_forms_returned,  
         agency.a_script_created,  
         agency.a_agency_trained,  
         company.corporate_id,  
         company.effort_id,  
         agency.agency_id,  
         agency.effort_id,  
         service_provider.sp_company_name,  
         service_provider.sp_percent_of_acct,  
         service_provider.sp_backoffice,  
         service_provider.sp_on_saturn,  
         service_provider.sp_trained,  
         service_provider.sp_rate_load_method,  
         service_provider.sp_rates_received,  
         service_provider.sp_rates_loaded  
    FROM agency,  
         company,  
         effort,  
         service_provider  
   WHERE ( agency.effort_id = company.effort_id ) and  
         ( company.effort_id = effort.effort_id ) and  
         ( effort.effort_id = service_provider.effort_id )    

Thanks again!
0
 
SylvainPouliotCommented:
Which columns are the vendor?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
pemurrayAuthor Commented:
service_provider
0
 
SylvainPouliotCommented:
Hmm...a simple but not so cute solution would be to go with code to take out duplicate data...


You could also try to use an UNION in your SQL statement.
If at first you select your Company and Agency and NO reference to the Service_provider table
Then in your seconde SQL (the one after the UNION) you could go get Distinct Vendor
Add a column Detail Type to both SQL like:
'C' as details_type /*Company */ ...and
'V' as details_type /*Vendor */

In your datawindow you should be able to make the difference between Company/Agency and Vendor and use them in some computed columns.


0
 
berXpertCommented:
And in a case like this:

COMPANY X
               AGENCY A
                             VENDOR A
                             VENDOR B
                             VENDOR J
               AGENCY B
                             VENDOR A
                             VENDOR B
                             VENDOR K

What do you expect to see in your group?

I.e.

COMPANY X
               AGENCY A
               AGENCY B
                             VENDOR A
                             VENDOR B
               AGENCY A
                             VENDOR J
               AGENCY B
                             VENDOR K

Or cases like this will never happen?
0
 
buasuwanCommented:
I have a none-coding solution.

but in case of

VENDOR A
VENDOR B
...

are in detail band.


ok, modify your datawindow.
you have 2 groups?
the 1st group is grouped by corporate_id.
the 2nd group is grouped by agency_id.

and you have vendor name 'sp_company_name' is in detail band.

1.set visible expression of this 'sp_company_name' to

if ( count(  agency_id  for group 1 distinct  agency_id ) =  cumulativesum( 1 for 1 distinct   agency_id ) , 1 , 0)

if you have more columns in detail band, please set above expresion to all objects in there.

2.set Height of the detail band = 0
3.then enable Autosize Height to detail band.

4.view your report.

this should works.
0
 
Ariel GarciaJefe de SistemasCommented:
let me guest...

Are you trying to make a list of service_provider, and not repeat at all.

And you want to show a Company separate.

Whell dont include a "Agency X" or second nivel...

Or better that all... you need to a litte work ( Yes i refer to PROGAMAR) like the olds days...

I like to give a tips (work for me)

Make a Result Table, with external data...

Make your counts apart one by one and fill the result table, i solve a lot off problems in this way.

Maybe this help...
0
 
pemurrayAuthor Commented:
Buasuwan,

Your approach is elegant.  And it works!  Just one last issue on this...

I no longer see the 'extra rows', but there is still a phantom space for them between agency a and agency b.  I could not find a way to 'autosize' the band.

Am I missing something?

Thank you.
0
 
buasuwanCommented:
> I could not find a way to 'autosize' the band.

is that detail band?

changing datail band property to autoresize.

first, just click at the detail band,

if you use PB5,PB6, you need to use right mouse click to open popupmenu and then click menu item 'Properties'. now you will see popupwindow 'Band Object'.

if you use PB7,PB8. you will see the right panel properties of detail band.

second,
change these 2 values.
1.Height will be 0
2.select Autosize Height checkbox.

0
 
pemurrayAuthor Commented:
It worked fine for the detail band, but it is the agency group that has the blank spaces.

corp
            agency a




            agency b
                               vendor a
                               vendor b
                               vendor c
                               vendor d

Thank you!
0
 
buasuwanCommented:
sorry, where are these value? in detail band? or group band?

vendor a
vendor b
vendor c
vendor d

these should be in detail band.




0
 
buasuwanCommented:
if you have these values in group band.
vendor a
vendor b
vendor c
vendor d

you couldn't be able to set autosize height to group band.

sorry, does this your report look like this?

corp
           agency a


           agency b
                              vendor a
                                 record a1
                                 record a2
                              vendor b
                                 record b1
                                 record b2
                              vendor c
                                 record c1
                                 record c2
                                 record c3
                                 record c4
                              vendor d
                                 record d1
0
 
buasuwanCommented:
if yes, you have 3 groups? group1=corp, group2=agency and group3=vendor?

you should split your report to 2 reports. and change your
sqlsyntax of the old report to.

first report,
you will have only join 2 tables, corp and agency.
and you create 2 groups in this report.
corp
          agency a
          agency b

and the second report, I think you may use the same sqlsyntax as the old report. and add 2 retrieval arguments(corporate_id,    agency_id) in sql where.

in report,
remove group corp, agency. you will have only 1 group=vendor.

                             vendor a
                                record a1
                                record a2

now, you need to place the second report into the first report in detail band. this is called a nested report, and set retrieval argument to (corporate_id, agency_id).

and use the same trick that I told you before.

1.set visible expression of this nested report to

if ( count(  agency_id  for group 1 distinct  agency_id ) =  cumulativesum( 1 for 1 distinct   agency_id ) , 1 , 0)

2.set Height of the detail band = 0
3.then enable Autosize Height to detail band.
0
 
pemurrayAuthor Commented:
Perfect answer although I am still a bit boggled by how it works. I did have the vendor field in the wrong band.  When I set it moved it to the detailed band and reset autosize=true and height=0 it worked perfectly.

Thank you so much!!!!!!!!!!!!!!!!!!!!!!!!!!!

pemurray
0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

  • 5
  • 5
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now