BusinessObjects Board

Sorting in Cross tab report for top 100

building reports desktopintelligence
#1

I have a cross tab report for top 100 customers. This report brings data for prompted month (Current month), Previous Month and Six months before from prompted month. If the prompted month is April 2002, then it brings data for March 2002 and October 2001.After I ranked the Customer for top 100 based on sales amount it doesnÂ’t sort the amount in Descending order but it is sorting based on row wise. Below is the scenario

Customer 04/01/2002 03/01/2002 10/01/2001
A 100000 99500 88500
B 95000 99000 91000
C 96000 95000 93000
D 87000 86500 92000
E 89000 88000 85400

If you look at rows C, D & E it is not sorting based on a column wise but on a row wise. Is there a way can we sort it in Descending order based on prompted month i.e., 01/04/2002.

Also I want to calculate variance between 04/01/2002 - 03/01/2002 and 04/01/2002 – 10/01/2001. I can insert variance by selecting the values but it is static, can we make it dynamic and how to achieve this.

TIA

brahma (BOB member since 2002-11-21)

#2

Which column did you apply the rank to? That column should be sorted in ascending order.

MichaelWelter :vatican_city: (BOB member since 2002-08-08)

#3

I applied rank on Customer based on the amount

brahma (BOB member since 2002-11-21)

#4

Which amount? You have three different amount columns.

MichaelWelter :vatican_city: (BOB member since 2002-08-08)

#5

Your Customers in you example are ranked by total amounts.

Create two new local report variables:
“VAR Flag Is Current Month” (Boolean) and “VAR Amount - Current Month” defined as:

=Amount Where (<VAR Flag Is Current Month>=1)

Then create another variable defined as

=rank (<Customer>, <VAR Amount - Current Month>)

Andreas :de: (BOB member since 2002-06-20)

#6

Guys,
I have similar kinda requirement where i have to sort total sales per month(for 12 monts) on the current month data( current month is chosen by user using a prompt). So do i need to create 2 report variables as discussed above and sort on those variables?

Code Month1 Month2 …
abc 1000 980…
def 1100 1000…

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

#7

can anybody please tell me how to create these report variables in Variable editor? When i try the given code, i get syntax error. Please help me out.

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

#8

Why cant i use this code to get the amount for the current month? User enters current month number as well as year.

= <AMOUNT> WHERE ((MonthNumberOfYear(<date>)=UserResponse("Query 1 " ," month number") ) &amp; Year( <date>) = UserResponse("Query 1 ","year number")

I’m getting syntax error at MonthNumberOfYear function? Any clues?

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

#9

  1. MonthNumberofYear returns data type NUMBER, UserResponse returns data type STRING/CHARACTER. You can only compare variables of the same data type.

  2. The WHERE clause in your code only accepts a constant on the right side of the equation, for example:

SUM (<Revenue>) WHERE ( <Year> = 2004)

Take a look at this entry in FAQ Reporter for a work around.

Andreas :de: (BOB member since 2002-06-20)

#10

Thanks ANdreas. But i still have a doubt: How do i determine current date from the user response to sort on Customer on Current month data. can i have something like this:

mon = tonumber(userresponse("Query 1","month")))>1
year = tonumber(userresponse("Query 1","year")))>1

to make them return boolean.

And then:

=Amount where (<mon> =1 &amp; <year> = 1)

(Here i’m getting syntax error on )

And then:

=Rank(<customer>,<amount>)

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

#11

In your WHERE clause do not use &, but AND, for example:

= SUM (<Amount>) WHERE ( <mon> = 1 AND <year> = 1)

In addition your code for and seems to have too many closing parenthesis’, try:

mon = toNumber (UserResponse ("Query 1","month")) > 1 
year = toNumber (UserResponse ("Query 1","year")) > 1

Andreas :de: (BOB member since 2002-06-20)

#12

Thanks again Andreas. But I’m still getting error at :
= SUM () WHERE ( = 1 AND = 1)

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

#13

You have to follow some basics regarding troubleshooting:

  1. What is the exact error message?

  2. Start with a “smaller” formula and add to it piece by piece. This will allow you to identify where the problem is.

Andreas :de: (BOB member since 2002-06-20)

#14

Error message is
Syntax Error : DMB0007 at

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