26 Ranking Data In Queries - Webiworx

7m ago
22 Views
1 Downloads
1.06 MB
5 Pages
Last View : 10d ago
Last Download : 2m ago
Upload by : Milo Davies
Transcription

Ranking Data in Queries26 Ranking Data in QueriesThis session describes how to query a database to request data such as:Top 5 customers based on Customer Spend in this yearBottom 10 products based on Quantity Sold in last quarterDatabase Ranking calculates the ranking based on a measure and then filters the data to only returnthe records satisfying the request, therefore the above queries will be processed by the database as:Top 5 customers based onCustomer Spend in this yearOnly the Top 5 customers are returnedbased on Customer Spend.Bottom 10 products based onQuantity Sold in the lastquarterOnly the Bottom 10 products are returnedbased on Quantity Sold.NOTES1. Database Ranking is only available if your database supports this type of query.database does not support Database Ranking then thein the Query Panel will be disabled.If your‘Add a database ranking’ button2. To demonstrate this functionality the ‘eFashion’ data has been migrated to Oracle and a newuniverse has been created to work with Oracle instead of Microsoft Access. This universe iscalled ‘eFashion Oracle’ in our examples. If you do not have ‘eFashion Oracle’ available toyou then hopefully you can still follow the examples shown to get an understanding of‘Database Ranking’.3. After selecting an object and then clicking on the ‘Add a database ranking’ button adds theDatabase Ranking Filter into the Query Filters pane:For Reference, Web Intelligence uses the SQL-99 Rank function in ranking SQL.NOTEFor ‘eFashion Oracle’ universe, further details and instructions can be found atwww.webiworx.com. Look for information on ‘SAP BusinessObjects Web IntelligenceTraining Course’ on the 'Downloads' page.425

Ranking Data in Queries26.1 Parameters for Database RankingThe Database Ranking Filter has a number of parameters as shown below:This is the Ranking Order.You can rank based on avalue (e.g. Top or Bottom10 based on [Quantitysold]).Ranking Dimension, e.g.if the dimension is [State]and the ranking is Top 10then the Ranking Filterreturns the top 10 states.For example, a ranking for[State] with a condition of[Christmas period] restrictsthe ranking to use dataavailable for the seasonalsales around Christmas.OrYou can rank based on apercentage of a value (e.g.Top or Bottom 20% of[Quantity sold]).Number of records toreturn, for example the Top10 records when RankingOrder is Top or Bottom.OrPercentage when RankingOrder is % Top or %Bottom. In this case thenumber of records will varyas it will be based on therecords that contribute tothepercentageyouspecify.Where condition (optional)is used to specify additionalrestriction(s) on the valuesreturned in the databaseranking.Based on (a Measure) bywhichtheRankingDimension is ranked.e.g.if the measure is[Quantity sold] and thedimension is [State], WebIntelligence ranks states bythe quantity sold in eachstate.Ranked by (optional) isused to specify additionalcalculation context for theranking.e.g. if the Database RankingFilter definition is:Top 3 [Store name] Basedon [Quantity sold] Rankedby [State] Where [Year] 2005thenWebIntelligenceisrequesting the database toreturn the Top 3 stores ineach state for 2005.426

Ranking Data in Queries26.2 Using Database Ranking Filter in QueriesLet us take the example we used in Sub-Queries and adapt it to use database ranking instead.In the Sub-Query we demonstrated how to:Show data for all years for the States that have sold less than 6000 (in quantity) in this year, i.e.show data for all years for the states Massachusetts, Colorado and Florida.We specified a finite limit (6000), so in the future we could run this query to find all states thathave sold less than 6000 (say in January of next year) or all states have sold less than 6000(say in September of next year). Therefore, in January we could get all states being reportedand in September we would have no data at all!It would be better if we had a dynamic query that returned the Bottom 3 states based on the[Quantity sold] (irrespective of the actual number).In the Sub-Query example, we first ran a queryfor [This year] that was sorted descending on[Quantity sold].From this we were able to see the 3 states thathad sold less than 6000 in this year.We then modified the query to show all years forthe states that have sold less than 6000 (inquantity) in this year, i.e. show data for all yearsfor the states Massachusetts, Colorado andFlorida.To do this we edited our query to include a SubQuery as shown below.427

