[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

DW Retrive options

Posted on 2006-04-06
12
Medium Priority
?
353 Views
Last Modified: 2013-12-26
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.
0
Comment
Question by:Namik48075
  • 6
  • 5
12 Comments
 
LVL 6

Expert Comment

by:tr1l0b1t
ID: 16399044
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
 
LVL 5

Expert Comment

by:berXpert
ID: 16400950
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
 

Author Comment

by:Namik48075
ID: 16453140
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 6

Expert Comment

by:tr1l0b1t
ID: 16467590
Namik,
Could you please post here the conditions or expression you're using for the flag ?
0
 

Author Comment

by:Namik48075
ID: 16468606
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
 

Author Comment

by:Namik48075
ID: 16498188
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
 
LVL 6

Expert Comment

by:tr1l0b1t
ID: 16500521
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
 

Author Comment

by:Namik48075
ID: 16500949
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
 

Author Comment

by:Namik48075
ID: 16501043
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
 
LVL 6

Accepted Solution

by:
tr1l0b1t earned 1200 total points
ID: 16501284
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
 

Author Comment

by:Namik48075
ID: 16501345
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
 
LVL 6

Expert Comment

by:tr1l0b1t
ID: 16505842
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Update (December 2011): Since this article was published, the things have changed for good for Android native developers. The Sequoyah Project (http://www.eclipse.org/sequoyah/) automates most of the tasks discussed in this article. You can even fin…
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…
The viewer will learn how to synchronize PHP projects with a remote server in NetBeans IDE 8.0 for Windows.
The viewer will learn how to use and create new code templates in NetBeans IDE 8.0 for Windows.
Suggested Courses

834 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