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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
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
Become a CompTIA Certified Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

tr1l0b1tCommented:
Namik,
Could you please post here the conditions or expression you're using for the flag ?
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.
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
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
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
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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Editors IDEs

From novice to tech pro — start learning today.