strange sorting problem

building reports desktopintelligence
1

I’ facing this strange problem in my reports. I have to show 12 month data in the cross tab report. So i have written SQL to limit top 100 sales records at DB level. In the SQL, i have sorted the records in DESC order on ‘sales’(measure). I used the same code to display the data for 4 months and got different sorting results when compared with 12 months in crosstab.
Do you have any idea why i’m getting different results for 4 months and 12 months? Logically speaking, i should get common data for 4 months in both the cases since current month would be same for both of them, right?

BOisBest :india: (BOB member since 2004-04-05)

2

Why should you sorting be the same when you’re sorting on ‘sales’? They are different over 4 months as they are over 12 months? Or Are you sorting on the last month? Then you should be right. I think we need more info on this…

jobjoris :netherlands: (BOB member since 2004-06-30)

3

Anita, please post us a clear example.

In my opinion, if you have the following situation:

JANUAR: 21,00
FEBRUAR: 21,00
MARCH: 27,00
APRIL: 25,00
MAY: 26,00
JUNE: 25,00
JULY: 25,00
AUGUST: 19,00
SEPTEMBER: 27,00
OCTOBER: 30,00
NOVEMBER: 21,00
DECEMBER: 20,00

If you sort DESC on JANUARY…APRIL, you have:

MARCH: 27,00
APRIL: 25,00
FEBRUAR: 21,00
JANUAR: 21,00

If you sort DESC on JANUARY…DECEMBER, you have:

OCTOBER: 30,00
MARCH: 27,00
SEPTEMBER: 27,00
MAY: 26,00
APRIL: 25,00
JULY: 25,00
JUNE: 25,00
FEBRUAR: 21,00
JANUAR: 21,00
NOVEMBER: 21,00
DECEMBER: 20,00
AUGUST: 19,00

As you can see, we obtain differences between sorting through 4 months and sorting through 12 months.

I hope now it’s OK.

Please let us know! :wink:

Christian Konrads :it: (BOB member since 2004-07-21)

4

Christian,
In both the cases, i’m displaying January, Feb, March, April… this sequence. So i think i should get common data for those 4 months like this:

Name Month1 Month2 Month3…
A 1000 1100 1100
B 950 1000 1000
C 800 900 850

BOisBest :india: (BOB member since 2004-04-05)

5

In B.O. Reporter you can order by a MEASURE that you have created.

In my example, the months have to be ordered with the MEASURE.

The measure is a sort of “weight” for the dimension objects in the table report.

Christian Konrads :it: (BOB member since 2004-07-21)

6

I’m sorting on sales(measure) for all the months.

BOisBest :india: (BOB member since 2004-04-05)

7

BO gurus out there, could you please shed some light on this topic.

BOisBest :india: (BOB member since 2004-04-05)

8

What about classifying the dimensions basing on the measure?

Open the Slide and Dice panel, click on the dimension and click the icon with the classifier symbol (a little white scale with 3 numbers: 1, 2 and 3). Then select the measure. You will be asked to select the top N numbers, or the last N numbers, and you can decide for N.

I would like to know what dimension is in row, what dimension is in column and what measure is in body of your CROSSTAB, I really am not able to imagine that.

Christian Konrads :it: (BOB member since 2004-07-21)

9

Anita, I must be plain stupid but I don’t realy get your problem.

Are U sure you’re using two correct SQL-queries? I simply can’t reproduce your problem so please ‘visualize’ it a bit more…

jobjoris :netherlands: (BOB member since 2004-06-30)

10

I too. We are speaking about a CROSSTAB, but I can’t realize how is it composed.

Anita, please explain us! :wink:

Christian Konrads :it: (BOB member since 2004-07-21)

11

Of course, in the BODY of the CROSSTAB we can see the MEASURE.

This is definitely TRUE! :wink:

Christian Konrads :it: (BOB member since 2004-07-21)

12

Name Month1 Month2 Month3…
A 1000 1100 1100
B 950 1000 1000
C 800 900 850

1000+1100+1100 = 3200

that is greater than or equal to:

950 + 1000 + 1000 = 2950

that is greater than or equal to:

800 + 900 + 850 = 2550

That’s right: I think you’ve applied a classifier on the dimention MONTH, with refer to the measure in the body of the table.

I reproduced a same query in my environment.

Christian Konrads :it: (BOB member since 2004-07-21)

13

Ok guys, let me explain it to you. Here is my cross tab structure.

Name Month1 Month2 Month3 Month4…
A 1000 1100 950 900
B 950 1000 900 800

I have to sort names for Month1(no matter i’m displaying for 4 months or 12 months) on SALES values. And Christian, you are right, its basically adding all the SALES values for particular Name(horizontal addition) and then sorting on that total. But i want to sort purely on Month1 values(vertical).
I have tested the SQL and its correct. Then i tested some old reports(similar) and changed the duration from 4 months to 12 months and got matching results compared to my reports. i.e. vertical sorting order for month1 got disturbed. Is there any way, i’ll get the same correct vertical sorting order for SALES even for 12 months?

BOisBest :india: (BOB member since 2004-04-05)

14

Anita, I understand what you want to do: a vertical sorting on MonthX.

From your present situation:

Name Month1 Month2 Month3 Month4…
A 1000 1100 950 900
B 950 1000 900 800

… you would like to see this:

Name Month1 Month2 Month3 Month4…
A 1100 1000 950 900
B 1000 950 900 800

IS IT TRUE OR NOT?

Donald Duck :mexico: (BOB member since 2004-07-29)

15

exactly donald duck. do you have any idea about this?

BOisBest :india: (BOB member since 2004-04-05)

16

Can A, B, … be STATIC strings?

Donald Duck :mexico: (BOB member since 2004-07-29)