DW Retrive options

I have a report, Dw with 3 groups
In the Trailer for group 3 I have a Grand Total and a Below Flag that gets printed only on the conditions specified.

How can I call that DW and only print the records where the conditions are true and flag is printed.
Let me know if you need more explanations.
Thanks a lot for your help, everyone.
Namik48075Asked:
Who is Participating?
 
tr1l0b1tCommented:
Hi Namik,

you could place a checkbox on the window :

    |X|  Show only Below Minimum

then apply the filter or not in its 'clicked' event...

If this.checked Then
   // apply the filter
    dw_1.SetFilter("min_level  > grand_total and on_order_qty = 0 and on_request_qty = 0")
    dw_1.Filter()
Else
   // Eliminate the filter
    dw_1.SetFilter("")
    dw_1.Filter()
End If

As you have several groups, I recommend you to call sort() and groupcalc() methods
so the entire code becomes :

dw_1.SetRedraw(FALSE)
If this.checked Then
   // apply the filter
    dw_1.SetFilter("min_level  > grand_total and on_order_qty = 0 and on_request_qty = 0")
    dw_1.Filter()
Else
   // Eliminate the filter
    dw_1.SetFilter("")
    dw_1.Filter()
End If
// Sort and recalculate
dw_1.Sort()
dw_1.GroupCalc()
dw_1.SetRedraw(TRUE)

Hope it helped
Regards
0
 
tr1l0b1tCommented:
Hi Namik,

You can avoid those records either specifying the conditions into the
WHERE statement of dw's SQL source or applying a filter to the dw :

 - in dw painter : Rows > Filter
 - in script ...
      dw_1.SetFilter("<filter expression>")
      dw_1.Filter()

Alternatively, if you only want to hide some columns, in dw painter
add an expression to the "Visible" property for each colum.

Regards
0
 
berXpertCommented:
Hi Namik,

You can copy your rows to other DW and print from the second DW.  2nd DW has the same dataobject, but you do not need to populate it with a  Retrieve.

// First find those rows you want to print
// for example, getRow() is the selected row
// and you are going to copy 10 rows

dw_1.RowsCopy( dw_1.GetRow(), 10, Primary!, dw_2, 1, Primary!)

// Make DW sort and group, next print
dw_2.GroupCalc()
dw_2.Print()


Regards,

BerX
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
Namik48075Author Commented:
Tried Rows > Filter and put the same condition as for the flag , doesn't give me any row
Thenk tried Rows> Filter and put the isnull( belowminimum) in the condition
Nothing worked.

Thanks
0
 
tr1l0b1tCommented:
Namik,
Could you please post here the conditions or expression you're using for the flag ?
0
 
Namik48075Author Commented:
Here is DW Structue:

Header
Header Group owner : Owner
Header Group Location_oid: location_id, location_description
Header Group oid:  item_type, id, item_master_description, min_level, max_level, curr_level, on_order_qty, on_request_qty

Detail : Alternate_id, Alternate_curr_level
Trailer Group Oid: sum( alternate_curr_level for group 3) + curr_level,  
belowminimumflag

Belowminimumflag = if ( (min_level  >  grand_total and  on_order_qty =0 and  on_request_qty =0), "BELOW MINIMUM", "")

Here is the SQL for this DW:
SELECT Inventory_level.oid,
      Inventory_level.location_oid,
      Inventory_level.item_oid,
      Inventory_level.curr_level,
      Alternate_item.alternate_item_oid,
      Inventory_level.min_level,
      Inventory_level.max_level,
      Inventory_level.reserved_qty,
      Inventory_level.Annual_Usage,
      Inventory_level.Quarterly_Usage,
      Inventory_level.Monthly_Usage,
      Inventory_level.On_Order_Qty,
      Inventory_level.On_Request_Qty,
      Inventory_level.Purch_Lead_Time_Days,
      Inventory_level.Calculation_Date,
      Inventory_level.Recommended_Min_Level,
      Inventory_level.Recommended_Max_Level,
      Item_master.id,
      "item_master_oid"=[item_master].[oid],
      "item_master_description"=[Item_master].[description],
      Location_master.location_id,
      "location_description"=[Location_master].[description],
      'item_type'=Left([code].[id],1),
      "alternate_curr_level"=Inventory_level_1.curr_level,
      "alternate_min_level"=Inventory_level_1.min_level,
      "alternate_location"=Inventory_level_1.location_oid,
      "alternate_oid"=Inventory_level_1.item_oid,
      "owner"=[Generic_company].[Description],
      "alternate_id"=Item_master_1.id
