Question about "Results" 1

[PPettit]

Is it possible to make Results generate a report that has columns of the same field but with different criteria?

For example:
I can get Results to generate data like this:

Warehouse |  Item  | Avg cost |
-------------------------------------
WH1       | 123456 |   123.45
WH2       | 123456 |   134.56
WH3       | 123456 |   112.34

But I want it to come out like this:

Item   | WH1 avg cost | WH2 avg cost | WH3 avg cost |...
----------------------------------------------------------------
123456 |       123.45 |       134.56 |       112.34 |...

If it can't be done in Results, I should be able to do this in Report Builder, right? I've never really done anything with that application before and it looks a bit complex. Are there any good books or online resources for information regarding Report Builder (or Results)?

 

[longhair]

PPettit,

not hard to do with progress at all. but i've never used th RB or results.
do you have access to the progress procedure editor?
if so:

for each item no-lock:
display item wh1 wh2 wh3.
end.

should get you started.
you can also take a look at

http://www.peg.com

regards,
longhair

 

[PPettit]

You'll have to pardon my ignorance. I know almost nothing about Progress. I'm just a computer guy that had to figure out how to pull some data out of our accounting system. Our software vendor is generally not that great at providing support for anything outside of their own application. I figured out the basics of Results mostly through trial and error.

I don't really understand what you're talking about. I generated a procedure from my Results query. I then went into the Procedure Editor and opened it up. This is what it shows:

DEFINE VARIABLE qbf-count    AS INTEGER NO-UNDO.
DEFINE VARIABLE qbf-governor AS INTEGER NO-UNDO.
 
DEFINE VARIABLE qbf-govcnt AS INTEGER NO-UNDO.
DEFINE VARIABLE qbf-loop   AS INTEGER NO-UNDO.
DEFINE VARIABLE qbf-time   AS INTEGER NO-UNDO.

DEFINE BUFFER warehouse FOR v2.warehouse.
DEFINE BUFFER wa_item FOR v2.wa_item.
DEFINE BUFFER item FOR v2.item.

ASSIGN
  qbf-count    = 0
  qbf-governor = 0
  qbf-time     = TIME.

OUTPUT TO TERMINAL PAGED.

DO FOR v2.warehouse,v2.wa_item,v2.item:
  FORM HEADER
    "Deviation Report" + "                                                                    " +  STRING(TODAY,"99/99/99") FORMAT "x(92)" SKIP(2) 
    WITH FRAME qbf-header PAGE-TOP COLUMN 1 WIDTH 112 NO-ATTR-SPACE 
    NO-VALIDATE NO-LABELS NO-BOX USE-TEXT STREAM-IO.
  VIEW FRAME qbf-header.

  FORM HEADER
    SKIP(2)
    "                                                                                " +  "Page: " + TRIM(STRING(PAGE-NUMBER,">>>>9")) FORMAT "x(92)" SKIP
    WITH FRAME qbf-footer PAGE-BOTTOM COLUMN 1 WIDTH 112 NO-ATTR-SPACE 
    NO-VALIDATE NO-LABELS NO-BOX USE-TEXT STREAM-IO.
  VIEW FRAME qbf-footer.

  main-loop:
  FOR EACH v2.warehouse NO-LOCK
    WHERE ((v2.warehouse.warehouse = "AN"   OR v2.warehouse.warehouse = "BS"   OR v2.warehouse.warehouse = "CB"   OR v2.warehouse.warehouse = "CR"   OR v2.warehouse.warehouse = "DC"   OR v2.warehouse.warehouse = "HB"   OR v2.warehouse.warehouse = "OD"   OR v2.warehouse.warehouse = "SD")),EACH v2.wa_item NO-LOCK
    OF v2.warehouse,EACH v2.item NO-LOCK
    OF v2.wa_item WHERE v2.item.it_acct_class = "03"  AND v2.item.is_deleted = FALSE  AND (v2.item.item >= "a" AND v2.item.item <= "vxxxxxxxxxx")
    BREAK BY v2.item.item
      BY v2.wa_item.warehouse:

    qbf-count  = qbf-count + 1.

    FORM
      v2.wa_item.item COLUMN-LABEL "Item!Number" FORMAT "x(10)"
      v2.item.descr[1] COLUMN-LABEL "Description[1]" FORMAT "x(30)"
      v2.item.descr[2] COLUMN-LABEL "Description[2]" FORMAT "x(30)"
      v2.warehouse.warehouse COLUMN-LABEL "Whse!Code" FORMAT "xx"
      v2.wa_item.avg_cost COLUMN-LABEL "Average!Cost" FORMAT "zzzzzzzz9.9999"
      WITH FRAME qbf-report-1 DOWN COLUMN 1 WIDTH 112
      NO-ATTR-SPACE NO-VALIDATE NO-BOX USE-TEXT STREAM-IO.

    DISPLAY
      v2.wa_item.item
      v2.item.descr[1]
      v2.item.descr[2]
      v2.warehouse.warehouse
      v2.wa_item.avg_cost
      WITH FRAME qbf-report-1.

    DOWN WITH FRAME qbf-report-1.
  END.

  PAGE.
