empno empname dept sal
----------- ------------------------------ -------------------- -
1 mike HR 4000
2 John HR 2000
3 Patty HR 3500
4 Carly HR 2200
5 ABC HR 1000
6 DEF HR 4500
7 Ben HR 2200
8 HGI IT 4000
9 GOG IT 2000
10 LILY IT 3500
11 Cathy IT 2200
12 Paul IT 1000
13 Ben IT 4500
14 Ben Fin 2200
15 HGI Fin 4000
16 GOG Fin 2000
17 LILY Fin 3500
18 Cathy Fin 2200
19 Paul Fin 10800
20 Ben Fin 4500
How will i get Maximum and Minimum 3 salaries of EACH DEPARTMENT
ie i want to know who are the employees who are drawing top 3 max and min salaries in each department?
When I insert a row of data into a table via a stored procedure, the row SOMETIMES gets inserted at the bottom, and sometimes gets inserted at another random location (top, midding, etc). I am using Microsoft SQL Server.
Dea anybody know the cause of this? Or how to ensure that the row gets inserted at the bottom?
I am a novice SQL Server user, but this seems like a simple problem that the more advanced users solved long ago. Thanks in advance!
Is there a way to sort records the following way.Original Table:KeyField Column 11 02 5003 2004 3005 0To this sorted tableKeyField Column 13 2004 3002 5001 05 0so that the numbers in column 1 are sorted asc but it treats a 0 asthe largest number. We need the first number greater than 0 to befirst in the table.
neo writes "hi i am a beginner at sql this query confuses me...
q:A LIST OF SERVICE TRANSACTIONS(WITH A FULL DESCRIPTION OF THE SERVICE) ON A PARTICULAR DAY ALONG WITH THE NAMES OF THE CUSTOMER AND THEIR ROOM NUMBERS.THE TOTAL AMOUNT TRANSACTED NEEDS TO BE DISPLAYED AT THE BOTTOM OF THE LIST
REQD TABLES ARE SERVICES,TRANSACTIONS AND RESERVATIONS...
all i can do was this answer...
SELECT T.TRANID,S.*,R.FNAME,R.LNAME,R.ROOMID FROM SERVICES AS S ,RESERVATIONS AS R,TRANSACTIONS AS T WHERE S.SERNO=T.SERNO AND T.CUSTID=R.CUSTID ORDER BY T.TRANID COMPUTE SUM(S.SERCHAR)
is there a better way to display in the same table the sum too?"
I have a results set that is sorted on LineNum. Not all items have aLineNum (some are null). I want my result set to return all items inASC LineNum order and then list all items with NULL LineNum at the endof the results. Any Suggestions?Thanks.
I know the function of being able to set a section of a report to print at the bottom of the page, but does anyone know any work arounds for this? I have a main report (statement) which ive done a work around, to include the details i need at the bottom of the page into the footer, but then i have a second report (invoice) that needs to be included as subreport, which also needs infomation at the bottom of the page, but as its a subreport, sql reports does not allow for footer/header infomation in a subreport.
Unfortuantly i can not split the two reports out as they need to be printed together.
Any help/recomendations would be greatly received.
How can you set the text direction to BT-RL 'bottom to top' in SRS? There is control for TB-RL but not BT-RL. Need this for SRS label printing application.
Hello all, I'd like to know if anyone has been able to get SQL Server 6.5 to send mail to a MAPI mail server other than Exchange. I would like to implement this functionality so that my group and I can better monitor the results of backups and other scheduled tasks but we do not have Exchange within our site. We currently use Netscape's mail server so it would be GREAT if someone with this knowledge could reply with any tips or URLs or whatever. Just something to get me heading in the right direction. Thanks for your time.
I keep getting extra space at the bottom of each page on my report when it is deployed to the web server but I don't get this extra space on the report server. Does anyone know if this is in the report layout or if the problem is in the web page itself?
I write few lines to do a bottom-up calculation, with 'fetch last' and 'fetch prior'.
It seems that the condition 'WHILE @@FETCH_STATUS = 0' does not work when cursor arrives at the first line, as there is an error message:
'(1 row(s) affected) 6255.84 (1 row(s) affected) Msg 16931, Level 16, State 1, Line 18
There are no rows in the current fetch buffer.
The statement has been terminated.'
how to fix the error?
Here is my code:
DECLARE @lastprice real DECLARE @updatedprice real DECLARE @updatedRe real DECLARE @updatedAUX real SET @lastprice = ( SELECT Close_P from #ClosePrice where #ClosePrice.DateTD = (SELECT MAX(#ClosePrice.DateTD) FROM #ClosePrice) )
Ok so this is more then likely very easy for most. But for me it's hard. I have a stored proc that I've been able to get to retun just the top 10 rows and have been able to order it asending. But it will only return the top 10 and I need to bottom 10 of the selection.
set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO
ALTER proc [dbo].[proj_ListFiles](@project_id int) as select top 10(@project_id) id, project_id, filename, contenttype, length from proj_file where project_id=@project_id
How can I make it return to bottom 10. On a side note about this, when it returns the recordset they all have the 'id' of the 'project_id' that I selected. Not that the side note really bothers me as it will not be returning the ID's to be visible.
I'm having a relatively big issue with printing my check report. The report continues, time after time, and regardless of the margins I choose for the report, to be displaced an additional 1" up from the bottom of the page. The printing method is through our newly designed application which sends the emf rendering to the printer. We have gone through the liberty of modifying the actual print document in VB with margins set at 0,0,0,0 (L, R, T, B) and have paid close attention to the printer's maximum printing area. We have come to the conclusion that the problem is coming from reporting services. In addition to the extra margin space on the bottom of the page, there is also a slight yet noticeable shrinking of the page. Despite the fact that those are trade symptoms of another issue after the emf rendering we have modified every possible function of the printdocument and printer settings. So we continue to push the question, is this reporting services? We are fairly certain. I'm asssuming this is a bug in SSRS 2005, yet, I have yet to hear of any similiar case. This is our VS version info just incase:
Microsoft Visual Studio 2005 Version 8.0.50727.762 (SP.050727-7600) Microsoft .NET Framework Version 2.0.50727 Installed Edition: Enterprise Microsoft Visual Basic 2005 77642-113-3000004-41917 Microsoft Visual Basic 2005 Microsoft Visual C# 2005 77642-113-3000004-41917 Microsoft Visual C# 2005 Microsoft Visual C++ 2005 77642-113-3000004-41917 Microsoft Visual C++ 2005 Microsoft Visual J# 2005 77642-113-3000004-41917 Microsoft Visual J# 2005 Microsoft Visual Studio Tools for Office 77642-113-3000004-41917 Microsoft Visual Studio Tools for the Microsoft Office System Microsoft Visual Web Developer 2005 77642-113-3000004-41917 Microsoft Visual Web Developer 2005 Visual Studio 2005 Team Edition for Developers 77642-113-3000004-41917 Microsoft Visual Studio 2005 Team Edition for Software Developers Crystal Reports AAC60-G0CSA4B-V7000AY Crystal Reports for Visual Studio 2005
Microsoft Visual Studio 2005 Premier Partner Edition - ENU Service Pack 1 (KB926601) This service pack is for Microsoft Visual Studio 2005 Premier Partner Edition - ENU. If you later install a more recent service pack, this service pack will be uninstalled automatically. For more information, visit http://support.microsoft.com/kb/926601 SQL Server Analysis Services Microsoft SQL Server Analysis Services Designer Version 9.00.1399.00 SQL Server Integration Services Microsoft SQL Server Integration Services Designer Version 9.00.1399.00 SQL Server Reporting Services Microsoft SQL Server Reporting Services Designers Version 9.00.1399.00
I'm planning to talk to MS if nobody recognizes these symptoms. Any ideas would be greatly appreciated! Thanks!
UPDATE (12/20/2007 1:43:07 pm): We have made a small break-through discovery. We found that that if we convert our report using a pdf rendering that the additional 1 inch added to the bottom margin is no longer there. So, pdf rendering would be an option if we became more desperate. However, this is too time consuming and we would much rather have the report directly sent to the print spooler instead of loading then spooling. Does anybody know what is going on with this apparent conflict between SSRS 2005 and our emf renderer?
I have to display Top 5 values in 'Green' and Bottom 5 in 'Red' out of some data.
Ex: my data is as below
ID Marks 1 98 2 62 3 89 4 97 5 92 6 67
[Code] ....
Expected result is : if the marks are >= 80 then 'Green' (pick only top 5) if marks are <= 70 then 'Red' (pick bottom 5) else 'Black'
As per my data even though we have values >=80 for more than 5 students display only top 5 in Green and same rule applies for Bottom 5 that means
ID 's : 1 ,3 , 4 , 5, 9 and their marks in Green (pick only Top 5 ) even though we have 6 students who got >=80 ID's : 2 , 6, 16 , 15 and their marks in Red (Bottom 5 but we have only 4 students who got <=70 )
I'm a new SQL Reporting user and could use some guidance.
Here is a simple explanation of my application: imagine a report which prints out a grade report for each of 10 students. Depending on how many notes a teacher makes on the student's individual grade report, each student's report might be 1 or 2 pages long.
To create this report I just used a List report item and laid out a typical grade report and filled in the appropriate fields (name, teacher, etc.) from my dataset query.
When I run the report, it is running great and the students and their grade reports all print out.
The problem I am running into is I want to make sure each student's name is on page 2 if the grade report for that student goes more than 1 page (so pages aren't separated from each other or mixed up). I tried using a Report Footer, but that just put the first student's name at the bottom of every page. I could put a textfield at the bottom the list control but that would just appear at whatever length page 2 ended up being (basically you could have a "footer" at mid-page if the whole list only took up 1.5 pages).
How can I create a footer for each set of pages? Basically this would be a "footer" to the list control itself.
I have a report (bills of lading for shipments) that uses a table to organize the data coming in and groups by customer. Each customer grouping has a header, body and footer in the table. A print run might generate 100 bills of lading, each between 1 and 4 pages long.
I have marked the group to force a new page when each new group occurs.
As it is the report runs beautifully EXCEPT that the footer prints wherever it happens to end up -- midway down the final page of the bill and looks quite goofy.
What I really need (and my client is really surprised that SSRS won't me) is to print the group footer at the bottom of the final page of each bill of lading.
Bills of lading may be several pages per customer, so until the group footer is hit, I don't want any footer info at all. As I said, it all works fine except for the final page of each bill, where the group footer occurs whereever it has ended up.
Jim
12/4/07
This post didn't get much of a response. So perhaps I should amend it -- If what I am trying to do is impossible, what would be the preferred way?
I am designing a bar chart in which five value comes .if all values comes then graph is working fine but my concern is when i have single value then in that case bar gets created on the middle of chart i want to generate the single bar on bottom of the chart.
I'm still a bit new to T-SQL and am wondering how to get various LOWEST values from a table, rather than TOP values. There does not appear to be a keyword opposite of TOP in T-SQL.
My real problem is this: I know how to get the 2nd highest value, third highest value, etc...but how does one get the 2nd lowest value, third lowest value, etc?
I'm having an interesting problem. In my report, I use a table to show my data set. The borders of the table are giving me a hard time...
The problem is, I'm not being able to display the bottom border for a row, but only at the end of the page, causing the table containing multi paged report data to be seen inside a box. Just to be clear;
I don't want to display borders of each row I don't want to display table footer in each page (Only on last page) I've already tried to display page footer, top border. Its not the answer :D
Although I am setting the Border Style for each & every cell and /or Merge cells ,But it is not reflected in the Reports Pre-view for Bottom ,Top & Right Sides in the Reports?Any clue why this is happening & for removing this & getting the right results would be helpful .
Relative newb to SSRS here, but the answer to this question evades me; answers and insight are appreciated.
Report in question is an invoice form. It requires an absolutely bottom-of-page aligned footer that has databound elements.
This is so that whatever page that footer finally appears on will print in such a way that the address will align in a windowed envelope.
Ironically, Books Online gives this exact scenario in explaining headers and footers in SSRS, but they cleverly don't explain how an absolutely bottom-of-page-aligned and data-bound footer can be made to happen. Headers at absolute page top is obviously no problem. Footers at page bottom, not so much.
So, this is not a "page footer"--page footers are employed in the body. Also this footer is databound, so a page footer as it's known in SSRS is out the window anyway.
Most of the time this will print on a single page, but if it breaks to multiple pages, that footer needs to go all the way to the absolute bottom.
I grasp that the "report footer" for SSRS is just what appears at the end of any repeating controls that you've implemented in your body. Because SSRS uses this kind of repeating-control based idiom rather than a section-based idiom as Crystal does, this kind of (what I would consider very basic) positioning control is looking fairly impossible right now.
Among what I've tried:
--Page footer (can't; databound)
--Specifying a page break after the pre-footer controls, and/or a page break before the controls that make up the footer in their properties. This leads to unpredictable results with blank printed pages (as many as 8 for what previews as a 2-page report, how silly is that?).
--Putting in a page-height rectangle as part of the footer (with and without the page breaks mentioned above), with the idea of forcing a basically blank page at the end of the report so that the footer will go to the bottom. SSRS will go ahead and break the page anyway on long elements like that, which again leads to the "footer" being printed in the middle or top of the final page, or whereever it happens to fall.
I may be having to explain to my client that you can't get there from here, and they may have to redesign their report. Does anyone have any insight?
Report in question is an invoice form. It requires an absolutely bottom-of-page aligned footer that has databound elements. This is so that whatever page that footer finally appears on will print in such a way that the address will align in a windowed envelope. Ironically, Books Online gives this exact scenario in explaining headers and footers in SSRS, but they cleverly don't explain how an absolutely bottom-of-page-aligned and data-bound footer can be made to happen. Headers at absolute page top is obviously no problem. Footers at page bottom, not so much.So, this is not a "page footer"--page footers are employed in the body. Also this footer is databound, so a page footer as it's known in SSRS is out the window anyway.
Most of the time this will print on a single page, but if it breaks to multiple pages, that footer needs to go all the way to the absolute bottom.I grasp that the "report footer" for SSRS is just what appears at the end of any repeating controls that you've implemented in your body. Because SSRS uses this kind of repeating-control based idiom rather than a section-based idiom as Crystal does, this kind of (what I would consider very basic) positioning control is looking fairly impossible right now.Among what I've tried:
--Page footer (can't; databound) --Specifying a page break after the pre-footer controls, and/or a page break before the controls that make up the footer in their properties. This leads to unpredictable results with blank printed pages (as many as 8 for what previews as a 2-page report, how silly is that?). --Putting in a page-height rectangle as part of the footer (with and without the page breaks mentioned above), with the idea of forcing a basically blank page at the end of the report so that the footer will go to the bottom. SSRS will go ahead and break the page anyway on long elements like that, which again leads to the "footer" being printed in the middle or top of the final page, or whereever it happens to fall.
I may be having to explain to my client that you can't get there from here, and they may have to redesign their report.
We have the customer requirement to display the footer of a SSRS Report fixed at the absolute bottom of a DIN A4 format page. The footer contains information like company address and stuff.
I searched quite a while on this topic and only found workarounds for SQL Server 2005 with Custom Code in the SSRS Report to calculate the size of the body content and then insert some empty lines to get the space needed to push the footer to the bottom of the page. But this won't work in SQL Server 2012. And I wasn't able to figure out how to achieve this yet.
I can't find any explanation why is it string1 and string 2 of the footer section of my report displayed separately from the expression3 which is aligned on it and the rest of the object on the second page.
The expected design is that all Footer items should be displayed together of whether it is placed on the first page or on the last page.
As a workaround of this, I converted string 1 into an expression (Added = and enclosed the string with double quote).. As a result, all of the items in the Footer section are now placed together on the last page of the report.
I also remember one of the issue I encountered before where the Footer items where placed together on the first page and still have space at the bottom of the page, but then expression 6 is forced to display (alone) on the last page of my report.
I can't find any discussion related to this, I wish somebody could give me an idea why RS behaved like this.