Ranking Data in QueriesWe want to keep the same result objects as the sub-query example but we will also use the DatabaseRanking Filter instead of the Sub-Query Filter.NOTE – Use the ‘eFashion Oracle’ universe for this example.1. Add the following objects into the ‘Result Objects’ pane, then click on [State] object and then clickon ‘Add a database ranking’ button.Web Intelligence will add the database ranking filter into the Query Filters pane.2. For our example we want to specify Bottom 3.3. Drag [Quantity sold] and drop into the area labelled ‘Drop a measure here’ in the database rankingfilter.The Database Ranking Filter is nowrequesting Bottom 3 [State] Based on[Quantity sold].428

Ranking Data in Queries4. For our example we also want to add in a filter to the database ranking filter itself, so drag and drop[This year] into the database ranking filter.The Database Ranking Filter is nowrequesting Bottom 3 [State] Basedon [Quantity sold] in [This year].5. Running the query shows data for all years for the Bottom 3 [State] in [This year] Based on[Quantity sold].Our results are the same as the SubQuery example (shown opposite), exceptwe now have a query that is dynamic inthat it will always show data for theBottom 3 states in this year (irrespectiveof the values for [Quantity sold]).429

'Database Ranking'. 3. After selecting an object and then clicking on the 'Add a database ranking' button adds the Database Ranking Filter into the Query Filters pane: For Reference, Web Intelligence uses the SQL-99 Rank function in ranking SQL. NOTE For 'eFashion Oracle' universe, further details and instructions can be found at

Related Documents:

Similar to SQL, WQL has a set of keywords & operators and supports three types of queries. WMI Query Types WMI supports three types of queries: 1. Data Queries 2. Event Queries 3. Schema Queries Data Queries This type is the simplest form

Moscow International University Ranking 2020 About the Project Moscow International University Ranking is a fundamentally new academic ranking, the fi rst to evaluate all the three key university missions: education, research, and interaction with society. The ranking uses a number of new criteria calculated on the basis of objective data,

In Cognos Report Studio, it is possible to include multiple queries on a report. In the Applicants report below, we will add a query for Admissions information. Multiple Queries Page 2 of 21 Revised July 2012 Follow these steps to create a report with multiple queries: 1. Open the report . Multiple Queries

SQL as Data Manipulation Language (DML) Insert and update data Simple SQL queries Advanced SQL queries Views. 2 FU-Berlin, DBS I 2006, Hinze / Scholz SQL / DML: Overview Insert, update, delete data Query data . SQL / DML: Simple queries More examples: All movies not by Lucas (SELECT * FROM Movie)

This document covers information on the essential HR Reports and Queries. A complete list of available queries and reports can be found in the document: Available Reports and Queries (PDF). Security All data in PeopleSoft HR is filtered through individual security permissions. These permissions control access to menu options as well as data.

PeopleSoft Query provides the following different types of queries: User queries. Create and run queries to retrieve data from the database directly from Windows-based Query Designer, or the web-based Query Manager/Query Viewer applications. These are the queries that will be discussed

Rave Feature Queries lRave allows for both system-generated and manual-generated queries l System-generated queries fire at the time a form is saved (system field edits and configurable edits) l Manual queries are created by selected roles and can be done at any time l Data Manager l Central Monitor

A/ B. COM - SEMESTER I – GENERAL ENGLISH (2019- 20) University Paper Style (total 4 questions, 70 marks, 2.30 hours) Unit/s Topic/s No MarksQuestion style I Lessons Beautiful Minds (Gujarati Medium) Pinnacle (English Medium) Q. 1. 1 to 3 (a) Answer in brief - 3/5 (b) Write a short note - 1/3 (09) (08) II Q. 2. Poems 1 to 3 (a) Answer in brief - 3/5 (b) Write a short note - 1/3 (09) (08) III .