END.

OUTPUT CLOSE.
RETURN.

How do I implement what you suggested? What do I do once I make the proper changes? I don't understand what I need to do in order to get the data into a file or a printed report.

The ultimate goal is to get this into an Excel spreadsheet so that our accounting people can plug in some numbers for whatever calculations they need to do.

 

[longhair]

PPettit,
was out of the office yesterday. what the rb did was make you a glorified report to the screen - most of which you don't need. you probably don't need to define any vars, and you don't need any forms.
i would try this:

output to \yourfolder\yourfilename.
  FOR EACH v2.warehouse NO-LOCK
    WHERE ((v2.warehouse.warehouse = "AN"   OR v2.warehouse.warehouse = "BS"   OR v2.warehouse.warehouse = "CB"   OR v2.warehouse.warehouse = "CR"   OR v2.warehouse.warehouse = "DC"   OR v2.warehouse.warehouse = "HB"   OR v2.warehouse.warehouse = "OD"   OR v2.warehouse.warehouse = "SD")),EACH v2.wa_item NO-LOCK
    OF v2.warehouse,EACH v2.item NO-LOCK
    OF v2.wa_item WHERE v2.item.it_acct_class = "03"  AND v2.item.is_deleted = FALSE  AND (v2.item.item >= "a" AND v2.item.item <= "vxxxxxxxxxx")
    BREAK BY v2.item.item
      BY v2.wa_item.warehouse:
   DISPLAY
      v2.wa_item.item
      v2.item.descr[1]
      v2.item.descr[2]
      v2.warehouse.warehouse
      v2.wa_item.avg_cost
      with width 240 no-box stream-io no-attr-space.
end.
output close.

post back with any questions.
hth
regards,
longhair

 

[PPettit]

Thanks for the response. What you've posted so far has been very enlightening.

I tried what you posted and got this:

Item                                                                                       Whse        Average
Number                       Description                    Description                    Code           Cost
---------------------------- ------------------------------ ------------------------------ ---- --------------
AD200                        O.L. RELAY (CH) MOUNTING       ADAPTER (FNA) #C306TB1         AN           3.4000
AD200                        O.L. RELAY (CH) MOUNTING       ADAPTER (FNA) #C306TB1         BS           3.4000
AD200                        O.L. RELAY (CH) MOUNTING       ADAPTER (FNA) #C306TB1         CB           4.2400
AD200                        O.L. RELAY (CH) MOUNTING       ADAPTER (FNA) #C306TB1         CR           3.4003
AD200                        O.L. RELAY (CH) MOUNTING       ADAPTER (FNA) #C306TB1         DC           4.7920
AD200                        O.L. RELAY (CH) MOUNTING       ADAPTER (FNA) #C306TB1         HB           4.2400
AD200                        O.L. RELAY (CH) MOUNTING       ADAPTER (FNA) #C306TB1         OD           4.2400
AD200                        O.L. RELAY (CH) MOUNTING       ADAPTER (FNA) #C306TB1         SD           3.4000
AD202                        O.L. RELAY (CH) MOUNTING       ADAPTER (KNA) #C316TB1         AN           9.7325
AD202                        O.L. RELAY (CH) MOUNTING       ADAPTER (KNA) #C316TB1         BS          10.3500
AD202                        O.L. RELAY (CH) MOUNTING       ADAPTER (KNA) #C316TB1         CB          10.7770
AD202                        O.L. RELAY (CH) MOUNTING       ADAPTER (KNA) #C316TB1         CR           9.1200
AD202                        O.L. RELAY (CH) MOUNTING       ADAPTER (KNA) #C316TB1         DC           0.0000
AD202                        O.L. RELAY (CH) MOUNTING       ADAPTER (KNA) #C316TB1         HB           4.1280
AD202                        O.L. RELAY (CH) MOUNTING       ADAPTER (KNA) #C316TB1         OD           9.0971
AD202                        O.L. RELAY (CH) MOUNTING       ADAPTER (KNA) #C316TB1         SD           9.0870
(etc.)

