Add data to Crosstab 2

[Kibeth]

I have a crosstab showing monthly sales for each product, it look something like this
----------------------------------------------
January|February|March|April|May...
Mug| 20| 15| 10| 8| 2...
Mouse Pad 5| 14| 2| 3| 12...

I need to add the Quarterly sales, as well as a Monthly Averages column, so it would look something like this
----------------------------------------------
January|February|March|Q1|April|May|June|Q2|Mo.Avg|Total
Mug| 20| 15| 10|45| 8| 2| 13|23| 11| 68
Mouse Pad 5| 14| 2|21| 3| 12| 7|22| 7| 43

Any help that anyone can provide is greatly appreciated. I am trying to figure this all out without any training or manuals.

I am using CR XI Pro. Thanks in advance!

 

[Kibeth]

Oh, I also need to be able to sort my products by the overall top seller. My "Products" are groups of similar SKUs currently grouped by sepcified order. I'm not sure if there is a way to do this besides doing it manually.

 

[lbass]

Insert a crosstab where you choose {table.date} twice as your first two columns. Select the top date->group options and choose "print on change of quarter". Select the second one and choose "print on change of month". Add product as your row field ascending (because you are going to change the order anyway later), and choose sum of sales as your summary field.

In your main report, insert a group on {table.product} (this is necessary so you can use a formula below--you can suppress the group and detail sections). Then create two formulas:

//{@month}:
month({table.date})

//{@Monthly Avg}:
sum({table.sales},{table.product})/distinctcount({@month})

Add {@Monthly Avg} as your second summary field in the crosstab, using maximum as the summary. Use the arrow key to toggle this into position as your first summary.

Then go to the customize style tab and choose horizontal summary and show labels. Also check "suppress column totals".

Then in preview mode, select the monthly avg cells for month and quarter and suppress them (format field->common->suppress). Then in design mode, grab the right border of the suppressed cells and drag them so make the cell as narrow as possible. Edit the column labels as necessary.

-LB