Anyone here know Crystal Reports?

May not be too complex for someone else, but here's what I need to do.

I have a set of data I'm grouping on Customer, and am trying to figure out a way to find out if item y appears after item x within that group. The tricky part is that item y can also come before item x, but I only care if it comes after. So here's what my data looks like:

Data within the group is sorted by date)

Customer Number 123
Date Item Y
Date Item X
Date Item Y
Date Item Z

Customer Number 456
Date Item Y
Date Item X
Date Item Z

So for Customer Number 123 I'd want the formula to return a 1 or a yes or whatever, but for Customer Number 456 I wouldn't want anything returned since no Item Y comes after Item X.

Here's what I've done so far. I created a running total to count Item x, have this total in every row of the report and at the group level, total resets itself on each group.

Then I have a formula, on every record, that basically says if the running total (above) is greater than 0 and this row is item y, then stamp eith a 1.

All that works great, and when I unhide the details section I can see it's working correctly. So that leaves one last step, which Crystal apparently does NOT support. I need to know at the group level what the total of that formula I created above is. However, when I go to create a running total at the group, the formula field is NOT one of the fields I can choose to total, likely because it's also based on a total...

So it's the very last piece that I'm stuck on.

Any help is appreciated.

Can you send the results of your formula to a report, then total the report at the group level?

A BI tool like business objects would make that easier!

I'm not sure how I would do that. I don't know Crystal all that well. This is a complex report overall, and I was hoping to avoid having to recreate it in SQL, but looks like I may have to go that route...this sucks...

You can do it in MS Reporting Services. Create Matrix report.

Yeah, it's just that's going to take at least a full day to complete, and was hoping to avoid that

I would look to SQL for this. You'll probably have to create two sub-sql calls to calculate the first date occurrence of X and the first date occurreance of Y, and then wrap them in another SQL call that ensures X is less than Y.

Example:

SELECT DISTINCT
Orders.CustomerID,
QueryX.XDate,
QueryY.YDate

FROM
(
Orders INNER JOIN
(
SELECT
CustomerID,
First(Date) as XDate
FROM Orders
GROUP BY CustomerID, Item
HAVING Item="X"
) as QueryX
ON Orders.CustomerID=QueryX.CustomerID
)

INNER JOIN

(
SELECT
CustomerID,
First(Date) as YDate
FROM Orders
GROUP BY CustomerID, Item
HAVING Item="Y"
) as QueryY
ON Orders.CustomerID=QueryY.CustomerID

WHERE

QueryX.XDate < QueryY.YDate

You have to know the location of the parameters you are looking for in the database. Then you have to pray that when you add the next location you are grouping does not make you data invisible. Good luck. I have do trial and error.


engtaz