My goal is to get the output more like this:

Item                                                                             Average Cost
Number   Description                    Description                        AN         BS         CB   (etc.)           
-------- ------------------------------ ------------------------------ ---------- ---------- ---------- 
AD200    O.L. RELAY (CH) MOUNTING       ADAPTER (FNA) #C306TB1             3.4000     3.4000     4.2400
AD202    O.L. RELAY (CH) MOUNTING       ADAPTER (KNA) #C316TB1             9.7325    10.3500    10.7770

I'm still not quite sure how to work your first bit of code into the procedure in order to modify the display behavior.

Am I on the right track by assuming that I need to make the display code more like this:

DISPLAY
      v2.wa_item.item
      v2.item.descr[1]
      v2.item.descr[2]
      v2.warehouse.warehouse v2.wa_item.avg_cost
      v2.warehouse.warehouse v2.wa_item.avg_cost
      v2.warehouse.warehouse v2.wa_item.avg_cost
      (etc.)

If each display line represents a column, how do I make each warehouse column display only data from a specific warehouse?

 

[longhair]

PPettit,
now i'm starting to get a better picture of what you need.
you are going to have to create some vars, to hold the avg cost for each whse like such:

def var an_cost as deci format "-zzzz,zzz,zz9.99" label "AN COST".
def var bs_cost as deci format "-zzzz,zzz,zz9.99" label "BS COST".

then change your for each:

for each item no-lock:
for each wa_item where wa_item.item = item.item no-lock:
for each warehouse where warehouse.? = wa_item.? 
and (warehouse.warehouse = "AN"   OR warehouse.warehouse = "BS"   OR warehouse.warehouse = "CB"   OR warehouse.warehouse = "CR"   OR warehouse.warehouse = "DC"   OR warehouse.warehouse = "HB"   OR warehouse.warehouse = "OD"   OR warehouse.warehouse = "SD") no-lock:
if warehouse.warehouse = "AN" then an_cost = wa_item.avg_cost.
if warehouse.warehouse = "BS" then bs_cost = wa_item.avg_cost.
...
end.
end.

you will need to find the field that relates warehouse to wa_item.
then change your display:

display
      wa_item.item
      item.descr[1]
      item.descr[2]
      an_cost
      bs_cost
      with width 240 no-box stream-io no-attr-space.
end.

hth
regards,
longhair

 

[PPettit]

I'm not making much progress (no pun intended). I can't seem to figure out how to get the FOR EACH part to work correctly. I'm still experimenting, though.

Is there a way to interrupt the procedure while it's running? Since I can't get the filters working properly, the procedure appears to loop through every single item. This takes forever. The last time I tried to interrupt the procedure (by stopping the Progress process via Task Manager), I think I caused my database to crash. Is there some kind of keystroke sequence like CTRL+C or BREAK that will safely terminate the procedure?

 

[PPettit]

This is the code I have right now:

def var an_cost as deci format "-zzzz,zzz,zz9.99" label "AN COST".
def var bs_cost as deci format "-zzzz,zzz,zz9.99" label "BS COST".
def var cr_cost as deci format "-zzzz,zzz,zz9.99" label "CR COST".
def var od_cost as deci format "-zzzz,zzz,zz9.99" label "OD COST".
def var hb_cost as deci format "-zzzz,zzz,zz9.99" label "HB COST".
def var cb_cost as deci format "-zzzz,zzz,zz9.99" label "CB COST".
def var dc_cost as deci format "-zzzz,zzz,zz9.99" label "DC COST".
def var sd_cost as deci format "-zzzz,zzz,zz9.99" label "SD COST".

