Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Report Design
Message Icon Topic: My First Funnel Post Reply Post New Topic
Author Message
ViperSBT
Newbie
Newbie
Avatar

Joined: 02 Jul 2009
Location: United States
Online Status: Offline
Posts: 12
Quote ViperSBT Replybullet Topic: My First Funnel
     Posted: 18 Oct 2010 at 3:18am
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.
IP IP Logged
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum