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 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'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
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.
I'm not sure how to go about this and need some help.I've got a data extract rather than a properly structure table in SQL.It looks something like below:Name: Item:John BallJohn RacketPaul BallJim GloveJim BallWhat sort of script can I run that will return each name once and thenthe Items in as many columns needed to list them all?I.e. so that it will look likeName: Item1 Item2John Ball RacketPaul BallJim Glove BallRegards,Ciarán
I have a table with a row to display the count of all records with a specified specialty. The count is displaying the correct value, however the row is being displayed 20 times as there are 20 records.
I would like this row to only be displayed once. I'd appreciate being pointed in the right direction as to how to do this.
When I query this field it returns a value between 0-10 . In my report I need to display this as a text(example: "2006-2007" if 6 was returned). Currently I'm using a Switch function. I was wondering is there is an easier way to do this using a lookup table or something like that. Thanks..
Got a question here and as I am no expert programmer, this should be easy for you gurus. I have this fairly generic code I've created where I return data from an SQL table in a DataList control. I want to take it to the next level and return only the last record in the table, but I am unsure of how to do that. Perhaps I shouldn't even be using a DataList control, I'm not sure.
Hye Friends, I'm using a DMX query to get some predictions out of my MiningModel
my DMX query is as follows :
SELECT predict([x SalaryPredictor].[Emp Gross],20),predict([x SalaryPredictor].[Emp Basic],20) From [x SalaryPredictor]
This query is returning me 2 objects of type expressions in my Dataset. The problem is when I try to drag these 2 fields in my Table i get a "#Error" value
I executed the query in 'Data' tab and found that data is getting returned is a tree like format where "Expression" is at top & values expected ar its child nodes.
I also found out that the returned objects are actually AdomdDataReader , but i'm not able to write a expression to get its values in my tables....
I want to display the values in the following format....
I like to get data from a signle table and arranged in hierarchical(hierarchy) order. What will be my sql script to be able to get the desired result shown below? Please include some explanation as too what script is doing..
Table Structure and Sample Data
Id ParentId Name Code DisplayOrder 1 null Group 1 G00001 1 2 null Group 2 G00002 2 3 1 Sub-Group 1 SG0001 1 4 2 Sub-Group 2 SG0002 1 5 3 Sub-Sub-Group 1 SSG001 1 6 null Group 3 G00003 3 7 3 Sub-Sub-Group 2 SSG002 2
Desired Result Id ParentId Level Name ExtendedName DisplayOrder 1 null 1 Group 1 Group 1 1 3 1 2 Sub-Group 1 Group 1 -> Sub-Group 1 1 5 3 3 Sub-Sub-Group 1 Group 1 -> Sub-Group 1 -> Sub-Sub-Group 1 1 7 3 3 Sub-Sub-Group 2 Group 1 -> Sub-Group 1 -> Sub-Sub-Group 2 2 4 2 2 Sub-Group 2 Group 1 -> Sub-Group 2 1 2 null 1 Group 2 Group 2 2 6 null 1 Group 3 Group 3 3
I have created a linked server using my local SQL2005. The linked server can be seen as a linked server, but the database can not been expanded to see the tables, stored procedures, views, and other details regarding the linked server. The only method that I have found that will allow me to see all the details is to use XP's Remote Desktop Connection to access the database. Has anyone else experienced this problem, if so, how did you resolve it?
I extracted data from a flat file using SSIS package and load them into destination table. One column contains no data. Data type of that column is varchar(9). I want to display "NULL" values in that column.
I used LOOK up transformation and used the following query.
SELECT PassThroughRouting = CASE PassThroughRouting WHEN 'NULL' THEN 'NULL' END FROM EPICWareTable
Hi, In my report, I have a table that displays all the data from query. I need to take the value of the 3rd row,2nd column of the table and display in a text box. I tried to do like:
=ReportItems("TextBox2").Value., where TextBox2 is the 3rd row,2nd column of the table. This returns just the first row value of the seleted data. But, I need the 3rd row value.
For some reason whenever I look at the SQL statement of a particular table, the table name displays twice.For example,SELECT * FROM State StateEven when I execute this statement, it still returns the correct results. It does this for all tables in this particular database. I also check another database and thoses display the table names in the SQL statements correctly. Does anyone know why the table name would display twice in a table inside of a particular database?
I am having problems displaying time values in my SSRS report. below is info. Tried expressions still does not work. I want the values to show what in the SQL Server table 00:00:00.82. I tried stored proc still does not work.
SQL Server table time value shown in milliseconds: 00:00:00.82
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!
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?
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.
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?
