Regarding sorting

system · July 16, 2004, 11:35am

I need to show 12 month data for top 100 sales in a crosstab and i have 6 such crosstabs for different measures. I need to sort sales in desc order starting from current month. But when i sort the data, i dont get proper sorting order. I tried through query panel as well as through formatting. Any kinda help would be appreciated.
Thanks

BOisBest (BOB member since 2004-04-05)

system · July 16, 2004, 11:49am

Look for Custom Sorts:
Select your month column, go to Format| Sorts…, check the radio button labeled “Custom”, click on values, and arrange the sort order to your liking.

Andreas (BOB member since 2002-06-20)

system · July 16, 2004, 1:26pm

Andreas,
I have tried this option too. Month is getting sorted in desc order but the measure values for each month are not sorted properly.Any clues?

BOisBest (BOB member since 2004-04-05)

system · July 16, 2004, 9:35pm

How about giving us a “picture” (that is, show us a sample crosstab) of what you’re getting, and another of what you want?

Anita Craig (BOB member since 2002-06-17)

system · July 18, 2004, 1:39am

This is what i need :


                                     Month1    Month2   Month3
Code  Issue_Name             Sales      Sales      Sales ............

abcd   FinanceIssue         1200       1100       1250
efgh   Marketing              1100       1050       1100
|
|
|

So i need to sort sales in desc order starting from Month1. And this is what i’m getting:


                                      Month1     Month2    Month3
Code    Issue_Name         Sales       Sales       Sales
xyz      HRDept                 1000        1100       1200
pqrs     Issuename            1200        1300       1250
|
|
|

These are not the actual figures but just to show you guys what i’m getting.

BOisBest (BOB member since 2004-04-05)

system · July 18, 2004, 4:21pm

Looks like all you need to do is verify that there are no other sorts/breaks in your report and that you are sorting by CODE in Desc order. The Slice and Dice panel will make this visually apparent.

You may also want to verify what “Format” your Months are in. If they are returned as Dates, you will be fine, but if they are character you will need a custom format to “fix” this.

digpen (BOB member since 2002-08-15)

system · July 18, 2004, 4:35pm

I’m showing months as ‘Jan-03’. I’m getting proper order for months i.e.desc order.

BOisBest (BOB member since 2004-04-05)

system · July 18, 2004, 4:39pm

digpen,
I have applied ranking on CODE based on sales to show top 100 records though. Is this hampering the sorting?

BOisBest (BOB member since 2004-04-05)

system · July 19, 2004, 1:03pm

Yes! Ranking is essentially performing a “sort and filter” function, and overrides any other settings.

Dwayne Hoffpauir (BOB member since 2002-09-19)

system · July 19, 2004, 1:40pm

Thanks Dyane
I 'm getting sorted results for one dimensions but when i try to restrict rows to top 100 through options in the Query panel, i get truncated results i.e. instead of getting 12 month data, i 'm getting data for a month only. Any clues?

BOisBest (BOB member since 2004-04-05)

system · July 20, 2004, 5:26am

The Query panel is restricting your return dataset to 100 rows of data. Unless you have some sorting occurring in the database, that 100 rows may be randomly selected based on the fetch sequence.

If you want the top 100 ranked, then you need to actually create a ranking object and then filter that. This can be done using a DRANK function in Oracle, though you may have to create the report in Freehand SQL if you want to control the context. I believe you will also need to rank in a sub-select so that you can restrict on it.

digpen (BOB member since 2002-08-15)

system · July 20, 2004, 11:11am

digpen,
when i try to use Oracle RANK function in my query, i get error message: object does not exist in the result.

BOisBest (BOB member since 2004-04-05)