on 04-06-2011 6:35 PM
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.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
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.
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.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
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
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.
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.
User | Count |
---|---|
84 | |
10 | |
10 | |
10 | |
7 | |
6 | |
6 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.