Filtering outer join table's fields in a dashboard

thanks Patty,

did you try and filter (with dashboard tracker arguments) values
coming from the the table which is connected to the right side of the
outer join (in the below example: SalesCat)?

I understand the way outer join works but I don't know if there's a
way to associate a tracker argument to filter the values in the
dashboard when the argument is bound to an outer join table.

what I think it does is actually filtering that table before the BAQ
runs (so it would filter all the records where SalesCat.FieldX = Y)
but when the BAQ runs the results would be wrong as the SalesCat table
is connected as an outer join table which means that records would be
created for left sides table values which did not appear in the filter
table to the right.

I guess my explanation is complex but I hope it is clear.

--- In vantage@yahoogroups.com, "bpbuechler" <pbuechler@...> wrote:
>
> All;
>
> It depends if you are looking to get information based on the sales
> order tables or information based on the invoice tables as the
> Product Group can be changed by the AR Invoice Person...and does not
> change the orderdtl.prodgrup.
>
> I have created two queries: Sales Order Status & Billed Sales Orders.
>
> Sales Order Status joins the following tables:
> OrderHed joined to Customer
> OrderDtl joined to OrderRel
> OrderHed joined to OrderDtl
> OrderDtl joined to ProdGrup
> OrderDtl joined to SalesCat (outerjoin)
>
> Billed Sales Orders joins the following tables:
> InvcDtl joined to Customer
> InvcDtl joined to InvcHead
> InvcDtl joined to ProdGrup
> InvcDtl joined to SalesCat (outerjoin)
>
> One thing to watch out for...
> To get to the Ext Price on the Sales Order...you cannot just take the
> qty * unit price. You must do the math for the UOM.
>
> (If (OrderDtl.PricePerCode = 'M') Then
> (((If (OrderRel.OurReqQty) > 0
> Then
> (OrderRel.OurReqQty)
> Else 0)/ 1000) * OrderDtl.DocUnitPrice * (1 -
> (OrderDtl.DiscountPercent / 100))) Else
> (If (OrderDtl.PricePerCode = 'C') Then
> (((If (OrderRel.OurReqQty) > 0
> Then
> (OrderRel.OurReqQty)
> Else 0)/ 100) * OrderDtl.DocUnitPrice * (1 -
> (OrderDtl.DiscountPercent / 100))) Else
> (((If (OrderRel.OurReqQty) > 0
> Then
> (OrderRel.OurReqQty)
> Else 0)/ 1) * OrderDtl.DocUnitPrice * (1 -
> (OrderDtl.DiscountPercent / 100)))))
>
> Hope this helps.
> Patty
>
>
> --- In vantage@yahoogroups.com, Robert Brown <robertb_versa@>
> wrote:
> >
> > Amir,
> >
> > Question (for my own knowledge/benefit): How are you successfully
> coding an outer join between 2 tables in a query with 3 joined tables?
> >
> > My query experience is with SQL (and I'm dating myself admitting
> this - also with 80's vintage D-base).
> >
> > Most SQLs only support outer left/right joins between 2 tables
> with a single linked key column. To add additional tables to a basic
> SQL outer join query, you typically need multiple queries tied
> together via UNION statements.
> >
> > It never occurred to me that the BAQ query phrase
> builder/language would support anything more than that as (so far)
> I've found it to be only equal or weaker than SQL.
> >
> > Have you validated that you are really getting a complete
> returned data set of the desired outer joined tables?
> >
> > Rob Brown
> >
> > "baruch.amir" <amir.baruch@> wrote:
> > Hi,
> >
> > I wanted to create a dashboard which will show all the Packing Slips
> > while allowing the user to query based on the Product Group in the
> SO
> > line associated with those packing Slips.
> >
> > the BAQ was quite simple:
> > I connected ShipDtl table with OrderDtl
> > and Orderdtl with ProdGroup. I used outer join with the last one as
> > not every OrderDtl must have a ProdCode associated with it.
> >
> > so far so good, but when I created a dashboard and added the
> ProdCode
> > to the tracker view in order to slice the results...the output was
> > affected what so ever.
> >
> > my translation to this behavior is that the arguments we place in
> the
> > tracker view slice the table before the BAQ runs, and since I
> > connected that table with an outer join I cannot really filter the
> values.
> >
> > I can try and bind the argument to the ProdCode in the OrderDtl
> > level,but my question still remains: can we filter the results after
> > the BAQ run with those tracker arguments?
> >
> > thanks,
> > Amir
> >
> >
> >
> >
> >
> >
> > ---------------------------------
> > Be a better friend, newshound, and know-it-all with Yahoo! Mobile.
> Try it now.
> >
> > [Non-text portions of this message have been removed]
> >
>
Hi,

I wanted to create a dashboard which will show all the Packing Slips
while allowing the user to query based on the Product Group in the SO
line associated with those packing Slips.

the BAQ was quite simple:
I connected ShipDtl table with OrderDtl
and Orderdtl with ProdGroup. I used outer join with the last one as
not every OrderDtl must have a ProdCode associated with it.

so far so good, but when I created a dashboard and added the ProdCode
to the tracker view in order to slice the results...the output was
affected what so ever.

my translation to this behavior is that the arguments we place in the
tracker view slice the table before the BAQ runs, and since I
connected that table with an outer join I cannot really filter the values.

I can try and bind the argument to the ProdCode in the OrderDtl
level,but my question still remains: can we filter the results after
the BAQ run with those tracker arguments?

thanks,
Amir
Amir,

Question (for my own knowledge/benefit): How are you successfully coding an outer join between 2 tables in a query with 3 joined tables?

My query experience is with SQL (and I'm dating myself admitting this - also with 80's vintage D-base).

Most SQLs only support outer left/right joins between 2 tables with a single linked key column. To add additional tables to a basic SQL outer join query, you typically need multiple queries tied together via UNION statements.

It never occurred to me that the BAQ query phrase builder/language would support anything more than that as (so far) I've found it to be only equal or weaker than SQL.

Have you validated that you are really getting a complete returned data set of the desired outer joined tables?

Rob Brown

"baruch.amir" <amir.baruch@...> wrote:
Hi,

I wanted to create a dashboard which will show all the Packing Slips
while allowing the user to query based on the Product Group in the SO
line associated with those packing Slips.

the BAQ was quite simple:
I connected ShipDtl table with OrderDtl
and Orderdtl with ProdGroup. I used outer join with the last one as
not every OrderDtl must have a ProdCode associated with it.

so far so good, but when I created a dashboard and added the ProdCode
to the tracker view in order to slice the results...the output was
affected what so ever.

my translation to this behavior is that the arguments we place in the
tracker view slice the table before the BAQ runs, and since I
connected that table with an outer join I cannot really filter the values.

I can try and bind the argument to the ProdCode in the OrderDtl
level,but my question still remains: can we filter the results after
the BAQ run with those tracker arguments?

thanks,
Amir






---------------------------------
Be a better friend, newshound, and know-it-all with Yahoo! Mobile. Try it now.

[Non-text portions of this message have been removed]
All;

It depends if you are looking to get information based on the sales
order tables or information based on the invoice tables as the
Product Group can be changed by the AR Invoice Person...and does not
change the orderdtl.prodgrup.

I have created two queries: Sales Order Status & Billed Sales Orders.

Sales Order Status joins the following tables:
OrderHed joined to Customer
OrderDtl joined to OrderRel
OrderHed joined to OrderDtl
OrderDtl joined to ProdGrup
OrderDtl joined to SalesCat (outerjoin)

Billed Sales Orders joins the following tables:
InvcDtl joined to Customer
InvcDtl joined to InvcHead
InvcDtl joined to ProdGrup
InvcDtl joined to SalesCat (outerjoin)

One thing to watch out for...
To get to the Ext Price on the Sales Order...you cannot just take the
qty * unit price. You must do the math for the UOM.

(If (OrderDtl.PricePerCode = 'M') Then
(((If (OrderRel.OurReqQty) > 0
Then
(OrderRel.OurReqQty)
Else 0)/ 1000) * OrderDtl.DocUnitPrice * (1 -
(OrderDtl.DiscountPercent / 100))) Else
(If (OrderDtl.PricePerCode = 'C') Then
(((If (OrderRel.OurReqQty) > 0
Then
(OrderRel.OurReqQty)
Else 0)/ 100) * OrderDtl.DocUnitPrice * (1 -
(OrderDtl.DiscountPercent / 100))) Else
(((If (OrderRel.OurReqQty) > 0
Then
(OrderRel.OurReqQty)
Else 0)/ 1) * OrderDtl.DocUnitPrice * (1 -
(OrderDtl.DiscountPercent / 100)))))

Hope this helps.
Patty


--- In vantage@yahoogroups.com, Robert Brown <robertb_versa@...>
wrote:
>
> Amir,
>
> Question (for my own knowledge/benefit): How are you successfully
coding an outer join between 2 tables in a query with 3 joined tables?
>
> My query experience is with SQL (and I'm dating myself admitting
this - also with 80's vintage D-base).
>
> Most SQLs only support outer left/right joins between 2 tables
with a single linked key column. To add additional tables to a basic
SQL outer join query, you typically need multiple queries tied
together via UNION statements.
>
> It never occurred to me that the BAQ query phrase
builder/language would support anything more than that as (so far)
I've found it to be only equal or weaker than SQL.
>
> Have you validated that you are really getting a complete
returned data set of the desired outer joined tables?
>
> Rob Brown
>
> "baruch.amir" <amir.baruch@...> wrote:
> Hi,
>
> I wanted to create a dashboard which will show all the Packing Slips
> while allowing the user to query based on the Product Group in the
SO
> line associated with those packing Slips.
>
> the BAQ was quite simple:
> I connected ShipDtl table with OrderDtl
> and Orderdtl with ProdGroup. I used outer join with the last one as
> not every OrderDtl must have a ProdCode associated with it.
>
> so far so good, but when I created a dashboard and added the
ProdCode
> to the tracker view in order to slice the results...the output was
> affected what so ever.
>
> my translation to this behavior is that the arguments we place in
the
> tracker view slice the table before the BAQ runs, and since I
> connected that table with an outer join I cannot really filter the
values.
>
> I can try and bind the argument to the ProdCode in the OrderDtl
> level,but my question still remains: can we filter the results after
> the BAQ run with those tracker arguments?
>
> thanks,
> Amir
>
>
>
>
>
>
> ---------------------------------
> Be a better friend, newshound, and know-it-all with Yahoo! Mobile.
Try it now.
>
> [Non-text portions of this message have been removed]
>
thanks Rob,

I compared my results to a query with regular join and the output was
the same so I guess it is working as expected.
I have copied the query at the bottom of this mail so you could have a
look .

but to the original question: do you know if there is a way to filter
the results after the query ran and not before it.
it seems that the arguments in the DSB filter the table before it takes
place in the query and not its results.

for each ShipHead where ( ShipHead.ReadyToInvoice = FALSE) no-lock ,
each ShipDtl where (ShipHead.Company = ShipDtl.Company and
ShipHead.PackNum = ShipDtl.PackNum) no-lock , each OrderDtl where
(ShipDtl.OrderNum = OrderDtl.OrderNum and ShipDtl.OrderLine =
OrderDtl.OrderLine) no-lock , each Customer where (ShipHead.Company =
Customer.Company and ShipHead.CustNum = Customer.CustNum) no-lock , each
ProdGrup outer-join where (OrderDtl.ProdCode = ProdGrup.ProdCode)
no-lock , each Project outer-join where (OrderDtl.ProjectID =
Project.ProjectID) no-lock by ShipHead.PackNum.

--- In vantage@yahoogroups.com, Robert Brown <robertb_versa@...> wrote:
>
> Amir,
>
> Question (for my own knowledge/benefit): How are you successfully
coding an outer join between 2 tables in a query with 3 joined tables?
>
> My query experience is with SQL (and I'm dating myself admitting
this - also with 80's vintage D-base).
>
> Most SQLs only support outer left/right joins between 2 tables with
a single linked key column. To add additional tables to a basic SQL
outer join query, you typically need multiple queries tied together via
UNION statements.
>
> It never occurred to me that the BAQ query phrase builder/language
would support anything more than that as (so far) I've found it to be
only equal or weaker than SQL.
>
> Have you validated that you are really getting a complete returned
data set of the desired outer joined tables?
>
> Rob Brown
>
> "baruch.amir" amir.baruch@... wrote:
> Hi,
>
> I wanted to create a dashboard which will show all the Packing Slips
> while allowing the user to query based on the Product Group in the SO
> line associated with those packing Slips.
>
> the BAQ was quite simple:
> I connected ShipDtl table with OrderDtl
> and Orderdtl with ProdGroup. I used outer join with the last one as
> not every OrderDtl must have a ProdCode associated with it.
>
> so far so good, but when I created a dashboard and added the ProdCode
> to the tracker view in order to slice the results...the output was
> affected what so ever.
>
> my translation to this behavior is that the arguments we place in the
> tracker view slice the table before the BAQ runs, and since I
> connected that table with an outer join I cannot really filter the
values.
>
> I can try and bind the argument to the ProdCode in the OrderDtl
> level,but my question still remains: can we filter the results after
> the BAQ run with those tracker arguments?
>
> thanks,
> Amir
>
>
>
>
>
>
> ---------------------------------
> Be a better friend, newshound, and know-it-all with Yahoo! Mobile.
Try it now.
>
> [Non-text portions of this message have been removed]
>



[Non-text portions of this message have been removed]