output to e:\v2\db\test.txt.    
    FOR EACH item no-lock:
    FOR EACH wa_item where wa_item.item = item.item
    and (v2.item.it_acct_class = "03"  AND v2.item.is_deleted = FALSE  AND (v2.item.item >= "a" AND v2.item.item <= "wxxxxxxxxxx")) no-lock:
    FOR EACH warehouse where warehouse.warehouse = wa_item.warehouse 
    and (warehouse.warehouse = "AN"   OR warehouse.warehouse = "BS"   OR warehouse.warehouse = "CB"   OR warehouse.warehouse = "CR"   OR warehouse.warehouse = "DC"   OR warehouse.warehouse = "HB"   OR warehouse.warehouse = "OD"   OR warehouse.warehouse = "SD") no-lock
    
    BREAK BY v2.item.item
    BY v2.wa_item.warehouse:

    if warehouse.warehouse = "AN" then an_cost = wa_item.avg_cost.
    if warehouse.warehouse = "BS" then bs_cost = wa_item.avg_cost.
    if warehouse.warehouse = "CR" then cr_cost = wa_item.avg_cost.
    if warehouse.warehouse = "OD" then od_cost = wa_item.avg_cost.
    if warehouse.warehouse = "HB" then hb_cost = wa_item.avg_cost.
    if warehouse.warehouse = "CB" then cb_cost = wa_item.avg_cost.
    if warehouse.warehouse = "DC" then dc_cost = wa_item.avg_cost.
    if warehouse.warehouse = "SD" then sd_cost = wa_item.avg_cost.


   DISPLAY
      v2.wa_item.item
      v2.item.descr[1]
      v2.item.descr[2]
      an_cost
      bs_cost
      cr_cost
      od_cost
      hb_cost
      cb_cost
      dc_cost
      sd_cost
      with width 240 no-box stream-io no-attr-space.
end.
end.
end.
output close.

I know I'm not doing something quite right but I can't figure out what it is. At least I got the filtering to work.

Each item is listed 8 times and the cost values are not correct under each warehouse. Do I need to modify the BREAK section somehow? Are the "end." statements where they need to be? Am I missing something else entirely?

 

[longhair]

PPettit,
getting closer. i'd be able to help more if i knew your db schema.
is your db and procedure editor on windose?
i work in a *nix environment and ctrl+c terminates code without causing any issues.
the reason that you are getting each item listed 8 times is because you are displaying in the warehouse 'for each' loop.
more your display to right before the 'end' of you item 'for each loop.
it also may be a good idea to reset your vars a the beginning of the item for each loop. so:

output to e:\v2\db\test.txt.    
    FOR EACH item no-lock:
    an_cost = 0.
    bs_cost = 0.

not sure that you need the break by statements at all.
your item table should be indexed by item, so you will get the items in table order. as far as the wa_item or warehouse doesn't matter that much how you gather their info since your report is really keyed on the 'item'.
to spped it up you may want to take a look at your db schema and make sure that any of your 'where' clauses in your 'for each' statements always use an indexed field first then non indexed fields. this will prevent a full table scan.
regards,
longhair

 

[PPettit]

The DB and the PE are running on Windows Server 2003.

Ctrl+Break is the one combination I must have overlooked. I tried it today and it does allow me to halt the procedure.

I modified my procedure to accommodate the changes you suggested. I must be missing something that you consider obvious or I'm misreading what you've posted because the procedure now outputs this:

** No warehouse record is available. (91)
** No warehouse record is available. (91)
** No warehouse record is available. (91)
...

This is how the code looks now:

