?
Solved

Powerbuilder Datawindow Group Problem

Posted on 2003-03-30
15
Medium Priority
?
2,407 Views
Last Modified: 2013-12-26
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
Comment
Question by:pemurray
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
  • 3
  • +2
15 Comments
 
LVL 2

Expert Comment

by:SylvainPouliot
ID: 8234449
Can you give some informations about your Table and Sql Statement you use...

0
 

Author Comment

by:pemurray
ID: 8234474
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
 
LVL 2

Expert Comment

by:SylvainPouliot
ID: 8235238
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!

 

Author Comment

by:pemurray
ID: 8238475
service_provider
0
 
LVL 2

Expert Comment

by:SylvainPouliot
ID: 8238564
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
 
LVL 5

Expert Comment

by:berXpert
ID: 8246785
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
 
LVL 5

Expert Comment

by:buasuwan
ID: 8247567
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
 
LVL 2

Expert Comment

by:Ariel Garcia
ID: 8331495
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
 

Author Comment

by:pemurray
ID: 8333113
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
 
LVL 5

Expert Comment

by:buasuwan
ID: 8335052
> 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
 

Author Comment

by:pemurray
ID: 8335496
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
 
LVL 5

Accepted Solution

by:
buasuwan earned 1000 total points
ID: 8339812
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
 
LVL 5

Expert Comment

by:buasuwan
ID: 8341853
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
 
LVL 5

Expert Comment

by:buasuwan
ID: 8341980
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
 

Author Comment

by:pemurray
ID: 8344282
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Jaspersoft Studio is a plugin for Eclipse that lets you create reports from a datasource.  In this article, we'll go over creating a report from a default template and setting up a datasource that connects to your database.
How to install Selenium IDE and loops for quick automated testing. Get Selenium IDE from http://seleniumhq.org Go to that link and select download selenium in the right hand column That will then direct you to their download page. From that p…
This tutorial covers a step-by-step guide to install VisualVM launcher in eclipse.
THe viewer will learn how to use NetBeans IDE 8.0 for Windows to perform CRUD operations on a MySql database.
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question