Last Order Date Per Customer (crosstab) 1

[Dominican1979]

Hello Everyone,

I have a customer sales report that has a group header and its grouped on customer number. On the same group header I have a crosstab. I need to show the last order date for travel orders and last order date for phone orders. In our database a phone order would be order type 1 and travel would be order type 2. The report looks like this:

Cust Info:

Cust No: 254478 Last Phone Order:
Cust Name: Essential Eyewear Last Travel Order:
Phone: (312)555-5555
----------------------------------------------------------
(crosstab)

Jan-08 Feb-08 Mar-08....etc
Phone Orders
Travel Orders
Credits
Net

I tried creating a formula like this:
Maximum({OrderDate}) and added it to the same group header, but it doesn't work, it just shows the last order on the whole from the whole order table and shows the same last order date for every customer on the reoport. I thought it would change on every customer, but it doesn't. I know I also have to somehow specify something like this:

if OrderType =1 then
Max({OrderDate}) ------> for phone orders


if OrderType = 2 then
Max({OrderDate}) ------> for travel orders

This is just what i'm thinking something like that but i'm not sure. I tried it but I'm not getting the result I'm looking for. I'm using SQL Server 2005 and Crystal Reports XI. Thanks a lot for your help.

 

[lbass]

If you are just wanting to place the maximums in the group header, the crosstab is irrelevant. Create two formulas:

//{@phonedt}:
if {table.ordertype} = 1 then {table.orderdate}

//{@traveldt}:
if {table.ordertype} = 2 then {table.orderdate}

Place these in the detail section and right click on each and insert a summary(maximum) at the group level. Then drag the results into the group header. It is unnecessary to use formulas for this, but if you did, they would look like:

maximum({@phonedt},{table.custno})

...where the second argument is the group field.

-LB

 

[Dominican1979]

Hi LB,

Sweeeeeet it works perfectly, if you dont mind me asking, how do you know so much? it just seems that you have an answer for everything

 

[lbass]

I don't know so much, but I've worked hard at learning for years.

-LB

 

[Dominican1979]

I think you know a loooooooooooooot and thanks again for all your help I really appreciate it. I've been trying to work on this report for months, but never had the time until the last month or so.