group by grand total in a cross tab report 1

[adhhealth]

crystal version8 pervasive sql 8

I have a cross tab report which summarizes the item information by qty shipped and sales amount by month.

2/2005 3/2005 total
item A 120 10 130 qty
$235 $240 $470 salesamt
item B 12 110 122
$23 $250 $273
CustomerA 132 120 252
$258 $490 xxx

Can we sort the report based on the grand total of the quantity shipped?

 

[lbass]

If you first select the crosstab, then go to report->topN/group sort, you should be able to select the quantity shipped field as a group sort field. Be sure to select the correct tab Customer or Item (or you could do both).

-LB

 

[adhhealth]

Is there a possibility of the user choosing the sort option, grand total of qty shipped or sales amount?
Ex: if the user chooses parameter sales amount the report is sorted on sales amount.

 

[lbass]

You could create a string parameter {?sort} with two options: Sales Amount and Quantity Shipped. Then create two formulas:

//{@Field1}:
if {?sort} = "Sales Amount" then {table.salesamt} else
{table.qtyshipped}

//{@Field2}:
if {?sort} = "Sales Amount" then {table.qtyshipped} else
{table.salesamt}

Use {@Field1} and {@Field1} instead of the database fields in the summary section of your crosstab. Then go to topN/group sort and choose "sum of {@Field1}" as your group sort field. Since the content will change based on the parameter, this will always be the sort field.

This approach does mean that the parameter field will always be the first summary field in the display.

-LB

 

[adhhealth]

This doesnot sort based on the GRAND total of the qty shipped or sales amount,
it sorts based on the total (which is grouped by the month- 2/2005, 3/2005)..
so for one item i see multiple records,

 

[lbass]

When you go to topN/group sort, be sure you are choosing the correct tab. The topN/group sort always sorts by the total column. You can sort by the customer total, by the item totals, or by both. As far as I know, sorting by the month totals would not be an option unless you pivoted the crosstab.

-LB

 

[adhhealth]

I wrote the formulae wrong thats why i could not get the correct result,
wow!!! that was a very intelligent solution!!hats off to you LB..
but now the problem i have is if the report is sorted by qty shipped, the order is
qty shipped
amount
but if the report is sorted by sales amount the values will be in the opposite order
amount
qty shipped.



currently i have

2/2005 3/2005 total
item A 120 10 130
$235 $240 $470

can we label these summaries in some way like

2/2005 3/2005 total
item A qty 120 10 130
amt $235 $240 $470

 

[lbass]

For the row label, create a formula:

whilereadingrecords;
if {?Sort} = "Sales Amount" then "Amt" + chr(13) + "Qty" else
"Qty" + chr(13) + "Amt"

Insert this as the lowest level row field. Then go to customize style->item row->suppress subtotals.

-LB