crosstab

[gagani]

orders table:
orderid product quantity trantimestamp
100 A 1 01/01/2001
100 B 2 02/02/2001
101 A 2 01/03/2001
100 A 3 01/02/2001
101 C 4 03/02/2001
102 A 1 04/02/2001
102 D 5 04/04/2001

products table:
A
B
C
D

the resultant output should sum the quantities for each product (for example, orderid 100
has two rows with product A, so it has to be summed up(1+3=4)).

output:
orderid A B C D
100 4 2
101 2 4
102 1 5

The products table in the future can increase with the new rows(new products).
The output should have to add columns dynamically fit the new rows
added in the products table. For the increase in product rows in products table, the columns should get added to the report.

 

[gagani]

can we insert crosstab as part of the report as there are some other columns belonging to other tables?

 

[Madawc]

Please clarify. Have you actually tried inserting the crosstab? They are pretty self-explanatory.

Madawc Williams (East Anglia, UK). Using Crystal 2008 with SQL and Windows XP

 

[gagani]

cross tab on its own is working fine
when the row is taken as orders.orderid and the column as product.product
I just need the column part from the crosstab and is to be displayed along with other
columns belonging to other tables.

sample table format is like this
jobnumber orderid customername telephone area A B C D
ABC 100 X 000 UK 4 2
DEF 101 Y 001 FR 2 4
GHI 102 Z 002 USA 1 5

A, B , C , D are obtained from crosstab, and the rest are belonging to other tables.

 

[gagani]

jobnumber orderid customername telephone area A B C D
ABC 100 X 000 UK 4 2
DEF 101 Y 001 FR 2 4
GHI 102 Z 002 USA 1 5

can we make crosstab as part of the report?

 

[lbass]

What happens when you try? Add the jobnumber, orderid, customername, and telephone as row fields. In the customize style tab, select each row and check "suppress subtotals" until you have the desired display.

-LB

 

[gagani]

Thank you. I am getting the results in the following way :

A B C D
ABC 100 X 000 UK 4 2
DEF 101 Y 001 FR 2 4
GHI 102 Z 002 USA 1 5

As there are no labels for each, then I introduced labels
at the top for each. It is ok to read but when I export it to excel, there is a bit difficulty.

I am getting the output as:

jobnumber orderid customername telephone

A B C D
ABC 100 X 000 UK 4 2
DEF 101 Y 001 FR 2 4
GHI 102 Z 002 USA 1 5

there is a space of two rows between the labels of

jobnumber orderid customername telephone


and the columns A B C D

Is there anyway we can introduce labels so that the labels of the output when exported to excel can be obtained in one line and followed by the results