cancel
Showing results for 
Search instead for 
Did you mean: 

Sort cross table based on summarized field

Former Member
0 Kudos

Hi all,

I have a cross table which shows the top N sales by customer. I'm trying and researching and I can't find a way to sort my crosstab by the summarized field to show the top N. I have seen posts from years ago where seems like it was not possible and people suggested workarounds,

You guys have found a better way to do it?

Accepted Solutions (0)

Answers (2)

Answers (2)

DellSC
Active Contributor
0 Kudos

There is potentially a way to get around this, but it means you cannot use a cross-tab and you need some fairly good SQL skills.  I have done this sort of thing in the past using a Command (SQL Select statement) to provide the data for the report.  If you do all of the summaries in the Command instead of having Crystal do them AND you "pivot" the data in the command, you can simulate a cross-tab by just putting the data in the details of the report because you'll have one row per customer.  At this point you can sort the data however you want to.

The challenge for this is the SQL.  What type of database are you connecting to?  If you'll provide the SQL that Crystal has generated along with information about what you're using for the cross-tab rows and columns along with which field you're summarizing and what parameters you may be using when filtering the report, I may be able to come up with the query for you.

-Dell

Former Member
0 Kudos

Wow, thank you!

Before sending the query, I wonder if it will be possible to modify the query behind as it is coming from a view, this view is used to feed at least 50 reports, so, in case of needed, I will have to create another view only for this report in a pivot way as you suggested. How would be the correct way to modify the query behind?

DellSC
Active Contributor
0 Kudos

The challenge with doing this in a view is limiting the time-frame of the data.  If the rule for pulling the data is something like "I always want the last twelve months", this can be done in a view.  If the start and end dates that can be selected are random, it will have to be done in a command or stored procedure instead.

-Dell

ido_millet
Active Contributor
0 Kudos

When the CrossTab is selected,

Group, Sort Expert

would sort it.

Former Member
0 Kudos

Hi Ido,

In that way, the crosstab gives me the option to sort by the row groups, not by the summarized field.

Thanks!

ido_millet
Active Contributor
0 Kudos

If what you are asking is about sorting the columns, that is not supported.

abhilash_kumar
Active Contributor
0 Kudos

Hi Minerva,

Could you elaborate on "the crosstab gives me the option to sort by the row groups, not by the summarized field."

-Abhilash

Former Member
0 Kudos

Hi Abhilash,

I have a crosstab that shows Sales by Parent, Territory and Category. I need to show the top (N) sales and sort by the total sales in a descendent way.

If I select the complete crosstab and do right click on "Group Sort Expert", Only row groups (Parent,Territory, Category) appear in the tabs, what I need is to sort by the summarized column.

ido_millet
Active Contributor
0 Kudos

As indicated in a prior answer, you cannot sort by column totals.  Only rows can be sorted in that manner.