FROM (((((((Inventory_level INNER JOIN Location_master ON Inventory_level.location_oid = Location_master.oid) INNER JOIN Item_master ON Inventory_level.item_oid = Item_master.oid) INNER JOIN Code ON Item_master.type_oid = Code.Oid) LEFT JOIN Alternate_item ON Inventory_level.item_oid = Alternate_item.item_oid) LEFT JOIN Inventory_level AS Inventory_level_1 ON Alternate_item.alternate_item_oid = Inventory_level_1.item_oid) LEFT JOIN Location_master AS Location_master_1 ON Inventory_level_1.location_oid = Location_master_1.oid) LEFT JOIN Generic_company ON Inventory_level.owner_oid = Generic_company.oid) LEFT JOIN Item_master AS Item_master_1 ON Alternate_item.alternate_item_oid = Item_master_1.oid
WHERE
 Inventory_level.curr_level<=[inventory_level].[min_level] AND
isnull(Inventory_level.min_level,0) > 0
and (Inventory_level_1.location_oid=[Inventory_level].[Location_oid] Or (Inventory_level_1.location_oid) Is Null)
union

SELECT Inventory_level.oid,
      Inventory_level.location_oid,
      Inventory_level.item_oid,
      Inventory_level.curr_level,
      Alternate_item.alternate_item_oid,
      Inventory_level.min_level,
      Inventory_level.max_level,
      Inventory_level.reserved_qty,
      Inventory_level.Annual_Usage,
      Inventory_level.Quarterly_Usage,
      Inventory_level.Monthly_Usage,
      Inventory_level.On_Order_Qty,
      Inventory_level.On_Request_Qty,
      Inventory_level.Purch_Lead_Time_Days,
      Inventory_level.Calculation_Date,
      Inventory_level.Recommended_Min_Level,
      Inventory_level.Recommended_Max_Level,
      Item_master.id,
      "item_master_oid"=[item_master].[oid],
      "item_master_description"=[Item_master].[description],
      Location_master.location_id,
      "location_description"=[Location_master].[description],
      'item_type'=Left([code].[id],1),
      "alternate_curr_level"=0,
      "alternate_min_level"=0,
      "alternate_location"="",
      "alternate_oid"=0,
      "owner"=[Generic_company].[Description],
      "alternate_id"=""
FROM (((((((Inventory_level INNER JOIN Location_master ON Inventory_level.location_oid = Location_master.oid) INNER JOIN Item_master ON Inventory_level.item_oid = Item_master.oid) INNER JOIN Code ON Item_master.type_oid = Code.Oid) LEFT JOIN Alternate_item ON Inventory_level.item_oid = Alternate_item.item_oid) LEFT JOIN Inventory_level AS Inventory_level_1 ON Alternate_item.alternate_item_oid = Inventory_level_1.item_oid) LEFT JOIN Location_master AS Location_master_1 ON Inventory_level_1.location_oid = Location_master_1.oid) LEFT JOIN Generic_company ON Inventory_level.owner_oid = Generic_company.oid) LEFT JOIN Item_master AS Item_master_1 ON Alternate_item.alternate_item_oid = Item_master_1.oid
WHERE
 Inventory_level.curr_level<=[inventory_level].[min_level] AND
isnull(Inventory_level.min_level,0) > 0