def var an_cost as deci format "-zzzz,zzz,zz9.99" label "AN COST".
def var bs_cost as deci format "-zzzz,zzz,zz9.99" label "BS COST".
def var cr_cost as deci format "-zzzz,zzz,zz9.99" label "CR COST".
def var od_cost as deci format "-zzzz,zzz,zz9.99" label "OD COST".
def var hb_cost as deci format "-zzzz,zzz,zz9.99" label "HB COST".
def var cb_cost as deci format "-zzzz,zzz,zz9.99" label "CB COST".
def var dc_cost as deci format "-zzzz,zzz,zz9.99" label "DC COST".
def var sd_cost as deci format "-zzzz,zzz,zz9.99" label "SD COST".

output to e:\v2\db\test.txt.    
    FOR EACH item no-lock:
      an_cost = 0.
      bs_cost = 0.
      cr_cost = 0.
      od_cost = 0.
      hb_cost = 0.
      cb_cost = 0.
      dc_cost = 0.
      sd_cost = 0.

    FOR EACH wa_item where wa_item.item = item.item
    and (v2.item.it_acct_class = "03"  AND v2.item.is_deleted = FALSE  AND (v2.item.item >= "a" AND v2.item.item <= "wxxxxxxxxxx")) no-lock:
    FOR EACH warehouse where warehouse.warehouse = wa_item.warehouse 
    and (warehouse.warehouse = "AN"   OR warehouse.warehouse = "BS"   OR warehouse.warehouse = "CB"   OR warehouse.warehouse = "CR"   OR warehouse.warehouse = "DC"   OR warehouse.warehouse = "HB"   OR warehouse.warehouse = "OD"   OR warehouse.warehouse = "SD") no-lock:
    
    if warehouse.warehouse = "AN" then an_cost = wa_item.avg_cost.
    if warehouse.warehouse = "BS" then bs_cost = wa_item.avg_cost.
    if warehouse.warehouse = "CR" then cr_cost = wa_item.avg_cost.
    if warehouse.warehouse = "OD" then od_cost = wa_item.avg_cost.
    if warehouse.warehouse = "HB" then hb_cost = wa_item.avg_cost.
    if warehouse.warehouse = "CB" then cb_cost = wa_item.avg_cost.
    if warehouse.warehouse = "DC" then dc_cost = wa_item.avg_cost.
    if warehouse.warehouse = "SD" then sd_cost = wa_item.avg_cost.

end.
end.  

    DISPLAY
      v2.wa_item.item
      v2.item.descr[1]
      v2.item.descr[2]
      an_cost
      bs_cost
      cr_cost
      od_cost
      hb_cost
      cb_cost
      dc_cost
      sd_cost
      with width 240 no-box stream-io no-attr-space.
end.
output close.

How do I determine what's indexed? So far, I haven't been able to figure this out. Remember, I'm not a database person. I've never really done anything with any of the Progress utilities (except Results) until I started working on this issue.

 

[PPettit]

Hmmm...

I swapped item and wa_item in the FOR EACH lines and now each item is listed once and the values appear to be correct under each column. However, the procedure now appears to loop through the entire list of items as many times as there are warehouses and then puts the values in just one of the columns each time.

First listing shows only the AN values under the AN column.
Second listing shows only the BS values under the BS column.
...

 

[longhair]

PPettit,
for how you want your report you can't swap item and wa_item.
what you can do is use the if available statement try this:

def var an_cost as deci format "-zzzz,zzz,zz9.99" label "AN COST".
def var bs_cost as deci format "-zzzz,zzz,zz9.99" label "BS COST".
def var cr_cost as deci format "-zzzz,zzz,zz9.99" label "CR COST".
def var od_cost as deci format "-zzzz,zzz,zz9.99" label "OD COST".
def var hb_cost as deci format "-zzzz,zzz,zz9.99" label "HB COST".
def var cb_cost as deci format "-zzzz,zzz,zz9.99" label "CB COST".
def var dc_cost as deci format "-zzzz,zzz,zz9.99" label "DC COST".
def var sd_cost as deci format "-zzzz,zzz,zz9.99" label "SD COST".

