Report to show units sold with other unit 1

[Woodyuk]

Im trying to do a report, which will show sales of a product, and then other types of products sold with it. In my example, I want to see how many DVDs are sold, in total and then see how many have the product TV sold with them (on the same agreement number). Im pulling the data from a table which has the agreement number in there, Product, Qty, Location and value. Its via an ODBC link, and im using crystal version 10. I thought about doing a subreport and just pulling out all the DVD sales Agreement numbers, then use this to pull out all the sales, but surely there must be another way?
See below for example, hope it makes sense

Data
Agree No Product Location Qty Value
1 DVD A 2 £80
2 TV B 1 £40
2 DVD B 1 £20
3 DVD A 1 £15
3 TAPE A 10 £5
3 TV A 2 £100
4 TV A 2 £100

Result im after:

Loca DVDs Sold Value of DVDs No of DVDs sold with TV Value of TVs sold with DVDs
A 3 £95 1 £100
B 1 £20 1 £40

 

[lbass]

I think you could just use conditional formulas. First insert a group on location. Then create these formulas:

//{@DVDqty}:
if {table.prod} = "DVD" then {table.qty}

//{@DVDval}:
if {table.prod} = "DVD" then {table.val}

Place these in the detail section and insert summaries on them at the group level to get the first two column results. Then create these formulas:

//{@TVqty}:
if {table.prod} = "TV" then {table.qty}

//{@TVval}:
if {table.prod} = "TV" then {table.val}

//{@reset} to be placed in the group header:
whileprintingrecords;
numbervar DVDwTVqty := 0;
numbervar TVwDVDval := 0;

//{@TVandDVD} to be placed in the detail section and suppressed:
whileprintingrecords;
numbervar DVDwTVqty;
numbervar TVwDVDval;

if sum({@TVqty},{table.location}) > 0 and
sum({@DVDqty},{table.location}) > 0 then (
DVDwTVqty := DVDwTVqty + {@DVDqty};
TVwDVDval := TVwDVDval + {@TVval}
);

//{@displDVDswTV} to be placed in the group footer:
whileprintingrecords;
numbervar DVDwTVqty;

//{@displTVwDVDval} to be placed in the group footer:
whileprintingrecords;
numbervar TVwDVDval;

-LB

 

[Woodyuk]

Thanks LB, for getting to this so soon. There is one problem. I cant see how you have linked the DVDs with the TVs. Maybe I havent made myself clear, or maybe ive misread your post.

I need to pull out all the DVD sales, and show how many had a TV with them.
So for example a location could sell 20 DVDs and 15 tvs in total. However my report would show 20 dvds sold, but only 10 of the 15 where on the same sale as the DVDs. Hope that makes sense

 

[lbass]

I think the formula should have been:

//{@TVandDVD} to be placed in the detail section and suppressed:
whileprintingrecords;
numbervar DVDwTVqty;
numbervar TVwDVDval;

if sum({@TVqty},{table.agreeno}) > 0 and
sum({@DVDqty},{table.agreeno}) > 0 then (
DVDwTVqty := DVDwTVqty + {@DVDqty};
TVwDVDval := TVwDVDval + {@TVval}
);

Sorry.

-LB

 

[Woodyuk]

Thanks LB.

In the mean time I worked out away to do this, with a bit of your help.
I dont get the numbervar things but from playing, I think ive got it and it works.

Thank you, a star has been added