473,324 Members | 2,166 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes and contribute your articles to a community of 473,324 developers and data experts.

Reporting Function

debasisdas
8,127 Expert 4TB
REPORTING FUNCTIONS ALLOW THE EXECUTION OF VARIOUS AGGREGATE FUNCTIONS AGAINST A RESULT SET.UNLIKE WINDOWING FUNCTIONS THESE CAN'T SPECIFY LOCALISED WINDOWS AND THUS GENERATE THE SAME RESULT FOR EACH ENTIRE PARTITION.REPORTING FUNCTION GENERATES SAME RESULT AS A WINDOWING FUNCTION WITH AN UNBOUNDED WINDOW.
Sample Ex#1
-------------------------
Expand|Select|Wrap|Line Numbers
  1. SELECT HIREDATE,SUM(SAL) "SALARY",SUM(SUM(SAL)) OVER (ORDER BY HIREDATE ROWS BETWEEN
  2. UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) "TOTALSALARY" 
  3. FROM EMP 
  4. GROUP BY HIREDATE 
  5. ORDER BY HIREDATE;
Sample Ex#2
-------------------------
Expand|Select|Wrap|Line Numbers
  1. SELECT HIREDATE,
  2. SUM(SAL) "SALARY",
  3. SUM(SUM(SAL)) OVER (ORDER BY HIREDATE ROWS BETWEEN
  4. UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) "WINDOWSALARY" ,
  5. SUM(SUM(SAL)) OVER() "REPORTINGSALES"
  6. FROM EMP GROUP BY HIREDATE ORDER BY HIREDATE;
THE EMPTY PARENTHESES AFTER THE OVER CLAUSE FOR THE COLUMN INDICATES THAT THE ENTIRE RESULTSET SHOULD BE INCLUDED IN THE SUM,WHICH HAS SAME EFFECT AS USING AN UNBOUNDED WINDOW FUNCTION.REPORTING FUNCTIONS ARE USEFUL WHEN BOTH DETAIL AND AGGREGATE DATA IS REQUIRED.

Sample Example #3
==================

Expand|Select|Wrap|Line Numbers
  1. SELECT HIREDATE,SUM(SAL) "SALARY",
  2. SUM(SUM(SAL)) OVER() "REPORTINGSALES"
  3. FROM EMP
  4. WHERE JOB='MANAGER'
  5. GROUP BY HIREDATE ORDER BY HIREDATE;
LIKE RANKING FUNCTIONS,REPORTING FUNCTIONS CAN ALSO INCLUDE PARTITION BY CLAUSE TO SPLIT THE RESULT SET INTO MULTIPLE PIECES,ALLOWING MULTIPLE AGGREGATIONS TO BE COMPUTED ACCROSS DIFFERENT SUBSETS OF THE RESULT SET.

Sample Example #4
==================

Expand|Select|Wrap|Line Numbers
  1. SELECT DEPTNO,EMPNO,SUM(SAL)"SALARY",SUM(SUM(SAL)) OVER(PARTITION BY DEPTNO) "DEPTSAL"
  2. FROM EMP
  3. GROUP BY DEPTNO,EMPNO
  4. ORDER BY DEPTNO,EMPNO
  5.  
RATIO_TO_REPORT
---------------------------------------
Sample Ex#5
=============
Expand|Select|Wrap|Line Numbers
  1. SELECT DEPTNO,
  2.  EMPNO,
  3.  SUM(SAL)"SALARY",
  4.  ROUND(SUM(SAL)/SUM(SUM(SAL)) OVER(PARTITION BY DEPTNO),2) "DEPTPERCENT"
  5.  FROM EMP
  6.  GROUP BY DEPTNO,EMPNO
  7.  ORDER BY DEPTNO,EMPNO;

Sample Ex#6
=============
Expand|Select|Wrap|Line Numbers
  1. SELECT DEPTNO,
  2. EMPNO,
  3. SUM(SAL)"SALARY",
  4. ROUND(RATIO_TO_REPORT(SUM(SAL)) OVER(PARTITION BY DEPTNO),2) "DEPTRATIO"
  5. FROM EMP
  6. GROUP BY DEPTNO,EMPNO
  7. ORDER BY DEPTNO,EMPNO;
Jul 13 '07 #1
0 4612

Sign in to post your reply or Sign up for a free account.

Similar topics

5
by: steve | last post by:
Hi, In my script (phpnuke), whenever there is access to database, there is this line of code: message_die(GENERAL_ERROR, ’some error msg’, ’’, __LINE__, __FILE__, $sql); Is there a more...
8
by: Woody Splawn | last post by:
I am asking this question here because I asked this question in the Reporting Services Newsgroup and did not get an answer. Does anyone know if Reporting Services is intended to work in a...
0
by: Joshua V. | last post by:
We are trying to implement Windows Error Reporting from within our VB.Net application. Basically we want to replace the standard unhandled exception message we use with the Windows Error Reporting...
2
by: Martin Widmer | last post by:
Hi guys I am looking for the best way to generate new reports with reporting services for SQL server 2005. The reports will be generated programmatically from a .Net VB application. So far I see...
2
by: RdS | last post by:
Hello, I use sourcesafe and vb 2003 for my dev environment. on the sourcesafe server I also have sql2005 and reporting services installed. The web app references this sql server for db. When...
0
by: YellowFin Announcements | last post by:
Whitepaper: "Yellowfin Reporting" enables Embedded Business Intelligence -------------------------------------------------------------------------------- Embedded reports are a standard...
2
by: petermichaux | last post by:
Hi, It seems like determining element position in a web page is a difficult task. In the position reporting source code I've looked at there are special fixes for at least some versions of...
8
by: Paul Furman | last post by:
How do I turn off MySQL error reporting? I set error_reporting(0); but that doesn't seem to be working.
0
by: Dan.Diasio | last post by:
Recently I moved from Crystal reports to the Reporting Services module. In Crystal, I was able to use the TOP N function to report only the top 15 customers in a particular query, and aggregate...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.