output to e:\v2\db\test.txt.    
    FOR EACH item no-lock:
      an_cost = 0.
      bs_cost = 0.
      cr_cost = 0.
      od_cost = 0.
      hb_cost = 0.
      cb_cost = 0.
      dc_cost = 0.
      sd_cost = 0.

    FOR EACH wa_item where wa_item.item = item.item
    and (v2.item.it_acct_class = "03"  AND v2.item.is_deleted = FALSE  AND (v2.item.item >= "a" AND v2.item.item <= "wxxxxxxxxxx")) no-lock:
    if available wa_item then do:
    FOR EACH warehouse where warehouse.warehouse = wa_item.warehouse
    and (warehouse.warehouse = "AN"   OR warehouse.warehouse = "BS"   OR warehouse.warehouse = "CB"   OR warehouse.warehouse = "CR"   OR warehouse.warehouse = "DC"   OR warehouse.warehouse = "HB"   OR warehouse.warehouse = "OD"   OR warehouse.warehouse = "SD") no-lock:
    if available warehouse then do:
    if warehouse.warehouse = "AN" then an_cost = wa_item.avg_cost.
    if warehouse.warehouse = "BS" then bs_cost = wa_item.avg_cost.
    if warehouse.warehouse = "CR" then cr_cost = wa_item.avg_cost.
    if warehouse.warehouse = "OD" then od_cost = wa_item.avg_cost.
    if warehouse.warehouse = "HB" then hb_cost = wa_item.avg_cost.
    if warehouse.warehouse = "CB" then cb_cost = wa_item.avg_cost.
    if warehouse.warehouse = "DC" then dc_cost = wa_item.avg_cost.
    if warehouse.warehouse = "SD" then sd_cost = wa_item.avg_cost.

end.
end.  
end.
end.

    DISPLAY
      v2.wa_item.item
      v2.item.descr[1]
      v2.item.descr[2]
      an_cost
      bs_cost
      cr_cost
      od_cost
      hb_cost
      cb_cost
      dc_cost
      sd_cost
      with width 240 no-box stream-io no-attr-space.
end.
output close.

regards,
longhair

 

[PPettit]

This is the closest I've gotten so far. I sure do appreciate all of your help.

Once I changed this:

DISPLAY
      v2.wa_item.item

To this:

DISPLAY
      v2.item.item

It started working pretty much like I wanted. However, it's not filtering out the items I don't need, but I'm pretty sure I can take care of that without too much trouble.

Thanks again for all of your assistance on this issue.

 

[longhair]

PPettit,
define not filtering items i don't need. are you referring to your where clause on the wa_item for each?
if so move (v2.item.it_acct_class = "03" AND v2.item.is_deleted = FALSE AND (v2.item.item >= "a" AND v2.item.item <= "wxxxxxxxxxx")) to be part of a where clause for the item for each.
hth
regards,
longhair

 

[PPettit]

I did move the criteria to the item FOR EACH. That took care of most of the problem. What still puzzles me is that if "AND item.is_deleted = FALSE" is present, the item numbers are listed in random order instead of alphabetic order by item number.

The corresponding cost values in each column appear to be correct for each item. It's just that the items are no longer in the correct order.

 

[longhair]

PPettit,
my gues would be that the field is indexed. progress is using this index prior to other ones. what you could do is take your exported file, import into excel, highlight all of the columns that have data in them and then sort on the item column, which should be column a.
regards,
longhair

 

[PPettit]

I realize I could do my sorting in Excel. This kinda defeats one of the main purposes of this procedure though. I wanted the output to be as correct as possible before I imported the data into a spreadsheet.

This may still work out, however. I was probably going to do some automation within the spreadsheet anyway. It shouldn't be too much trouble to sort the data along with the other tasks I had in mind.

While I'm thinking about it...
Where can I find some good documentation on writing procedures? Obviously, I need to know more about stuff like syntax/command structure. My understanding is that Progress is either based on or somehow associated with the 4GL language. Is that the language used when writing procedures?

 

[longhair]

PPettit,
progress is a 4gl (fourth generation language). do you have any programming experience? check out

http://psdn.progress.com/library/modern_progress/index.ssp

click on OpenEdge Development.
regards,
longhair

 

[PPettit]

Thanks for the link. I scanned through the documents and it looks like I should be able to find some useful information to get me started.

My background is more hardware/networking. I'm pretty much just a VBScript person for now, but it looks like this job is going to require more coding experience from me in the future.