Ranking feature changes data?

building reports webintelligence
1

I noticed something very strange in an XI report today. It’s a report with one query and a cross tab, pretty simple. I want to rank it to display only our top 100 customers. When I do this the data actually changes and it seems like the cross tab eliminates some of the columns that should be there?

I can’t understand why, has anyone ever noticed something like this happening before?

LuxBOB :us: (BOB member since 2008-08-20)

2

A rank applies a sort followed by a filter. It could be that the filtering is removing values that provide structure (columns) from your crosstab, and that’s not unexpected.

Dave Rathbun :us: (BOB member since 2002-06-06)

3

Why would the filter applied by the rank exclude columns from the cross tab?

Is there a solution?

LuxBOB :us: (BOB member since 2008-08-20)

4

The number of rows and columns in a crosstab are determined by the data. Simple example: if you have four quarters, and you rank the “top two” quarters, and if quarter is the heading across the top of your columns, your crosstab will reduce from four columns down to two. There’s no solution, because it’s not broken. :slight_smile: That’s the way it works.

Dave Rathbun :us: (BOB member since 2002-06-06)

5

Doesn’t seem like that can be the problem here. I am ranking by total sales of the customer. The sales are segmented into a few different catagories.

The rank should not filter out any of the catagories if it is total sales?

Am I missing something?

LuxBOB :us: (BOB member since 2008-08-20)

6

Unless you explicitely create a variable for total sales across all categories, when you do a rank in a crosstab the rank is based on the crosstab structure.

Even if that’s not the case, the concept of “data-driven headings” is still applied. Suppose you had 10 customers, and each has sales in only 5 categories. If you rank the top 1 customer, then you will only see the 5 categories where they have sales.

Dave Rathbun :us: (BOB member since 2002-06-06)

7

There is one measure that is the total sales. I create a crosstab of that to segment it into different catagories.

After the rank is applied the same customer before and after the rank has a different total sales.

I understand the concept of data driven headers but if the data is in the header before the rank why would it be gone after. The rank should be ranking by total sales not any specific header.

Eg:
Before Rank
Cust #1 type A sales = 2 Type B sales = 5 Total Sales = 7
After Rank
Cust #1 type A sales = 2 Total Sales = 2

LuxBOB :us: (BOB member since 2008-08-20)

8

A rank is not based on a measure. A rank is based on a dimension. The measure value is used to determine the ranking assigned to the dimension, so when you build a rank you say rank “customer” based on “sales” and that’s where the filtering comes in.

Ok, now I’m a bit confused. You are sure that you are ranking sales on “customer” and not “type”? And it looks like it’s picking the bottom amount rather than the top amount…

Dave Rathbun :us: (BOB member since 2002-06-06)

9

Ah ha!

I got it now, it seems that it ranks by type if you don’t specify to rank by customer. That’s why data from column headings were disappearing.

I was trying to rank each section of the report to show the top 100 so I ranked by section by sales. When I changed it to by customer by sales it ranks it within each section and works perfectly.

Thanks for all your help.

LuxBOB :us: (BOB member since 2008-08-20)