Subreprots

[Viscount]

Hello,

I am new to Business Objects (actually a crystal user!)

Anyway I hope that someone can give some help. I have got report that shows Customer and products...

Cust_ID Cust_Name P_Code P_Desc

I would like to show customers that have purchased a certain Product (e.g. A) and have purchased any other products (e.g. B,C,D,E,F etc..) since purchasing the original product.

Any ideas..?

Cheers

Vis

 

[skrandel]

Since BO doesn't have subreports. You'll need to do this with a subquery.

Steve Krandel
VERITAS Software

 

[blom0344]

If you want to exclude those customers that only bought 'A' and no further products you could use a second DP that counts the items for each customer. Link the DP's and use a complex filter to exclude those customers where count(product) = 1. That is ,if you want to show the dates as well

so, first DP SQL would be something like:

[sup]
SELECT
  A.CUST,B.DAY,A.NAME,B.ITEM
FROM A,B
WHERE
  ( B.CUST=A.CUST  )
  AND  (
  A.CUST  IN  (SELECT A.CUST FROM A,B
WHERE
  ( B.CUST=A.CUST  ) AND  (B.ITEM  =  'XXX')))
[/sup]

and the second one:

[sup]
SELECT
  A.CUST,
  count( B.ITEM )
FROM
  A,B
WHERE
  ( B.CUST=A.CUST  )AND  (A.CUST  IN  (SELECT A.CUST
FROM
  A,B
WHERE ( B.CUST=A.CUST  ) AND  (B.ITEM  =  'XXX')))
GROUP BY A.CUST
[/sup]

presuming you have two tables involved

Ties Blom
Information analyst

 

[Viscount]

Thanks All,

I will give it a go today and let you know how I get on!

Cheers

Vis