Crystal 1

[tsouth4]

Using Crystal XI and am currently working on a very complex report (so I think). What I need to do is determine total revenue by customer based on which year their first order was placed. Sounds pretty easy. The catch is I only want a summation of the total revenue for each custmer from year their first order was placed in. I dont want to include any revenue received by the customer in any orders post the first order year. I began by grouping all customers by email address, then grouped them by Year in which the orders were placed. I inserted the following fields into my report for each customer [(WM_ORDER.EMAIL),(WM_ORDER.ORDERID)aka purchase order, (WM_ORDER.DT_PLACED),(WM_BILL.TOTRECD)aka Revenue)].


Please see an example below.

joesmith@1234.com
2003
PO#10000 06/01/03 $859.99
PO#11111 09/09/03 $35.99
2004
PO#33333 01/05/04 $651.22
2006
PO#55555 12/15/06 $1999.99

Dave@1234.com
2003
PO#20000 05/06/03 $400.10
2005
PO#40000 04/01/05 $70.23
PO#45000 08/01/05 $5000.00


My desired result for this report is to see the total revenue by customer when their first order was placed. I dont want to include any Orders other than those that were placed in the same year in my totals. So my desired result for the data above would be as follows

Joesmith@1234.com 2003 $895.98
Dave@1234.com 2003 $400.10

The reason I need to show all data and I can't just run it by year is that I dont want to include them in any other year besides the year their first order was placed. I plan on showing these totals by year to see how much revenue has increased by first time customers through the years. It would be much more simple if there was a view or for each year which provided emails first time customers.

I am open to any help

-Tim

 

[Turkbear]

Hi,
What database and version?

If you can create views in the database, create separate ones using the 'Min( Year) grouped by customer' for each view's data..





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[lbass]

Insert a group on {WM_ORDER.DT_PLACED} and then go to report->selection formula->GROUP and enter:

year({WM_ORDER.DT_PLACED}) = year(minimum({WM_ORDER.DT_PLACED},{WM_ORDER.EMAIL}))

You can then insert a running total that sums {WM_BILL.TOTRECD}, evaluate for each record, reset on change of group: {WM_ORDER.EMAIL}. Place the running total in the group footer.

-LB

 

[tsouth4]

I am using CR XI and accessing an Oracle database. Unfortunately I myself do not have the authority to create a view in the database. Is there no other way?

 

[lbass]

I think you missed my post.

-LB

 

[tsouth4]

Thanks for the quick replies. Should I do everything as I mentioned above meaning first Group by WM_ORDER.EMAIL then group by WM_ORDER.DT_PLACED and then go to report->selection formula as you mentioned?

 

[lbass]

Yes.

-LB

 

[tsouth4]

Ok that worked now how can I get the total Revenue per year from that. Sorry if this is confusing... If the order was placed in 2005 and they placed another order in 2006 I do not want this included in my 2006 total revenue.

Thanks for all your help.

-Tim

 

[lbass]

Create a separate running total for each year by using a formula in the evaluation section of the running total, e.g.:

year({WM_ORDER.DT_PLACED}) = 2006

Place the running total in the report footer. It will only pick up the displayed results.

-LB

 

[tsouth4]

Thanks LB you were very helpful!!!!

 

[synapsevampire]

See the little text that says "thank lbass for this valuable post"?

That's how one thanks someone who puts in this sort of effort on you, not by disregarding what has been in front of you all day.

This states that the post is of use, and others searching for solutions will open it because someone found it useful, plus it gives lbass kudos for her monumental efforts in extracting requirements from you and resolving the issue.

Which should pay off in the future if she leverages it.

Anyway, saying thanks is good too, but please take a moment to observe your surroundings and conduct yourself accordingly.

-k

 

[tsouth4]

Thanks for the advice however I already thanked LB before your post. However when I thanked them by clicking on the link it did not allow me to type anything. I felt the need to express how greatful I was for the help.

-Tim

 

[synapsevampire]

Well you didn't thank lbass, even though you might like to pretend that you did. Otherwise there would be a star there for all to understand you had thanked her, and that she had yet again provided a great service.

Which of course gives her more negotiating room in the marketplace.

So don't say thanks to me for the advice, click the thank lbass and then click confirm it.

In lieu of giving donations for Aids and Malaria in Africa, do you send along a get well card because you feel it's better for them?

Typing thanks is wonderful, but do the right as well.

-k

 

[synapsevampire]

Bravo!

-k