and Inventory_level.item_oid not in
(SELECT Inventory_level.item_oid
FROM (((((((Inventory_level INNER JOIN Location_master ON Inventory_level.location_oid = Location_master.oid) INNER JOIN Item_master ON Inventory_level.item_oid = Item_master.oid) INNER JOIN Code ON Item_master.type_oid = Code.Oid) LEFT JOIN Alternate_item ON Inventory_level.item_oid = Alternate_item.item_oid) LEFT JOIN Inventory_level AS Inventory_level_1 ON Alternate_item.alternate_item_oid = Inventory_level_1.item_oid) LEFT JOIN Location_master AS Location_master_1 ON Inventory_level_1.location_oid = Location_master_1.oid) LEFT JOIN Generic_company ON Inventory_level.owner_oid = Generic_company.oid) LEFT JOIN Item_master AS Item_master_1 ON Alternate_item.alternate_item_oid = Item_master_1.oid
WHERE
Inventory_level.curr_level<=[inventory_level].[min_level] AND
isnull(Inventory_level.min_level,0) > 0
AND (Inventory_level_1.location_oid=[Inventory_level].[Location_oid] Or (Inventory_level_1.location_oid) Is Null  )
)


Sorry , its so long,

May be I should do the sum if Alternative in the Query then I will not need the grouping.
Let me know what you think, Thanks a lot everyone.
0
 
Namik48075Author Commented:
I am still struggiling with this one.
Probably the option with printing to another Dw would be the most sufficient for me, since I need both possibilities:
the user would choose either to print all the records or just the records with the Belowminimum flag displayed.

I need more help with sytax and writing the code for that.
I am sorry, but I've been using PowerBuilder only for few months and don't know all details yet.
I appreciate any help here.

Thanks alot
0
 
tr1l0b1tCommented:
Namik,

The filter expression should be :

"min_level  > grand_total and on_order_qty = 0 and on_request_qty = 0"

I see that 'min_level', 'on_order_qty' and 'on_request_qty' are DB cols
but ... 'grand_total' ? ..I suppose it's a computed field, what does it sum ?

sorry for delaying
Regards
0
 
Namik48075Author Commented:
That's what I put for the filter but it doesn't work.
Or mabe be I put the phole if statement.
 I will try without and see what I get

The Grand Total sums the curr_level and Alternative_curr_level for the group.
Ex. Main item curr_level = 2, there is 3 alternative items with curr_level respectfully 1,2,3,
The grand total will be 2+1+2+3 = 8.

Then it compares the min_level of main item to the grand total and checks for qty_ordered and qty_onhand. Based on that the flag is being printed.

I will try the filter statement without the "if" and see if it will work
Thanks a lot for your continuouse support
0
 
Namik48075Author Commented:
for some reason,
when I put the filter in the dw and save it , there is no rows in the output at all.
Then, I tried to run the dw ( run/preview), got all 632 rows, then did rows> filter with that expression, it gives me the correct results.

how do I do that in the script, do I put the filter statement before Retrive or after?

Thanks again
0
 
Namik48075Author Commented:
I did it in the script, it seams like I have to do 2 retrives.
When I put SetFilter and Filter statements before the Retrieve there is not rows returned, but when I do the Retrive and then Filter then retrive again, that  worked.

tell me if I am doing it wrong, may be I don't understand something and have some extra steps there,

Thanks
0
 
tr1l0b1tCommented:
Namik, there's no need to do 2 retrieves.
these are the general steps :

    // Turn off drawing
    dw_1.SetRedraw(FALSE)

    // Clear any previous filter
    dw_1.SetFilter("")
    dw_1.Filter()

    // Retrieve data
    dw_1.Retrieve(<arguments>)

    // Apply new filter if needed
    dw_1.SetFilter("<filter_expression>")
    dw_1.Filter()

    // Sort & Recalc groups
    dw_1.Sort()
    dw_1.GroupCalc()

    // Turn drawing on
    dw_1.SetRedraw(TRUE)

That should work.
Regards
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.