WEBI Database ranking problem

[Former Member]

Hi,

I have a problem with Add Database ranking feature in query filter in WEBI rich client.

I have 100 products and would like to pull only top 10 products onto my report based on number of sales (measure). I am using SQL server 2008. But when I use this feature I am only getting a table with only top 4 products in my report. When I increase the number to sat 50 or 60 (top 50 products in my query) I am getting top 10 now.

Is it a bug which can be fixed with any fix pack. Like I get only top 4 products if I choose top 10 in the ADD database ranking and I get 10 if I use any number greater than 63 in the query.

Eg:

Case1: Query: Database ranking : Top 10; Product (Dimension); based on Sales(measure)\

Result: Table with only top 4 products sorted in descending order instead of 10 products

Case2: Query: Database ranking : Top 63; Product (Dimension); based on Sales(measure)\

Result: Table with only top 10 products sorted in descending order.

Right now it meets my criteria of getting top 10 products when I use top 63 in the query, but I think there is something wrong with this work around. Please reply me with any solution.

Thanks

Sudhir.

[former_member142702 · Accepted solution]

Did you try to run the SQL statement generated by your WebI report directly against the database?

How many rows do you get back?

What kind of data source do you access?

Regards,

Stratos

[former_member793810 · Accepted solution]

Hi,

Is it happening only in this report or you have tried with some other reports and go the same issue?

What SP and FP you have and in what version of the product?

Regards,

Bashir Awan

[Former Member · Accepted solution]

Hi,

@ Stratos

I ran the SQL generated directly against the data source. I still get the same result (only 4 records are pulled when top 10 are choosed).

My Data source is MS SQL Server 2008.

@Bashir Awan

I tried to use same universe to create another new report, and I still get the same result.

I am using the Business Objects XI 3.1, no fix packs installed yet (basic installation). We recently moved into setting up Business Objects for our enterprise. I guess the product is some what buggy with no Fix packs applied.

Environment: Business Objects XI 3.1

version: 12.3.0

Build:601

Thanks

Sudhir.

[former_member142702 · Accepted solution]

Can you post the SQL statement here?

Regards,

Stratos

[Former Member · Accepted solution]

Initially I used an example of Product and Sales to explain, which are in reality SCREEN.NAME and AGG_SCREEN.ACTIVE_TIME_SUM respectively as shown below.

SELECT

sum (REP.dbo.AGG_SCREEN.ACTIVE_TIME_SUM),

REP.dbo.SCREEN.NAME

FROM

REP.dbo.AGG_SCREEN INNER JOIN REP.dbo.SCREEN ON (REP.dbo.AGG_SCREEN.SCREEN_ID=REP.dbo.SCREEN.ID)

WHERE

REP.dbo.SCREEN.NAME IN

(

SELECT

View__1.Column__1

FROM

(

SELECT

REP.dbo.SCREEN.NAME AS Column__1,

RANK() OVER( ORDER BY REP.dbo.AGG_SCREEN.ACTIVE_TIME_SUM DESC ) AS Rk__1

FROM

REP.dbo.AGG_SCREEN INNER JOIN REP.dbo.SCREEN ON (REP.dbo.AGG_SCREEN.SCREEN_ID=REP.dbo.SCREEN.ID)

) View__1

WHERE View__1.Rk__1 <= 10

)

group by REP.dbo.SCREEN.NAME

Note: text in bold represent changes I made to SQL generated to sum and group by when used against database.

[Former Member · Accepted solution]

Hi,

Still unable to resolve this issue.

Can I create a Object in Universe such that when I use in my WEBI I get only top Ten Applications based on Active Time. Also the Top ten should change with the prompts at WEBI query level.

For Example:

Prompt on Time period when given should pull top ten application for that particular period of time only.

Can anybody send me a example of the syntax of the SQL for creating an object.

Another question is can I not use Dimension object from one table and measure object from another table where both the tables at joined at Universe level ?

Should I create a Derived table fro resolving this?

Thanks,

Sudhir.

[Former Member]

Looks like this is an issue with Dimension associated with ranking. Try add ranking from the Alert button in the tool bar. Also try Ranking in other reports to check whether its an issue with SP or FP.

Thanks,

Rajesh

[Former Member]

Hi Rajesh,

Can explain how to apply ranking using Alerters. My requirement is to display data in a chart with top 10 application and top 10 users. So my table has Application, Users, Active time(measure).

I can only use rank on one dimension for a table. As now I have to filter the top 10 user data from top 10 applications based on Active time. My approach was to first apply Database rank on Application at Query and later apply report level ranking on User in the table. This gives me 10 * 10 cross tab table, which I would like to convert into char (Bar).

If the ranking at Database level (query panel) is not possible please suggest me any work around.

Thanks

Sudhir.

[Former Member]

Hi Sudhir,

Applying ranking at the database level would be the best option. But if that doesn't work try this.

1. Create a ranking for Application dimension first.

1. After receiving this resultset, now create a variable Rank_users as Dimension with the formula =Rank(L01 Users;<Keyfigure>;Bottom)

2. Click on the User Column and click on Report Filter button on the leftmost corner of the tool bar.

4. Drag Rank_users to report filter and Select constant radio button with condition , Rank less than or equal to 10.

Now the output should be only top 10 users.

Try this and let me know if it works..

Thanks,

Rajesh

[Former Member]

Hi Rajesh,

Thanks for your suggestion of using Ranking and Filter using Rank object. But I would like to make a small correction in Rank formula order, Measure object should be defined first and then comes Dimension Object. And also in my case I used Rank function on Application and Ranking in table on User, logically the other way around gives wrong results (which is not as per requirement).

Finally I would still like to know the reason behind my Database ranking not working in my WEBI Rich client. I would like to know if anyone is familiar with any Fix pack that may have solved this issue. I would like to add to my previous posts that when I used SQL server 2005 I was able to get Database ranking working fine, but started facing this error when I use SQL server 2008 as data source.

Thanks

Sudhir.

[Former Member]

Hi Sudhir,

Sorry about that, I always use the formula bar, so was not right when i typed it.

FYI! You can also use runnincount(<Keyfigure>) formula, which is much more simpler than the other one.

Thanks,

Rajesh

[Former Member]

Hi,

I just figured out that the approach is not working. For example

Case1: Using Application for Ranking and User with Rank variable and filter.

once I rank the based on the Application then add the Rank_User variable to the filter pane, the set of top ten applications is changing. This mean that the top 10 Applications pulled using Ranking is not working as it changing based on the Ran_user filter variable.

My requirement is to limit the number of users to top ten for the given set of top 10 applications. But when the list of top 10 applications changes the results are effected.

same issue with Case 2: Using Top 10 Users and creating a rank variable for application and using it in filter pane.

Coming back to Database ranking did anyone figure out whether this is an known issue with error due to Database connections or solved by Fix packs.

Thanks

Sudhir.