cross tab or reporting sort 2

[shahinrep]

dear all if have problem drive me crazy
now i have report ( cross tab ) like this

for each item the quantity sold in the current year and the last year

Item Code 2007 2008
--------- ----- -----
11 200 350
12 350 223
13 200 120


now the problem is

how can i sort the report with the quantity sold for the current year

( sort for the quantities for the current year up-down )

and how can i add field in the report to make the percintage beteen the last yeat and current year quantity sold

any help

best regards

 

[Madawc]

I think a different approach is needed. One way would be to add the dataset twice, linked by Item Code. Select on for 2007 and the other for 2008 - or rather for the current year value and the previous year. On this basis, it would be very easy to sort using 'Top N', and to compare the totals.

A possible drawback is that you'd not see items unless there was a 'quantity sold' in each year. Does this matter? If it does, you can do a left-outer link and also adjust your test to allow for nulls - you find a null if a linked record is missing.

It helps to give your Crystal version - 8, 8.5, 9, 10, 11 or whatever. Methods sometimes change between versions, and higher versions have extra options. In this case, it probably makes no difference.


Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[lbass]

You don't need to add the table twice. Instead create a manual crosstab. Insert a group on {table.itemcode} and then create two formulas:

//{@curryr}:
if year({table.date}) = year(currentdate) then {table.qty}

//{@lastyr}:
if year({table.date}) = year(currentdate)-1 then {table.qty}

Place these in the detail section and right click and insert a sum on each of them at the group level. Then drag the group name into the group footer, and suppress the group header and detail section. Then create a formula for the percentage:

//{@pctchange}:
if sum({@lastyr},{table.itemcode}) > 0 then
(sum({@curryr},{table.itemcode})-sum({@lastyr},{table.itemcode})) % sum({@lastyr},{table.itemcode})

Place this in the group footer also. Then go to report->group sort->and choose sum of {@curryr} as your group sort field.

-LB

 

[shahinrep]

thank you for your replly

but what if there is 3 or 4 year
shall icreated manualy with formulas

i mean if i need 2006 2007 20008 2009

 

[shahinrep]

i have problem now when i sort the report

iam using cr 7

there is no group sort

((

 

[lbass]

It would be called TopN in CR7, but you can just choose "All" for the sort.

Yes, you can still do this manually. Just change the number of years you subtract in each formula.

-LB