CODEWITHSUNDEEP
sortingcrystal-reportscrosstabcrystal-reports-xi
Percy Greyhound
Percy Greyhound

Reputation: 31

Crystal Reports Sort by Specific Column in Cross Tab

I am trying to sort by a specific column in Crystal Reports. I have a cross tab of breed of dogs and cats by food type. So the breeds are the rows and the food types (dry, wet, raw, diet, senior) are the columns.

Inside the cells are the percentages - that is, for example, 10% of golden retrievers eat raw food. I want to sort by the "raw" column to find out the top breeds whose owners feed them raw food.

I've searched, but can't find an answer to this - most sites are saying a custom formula is required, but I can't get one to work.

If we're able to figure this out, it would be great to next be able to do a bar chart of the top 5 raw-eating breeds. And if we get THAT to work, to then sort out by dogs and cats (top 5 raw-eating dogs, top 5 raw-eating cats).

Upvotes: 2

Views: 1571

Answers (1)

Ryan
Ryan

Reputation: 7287

Normally a crosstab sort is designated by the entire row and not for a single column. You can do it but it involves a little workaround involving an additional formula, as you already guessed.

This new formula should only consider raw food and ignore all other types. So something like this:

//{@Raw Food}
//Formula that includes data only for raw food
if {Table.FoodType}="Raw" then {Table.NumberOfAnimalsThatEatThis}

Now go into your pre-existing crosstab and add this new formula to the "Summarized Fields", which will allow you to use it for sorting. Select your crosstab, right-click on it, and select the "Group Sort Expert" and finally select the formula you just created and tell CR to sort the crosstab in descending order. If you don't want to display this field (which you probably won't) you can just suppress the new cells.

As for the second part of your question: Creating charts is not related to the crosstab and will be set up entirely independently. You can just use the same formula you just created for raw food and throw it in a run-of-the-mill bar chart; nothing tricky.

Upvotes: 1

Related Questions