Customer Status Definition

Hi, hope someone can help with these and apologies if this has come up before. I should stress that I am relatively new to BusinessObjects.

A key dimension for our marketing department is customer status - how many new customers bought something in our online shop over a particular period of time, how many new customers returned in this period, and how many were existing customers. To this end, I have created a derived table in the data foundation called customer status, as follows:

SELECT uni_usr_id,

Customercode,

CASE

WHEN Customercode = 0 THEN 'New: Unique Order'

WHEN Customercode = 1 THEN 'New: Multi Orders'

WHEN Customercode = 2 THEN 'Existing Customer'

ELSE 'Error'

END AS Customerstatus

FROM

(SELECT uni_usr_id,

CASE

WHEN min_prev = 0 AND no_ords = 1 THEN 0

WHEN min_prev = 0 AND no_ords > 1 THEN 1

WHEN min_prev > 0 THEN 2

ELSE 3

END AS Customercode

FROM

(

SELECT

uni_usr_id, min(prev_orders) AS min_prev, count(DISTINCT order_id) AS no_ords

FROM

fact_orders

WHERE fact_orders.order_date BETWEEN @Prompt(From_date) AND @Prompt(To_date)

GROUP BY uni_usr_id) a

) b

ORDER BY Customercode

;

prev_orders is just a field in the orders table, starting at 0 for a customer's first order. I hope the rest is self-explanatory. This derived table is connected to the main fact table via uni_usr_id, and the prompt dates are run once for both tables. In most circumstances this works very well.

However, if a report user wants to specify something regarding the order - for example, minimum order spend - then it gets trickier. Of course, you can do this for the actual orders with a sub-query, but this will not affect the calculation of status. So for example, if one customer had 4 orders, and only 2 of them were above €20, then the main report would just include the €20, but the calculation of status would pertain to all customer orders.

Does anyone know of a smarter way to make this kind of report more dynamic, therefore, other than adding a lot of filters to the script of the derived table? I can also replicate the calculation of status via a report variable, but the problem there is that the variable only works at the level of the individual user, and does not seem to work at the summary level.

Hope this makes sense, very happy to provide more information if required. Thanks