Working on creating my first Funnel Chart and report. What I am trying to do is get a look at all of my quotes and compare that with all of my orders.
Based on this comparison, I need to know all of my quotes that do not have corresponding orders. This should allow me to get a total for those quotes not having orders against them as the top of my funnel, then the orders that are currently in the system as the second layer of the funnel.
I will be adding more to this funnel as I learn more about this... So, here is where I am stuck...
I can generate a report for all of my quotes and get a total. I can generate a report for all of my orders and generate a total. But, I can't seem to generate a report that shows both Quotes and Orders together without duplicating lots of values. It seems that this would have something to do with the JOIN. As example:
| QuoteNum |
QuoteLine |
ExpectedRevenue |
OrderNum |
OrderLine |
OrdBasedPrice |
| 80303 |
2 |
$
2,265 |
61303 |
2 |
$
3739.00 |
| 80303 |
2 |
$
2,265 |
61303 |
3 |
$
3931.00 |
| 80303 |
2 |
$
2,265 |
61303 |
4 |
$
4634.00 |
| 80303 |
2 |
$
2,265 |
61303 |
5 |
$
4891.00 |
| 80303 |
2 |
$
2,265 |
61303 |
6 |
$
39.00 |
| 80303 |
2 |
$
2,265 |
61303 |
7 |
$
1494.00 |
| 80303 |
2 |
$
2,265 |
61303 |
8 |
$
7199.00 |
| 80303 |
2 |
$
2,265 |
61303 |
9 |
$
10756.00 |
Looking at the above data we should not see quote 80303 line 2 replicating its data for every line of order 61303...
My order details do have a quote and quote line field that I could use to link them to specific Quotes, but then that would leave out all of my Orders that do not reference a specific quote, and I don't want to miss those in my funnel...
My current query looks like this:
SELECT "QuoteDtl1"."ExpectedRevenue", "QuoteDtl1"."ProdCode", "OrderDtl1"."OrdBasedPrice", "QuoteDtl1"."QuoteNum", "QuoteDtl1"."QuoteLine", "OrderDtl1"."OrderNum", "OrderDtl1"."OrderLine"
FROM {oj "MFGSYS"."PUB"."QuoteDtl" "QuoteDtl1" LEFT OUTER JOIN "MFGSYS"."PUB"."OrderDtl" "OrderDtl1" ON "QuoteDtl1"."Company"="OrderDtl1"."Company"}
WHERE ("QuoteDtl1"."ProdCode"='BOI' OR "QuoteDtl1"."ProdCode"='ENG' OR "QuoteDtl1"."ProdCode"='FGD') AND "QuoteDtl1"."ExpectedRevenue">0 AND "OrderDtl1"."OrdBasedPrice">0
Any help would be greatly appreciated, I am using CR XI.