Cross Tab sorting 1

[Kibeth]

I have a Cross Tab that has Products in rows, and Quantities in monthly columns. I want to sort this by Total Quantities for the year. I have tried using the Group Sort option, and it isn't working. Any ideas?

I'm using CR XI Professional.

 

[lbass]

I forgot to address this in your previous post. You need to select the upper left hand corner of the crosstab and then go to report->group sort->and choose the summary to sort by. Note that this will only sort on the total column.

-LB

 

[Kibeth]

lbass, thank you so much for all of your help with this. That option is not available to me after I have clicked in the corner of the cross tab. I wonder if this is because my products are grouped in specified order. I have 10 sizes of one product displaying as one line instead of 10, and have done this with most of my products. If I click on one of the products then the option is available, but it isn't actually sorting by my totals. Any other suggestions?

 

[lbass]

There is no point in using specified order if you are going to then group sort--which will order the products by the total, and group sort will not be enabled if you have the order specified. Try sorting ascending. Also make sure the crosstab object is selected when you go to report->group sort, otherwise it won't be enabled.

I don't know what you mean by

I have 10 sizes of one product displaying as one line instead of 10, and have done this with most of my products.

, but it's probably not relevant to the group sort issue.

-LB

 

[Kibeth]

The reason I am using the Specified order is because I have several products that I want to list as one product. For example, one of our products is "Prints" so the database actually has 4x6 Prints, 5x7 Prints, 8x10 Prints, 8x12 Prints, 3x5 Prints and Wallets, but in the report I want it to see the total sales for ALL Prints. We also have Posters, several mugs, etc.
If there is another way for me to combine these products into one so I can use the sort option that would be great!

Example (straight from the database)
4x6|10|2|12
5x7|5|10|3

I want to see:
Prints|15|12|15

Hope that helped to clarify.
Thank you for all your help.

 

[lbass]

I guess I don't understand what you are using for your row field, and what using specified order actually does for you.

If you have a field that results in "Prints", "Mugs", "Posters", then you should be using that field as your row in your crosstab.

If you don't have a field that automatically clusters these products, then you should make a formula that uses some field that does distinguish these higher order products. What fields are you working with, and how do they display? Is there a product ID that has some character(s) that identify these products at the higher order level?

-LB

 

[Kibeth]

Sorry for taking so long to get back with this information. I do not have a field that clusters these products. The field I am working with (The field for the Row) is the product name (4x6, 3x5, 20x20 Gloss, etc), which I am grouping using the Sepcified Order to get the high level product descriptions(Prints, Posters, etc). The other field associated with the products is the SKU, but there is no common character by which to group items.
I don't know how to even begin grouping these items in a formula. Thank you again for all of your help with this.

 

[lbass]

If you manually know how to cluster them, then use the same principle in a formula, as in:

if {table.SKU} in ["123","456","357"] then
"Posters" else
if {table.SKU} in ["346","532","248"] then
"Mugs" else
"Other"

Or you could base the formula on some content of the product name. Then use this formula as your row field instead of using specified order.

-LB

 

[Kibeth]

Thank you so much for all of your help with this! It has been wonderful working with you. You have been so patient, and helped walk me through the simplist of issues. You truly are a wonderful help to everyone on here. Thank you!