Help Required Regarding Formatting Of Fields
Apr 18, 2007
Hello, I am using SQL SERVER Reporting Services 2005. I am having a formatting problem. The problem is that I hav a textbox in which numeric field is coming. I want to show the currency format of the Regional Setting of the user pc. I have set the Language setting of the textbox to Default. Now when I am changing the Regional setting from US to UK then in the criteria section the datetime criteria (calendar's value) is changed according to the UK setting, but in the report section the selected date time is not changed (coming in US setting). And the currecy format is also not changing to UK format. Any help ?Regards,Furqan
View 1 Replies
ADVERTISEMENT
Nov 14, 2006
Hi,
I am saving a couple of paragraphs of text into a varchar(max) field in a SQL database table. But when I try to use display the information again, then all the linefeed and return characters seem to be removed. How do I keep this text formating in the database field?
I am sure that I am missing something simple here, but I can not figure out what. Can someone please help with some advise?
Thanks
RegardsJan
View 6 Replies
View Related
Jun 7, 2001
I have a problem.... I have a TSQL procedure that calculates employee overtime based upon days 1-5 of week being "in-week", and days 6-7 oweek being week-end overtime. The resulting data is then mailed to dept managers. My problem: for some reason the calculated colums are being generated at a width of 40 char per column. I somehow need to reduce the width to 8-10 characters per colum (this will eliminate the wraping that I currently get in my e-mails)... Any help would be appreciated... Thanks..Tom
View 1 Replies
View Related
Dec 18, 2007
I have a "report" that is more like a form with a great many fields on it that are not arranged like columns. The data is displayed in text boxes. My problem is that the "fields" or text boxes do not always form a nice single line where they connect - at least in the displayed format (HTML4.0), but actually do in printed format. It looks like the fields/textboxes may have a varying width, and possibly height.
Is there any way to make sure that the width and height are static?
Thanks,
Menno Homburg
View 1 Replies
View Related
Jun 6, 2007
Dear All,
I have a problem formatting a field in Reporting Services (minutes to hours).
I have a field called duration which stores time in whole minutes only. I can format this into hours within mssql using the following:
cast(sn.duration/60 as varchar(5)) + ':' + RIGHT('0' + cast(sn.duration%60 as varchar(2)), 2)
But I need to have totals and average columns in my report, which means that the data must come through to RS in the minutes format so I can perform the calculations there.
I have the first part (I think!!):
=string.format("{0:0}",Fields!SalesTime.Value / 60) + ":"
But I cannot get the minutes part working!
Any help would be gratefully received.
Dan
View 3 Replies
View Related
Jan 28, 2008
I have just started using SQL Server reporting services and am stuck with creating subreports.
I have a added a sub report to the main report. When I right click on the sub report, go to properties -> Parameters, and click on the dropdown for Parameter Value, I see all Sum and Count fields but not the data fields.
For example, In the dropdownlist for the Parameter value, I see Sum(Fields!TASK_ID.Value, "AppTest"), Count(Fields!TASK_NAME.Value, "CammpTest") but not Fields!TASK_NAME.Value, Fields!TASK_ID.Value which are the fields retrieved from the dataset assigned to the subreport.
When I manually change the parameter value to Fields!TASK_ID.Value, and try to preview the report, I get Error: Subreport could not be shown. I have no idea what the underlying issue is but am guessing that it's because the field - Fields!TASK_ID.Value is not in the dropdown but am trying to link the main report and sub report with this field.
Am I missing something here? Any help is appreciated.
Thanks,
Sirisha
View 3 Replies
View Related
Feb 20, 2008
How can I create a Table whose one field will be 'tableid INT IDENTITY(1,1)' and other fields will be the fields from the table "ashu".
can this be possible in SQL Server without explicitly writing the"ashu" table's fields name.
View 8 Replies
View Related
Jan 26, 2008
sir
I have got this error message to establish connction with recordset vb .net, Can you please rectify this
Too many arguments to 'Public Overridable ReadOnly Default Property Fields() As ADODB.Fields'
my code like this
rs = New ADODB.Recordset
rs.Open("Select * from UserLogin where userid='" & txtUserName.Text & "'", gstrDB, DB.CursorTypeEnum.adOpenStatic)
If txtUserName.Text = rs.Fields.Append(userid) Then
MsgBox("OK", MsgBoxStyle.OKOnly, "Confirmation")
End If
thanks
View 1 Replies
View Related
Jul 23, 2005
Hello !I'm trying to update one table field with another table searched firstdate record.getting some problem.If anyone have experience similar thing or have any idea about it,please guide.Sample case is given below.Thanks in adv.T.S.Negi--Sample caseDROP TABLE TEST1DROP TABLE TEST2CREATE TABLE TEST1(CUST_CD VARCHAR(10),BOOKING_DATE DATETIME,BOOKPHONE_NO VARCHAR(10))CREATE TABLE TEST2(CUST_CD VARCHAR(10),ENTRY_DATE DATETIME,FIRSTPHONE_NO VARCHAR(10))DELETE FROM TEST1INSERT INTO TEST1 VALUES('C1',GETDATE()+5,'11111111')INSERT INTO TEST1 VALUES('C1',GETDATE()+10,'22222222')INSERT INTO TEST1 VALUES('C1',GETDATE()+15,'44444444')INSERT INTO TEST1 VALUES('C1',GETDATE()+16,'33333333')DELETE FROM TEST2INSERT INTO TEST2 VALUES('C1',GETDATE(),'')INSERT INTO TEST2 VALUES('C1',GETDATE()+2,'')INSERT INTO TEST2 VALUES('C1',GETDATE()+11,'')INSERT INTO TEST2 VALUES('C1',GETDATE()+12,'')--SELECT * FROM TEST1--SELECT * FROM TEST2/*Sample dataTEST1CUST_CD BOOKING_DATE BOOKPHONE_NOC12005-04-08 21:46:47.78011111111C12005-04-13 21:46:47.78022222222C12005-04-18 21:46:47.78044444444C12005-04-19 21:46:47.78033333333TEST2CUST_CD ENTRY_DATE FIRSTPHONE_NOC12005-04-03 21:46:47.800C12005-04-05 21:46:47.800C12005-04-14 21:46:47.800C12005-04-15 21:46:47.800DESIRED RESULTCUST_CD ENTRY_DATE FIRSTPHONE_NOC12005-04-03 21:46:47.80011111111C12005-04-05 21:46:47.80011111111C12005-04-14 21:46:47.80044444444C12005-04-15 21:46:47.80044444444*/
View 3 Replies
View Related
Jul 2, 2015
I am using MS SQL 2012. I have a table that contains all the data that I need, but I need to summarize the data and also add up decimal fields while at it. Then I need a total of those added decimal fields. My data is like this:
I have Providers, a unique ID that Providers will have multiples of, and then decimal fields. Here are my fields:
ID, provider_name, uniq_id, total_spent, total_earned
Here is sample data:
1, Harbor, A07B8, 500.00, 1200.00
2, Harbor, A07B8, 400.00, 800.00
3, Harbor, B01C8, 600.00, 700.00
4, Harbor, B01C8, 300.00, 1100,00
5, LifeLine, L01D8, 700.00, 1300.00
6, LifeLine, L01D8, 200.00, 800.00
I need the results to be just 3 lines:
Harbor, A07B8, 900.00, 2000.00
Harbor, B01C8, 900.00, 1800.00
LifeLine, L01D8, 900.00, 2100.00
But then I would need the totals for the Provider, so:
Harbor, 1800.00, 3800.00
View 3 Replies
View Related
Jul 20, 2005
I would like to search a table for a phrase, or for a partial phrase,eg on table product - for name or description, or name + descprition.How does one say select * from product where name + description like%phrase%or contains phraseCurrently I can get where name, or where descriotion like %phrase%,eg, where name like krups, or where description like coffee makerBut if I search for where name like %krups coffee maker% i get noresults. krups is in the name field, coffee maker is in thedescription field.Thanks,-M
View 1 Replies
View Related
Jun 30, 2000
Pardon me if this question is too elementary. I am trying to create a trigger that will cause certain datafields to be updated with values from other data fields in the same row when a certain column, created specifically to fire the trigger, is updated. The purpose of this is to reduce data entry by field personnel.I think I have the create trigger statement correct, but I'm a little confused on the update statement.
In a nutshell, how can I write something like:
UPDATE "TABLENAME"
SET DATAFIELD1 = DATAFIELD2
WHERE RECORDNUMBER = (THE SAME RECORD NUMBER)
I do know that I have to ensure that sp_dboption Recursive Triggers value is set to false, thanks.
View 2 Replies
View Related
Jul 31, 2013
I have a list of items in one table and a field (pageName) in another table that may contain one of the aforementioned items somewhere within that field. There is no fixed position within the field where the itemNo may be so I can't just use SUBSTRING(pageName,2,5) in(select itemNo from tblItem).
Logically, it's like I need to combine IN and LIKE: select pageName where pageName LIKE IN %select itemNo from tblitemNo%..LIKE can only handle one comparison string.
View 5 Replies
View Related
Jan 21, 2008
Hi people
My users are having troubles with link to default drillthrough report when reports are exported to excel (they REALLY don't like this behavior ), so I decided set all of them disabled in report model, this work fine, but calculated field in reports has this drillthrough link.
Let me show you the situation. Entity Product has an UnitaryCost field, I set the EnableDrillthrough Property in False so when I export a report with this field, no link is shown.
But if I create in the report a calculated field Round(UnitaryCost) this field has a Drillthrough Link
Is this the standard and expected behavior? or its simply a BUG?
Have I done something wrong in my model? and in this case, How I can correct this?
regards.
Julio Diaz.
View 1 Replies
View Related
Aug 18, 2005
Hello all,I have a strange problem that i need some advice on. I have the following field called FILENO. It is a SQL 2000 field with the Data Type set to Char (7). The following sql statement works perfectly:SELECT TOP 1 RTRIM(FILENO) AS TEST, RIGHT(DATEPART(Yy, FILENOYEAR), 2) AS YEAR FROM tblRecordsWHERE RIGHT(DATEPART(Yy, FILENOYEAR), 2) = '05'ORDER BY FILENO DESCIt returns the correct data, 0050. Now, what i'm trying to do is add 1 to the value so i can get the next available number which is 0051. But, when i run the following sql statement, i get 51 instead of 0051.SELECT TOP 1 RTRIM(FILENO+1) AS TEST, RIGHT(DATEPART(Yy, FILENOYEAR), 2) AS YEAR FROM tblRecordsWHERE RIGHT(DATEPART(Yy, FILENOYEAR), 2) = '05'ORDER BY FILENO DESCDoes anyone have an idea how to solve this? Thanks.Richard M.
View 1 Replies
View Related
Feb 28, 2001
I'm working as a consultant for a project and I have been having constant issues with the in house "database guru" about stored procedure formating.
He insists I use a format similar to this:
create procedure getUser @UserId int = NULL
as
select
UserName
, UserPassword
, UserRole
from
Users
join
UserRole
on
User.ID = UserRole.ID
where
User.ID = @UserID
and
User.Active = 1
where as I prefer this format:
create procedure getUser
(
@UserId int = NULL
)
as
select UserName,
UserPassword,
UserRole
from Users
join UserRole on User.ID = UserRole.ID
where User.ID = @UserID
and User.Active = 1
Now normally I don't argue about style issues but I find his format confusing and it bothers me that it senselesly wastes lines make the stored procedure longer than necessary.
I'd love to get some comments on this issue or reference to any discussions on issues such as this.
Thanks
View 2 Replies
View Related
Apr 9, 2008
Hello -
Does anyone know why SQL Server Reporting Services formatts SQL weird? -- If I type a SQL statement in notepad or notepad++ and then paste it in Reporting Services it will change the whole structure and "expand" it out to three or four times its size.
Thanks
Adam
View 3 Replies
View Related
Jun 27, 2007
I need to create either a sql query or a procedure and i am not sure how to go about it.Simply i have two fields in a table. 1 for products values and 1 for how many of that product was purchased.What i am wondering is how to calculate this in a sql query.can someone help pleaseIlan
View 5 Replies
View Related
Aug 27, 2007
I am deleting a database but I am not seeing any entry in server logs or in event viewer. and same thing is happens when I am deatching a database.Can any body tell me the way through which I can know when a particualr database was deleted or deatched.
View 2 Replies
View Related
Mar 30, 2004
Psting For Charan,
Hi,
I am trying to connect MS SQL 2000 with C language using ODBC drivers in win 2k environment
i have a UTF 8 string stored in a nvarchar field of the MS SQL table and i am trying to retrieve this field from C and store it in a txt file
everythin is working perfectly fine the probs is that the UTF 8 string is not getting being retrieved correctly
Help Required Urgently
Tks in Adv
Ssg
View 1 Replies
View Related
Oct 26, 2007
Hello All,
When I execute my SP it throws an error.
Could not allocate space for object '(SYSTEM table id: -449020546)' in database 'TEMPDB' because the 'DEFAULT' filegroup is full.
Any help is apperciated.
Nirene
View 2 Replies
View Related
Dec 5, 2007
Hello All,
I have 2 tables Trans & Slmast
In Trans
Tran_type,Tran_amt,Slcode
----------------------------
VES,10000.00,AB01
DBN,20000.00,AB01
CRN,30000.00,AB01
BRT,40000.00,AB01
DBN,10000.00,AB02
CRN,50000.00,AB02
BRT,20000.00,AB03
CRN,30000.00,AB03
In Slmast
Slcode,Sldesc
--------------
AB01,ABC Ltd
AB02,CAB Ltd
AB03,BAC Ltd
All transaction related datas will be in Trans for which Slmast is the master.
I want to sum those records based on slcode,tran_type for slcode which does not have tran_type with 'VES'
Sample Output
Slcode,Sldesc,DBN,CRN,BRT
AB02,CAB Ltd,10000,50000,0
AB03,BAC Ltd,0,30000,20000
View 7 Replies
View Related
Jan 2, 2008
CREATE PROCEDURE VEHICLE_OUTSTANDING @SDt Varchar(10),@EDt Varchar(10),@Cocode Varchar(5),@Brcode Varchar(5),@Loccode Varchar(5),@GLCode Varchar(6)
AS
Select (Loccode+Glcode+Slcode) as LGS,Refno,Refdt Into #VST from Trans_Transnarr
Where Trtype='VES' and (Refdt>=Convert(Datetime,@SDt,103) and
Refdt<=Convert(Datetime,@EDt,103))
and (Cancflg Is Null or Cancflg='R') and Loccode=@Loccode and Glcode=@Glcode
Select Sl.Sldesc,Tr.Loccode,Tr.Glcode,Tr.Slcode,
X.Refno,Convert(Varchar,X.Refdt,103) as Refdt,
Sum(Case When Tr.Trtype='VES' Then Tr.Tramt Else 0 End) as VES,
Sum(Case When Tr.Trtype='DBN' Then Tr.Tramt Else 0 End) as DBN,
Sum(Case When (Tr.Trtype='CPM' or Tr.Trtype='BPM') Then Tr.Tramt Else 0 End) as Refund,
Sum(Case When (Tr.Trtype='JNL' and Tr.Drcrflag='D') Then Tr.Tramt Else 0 End) as JNLD,
Sum(Case When Tr.Trtype='SPS' Then Tr.Tramt Else 0 End) as SPS,
Sum(Case When (Tr.Trtype='CRT' or Tr.Trtype='BRT') Then Tr.Tramt Else 0 End) as Receipt,
Sum(Case When Tr.Trtype='ADV' Then Tr.Tramt Else 0 End) as ADV,
Sum(Case When Tr.Trtype='CRN' Then Tr.Tramt Else 0 End) as CRN,
Sum(Case When (Tr.Trtype='JNL' and Tr.Drcrflag='C') Then Tr.Tramt Else 0 End) as JNLC,
Sum(Case When Tr.Trtype='SRT' Then Tr.Tramt Else 0 End) as SRT
Into #VOS from Trans_Journal Tr,Slmast Sl,#VST X
Where (Sl.Loccode=@Loccode and Tr.Glcode=Sl.Glcode and Tr.Slcode=Sl.Slcode) and
(Tr.Loccode+Tr.Glcode+Tr.Slcode)=X.LGS and (Tr.Cancflg Is Null or Tr.Cancflg='R')
Group By Tr.Loccode,Tr.Glcode,Tr.Slcode,Sl.Sldesc,X.Refno,X.Refdt
ALTER TABLE #VOS ADD OPDR Float,OPCR Float
UPDATE #VOS SET #VOS.OPDR = S.DRAMT,#VOS.OPCR = S.CRAMT FROM #VOS,
(SELECT GLCODE,SLCODE,
(CASE WHEN DRCRFLAG = 'D' THEN OPBAL ELSE 0 END) AS DRAMT,(CASE WHEN DRCRFLAG = 'C' THEN OPBAL ELSE 0 END) AS CRAMT
FROM OBDATA WHERE LOCCODE=@Loccode and GLCODE =@Glcode) S
WHERE #VOS.GLCODE = S.GLCODE AND #VOS.SLCODE = S.SLCODE
Not able to Update OPDR & OPCR
Error Message is
Invalid column name 'OPDR'.
Regards
Nirene Veshnav
View 1 Replies
View Related
Sep 6, 2006
Always wondered, now I have the time to ask - we currently backup ALLdatabases and these seem to all grow in size, the MSDB BAK files are now35mb each and growing!!! Can we remove these from our Enterprise Manager?- Master- Model- MSDBCheers, @sh
View 10 Replies
View Related
Feb 28, 2007
Hi allI am creating a web services application in Visual Studio Dot netwhich links to a MS SQL database.I need to create a form which lets the user pick his field ofchoice(doesnt matter what it is..) using drop down boxes,radio buttonsetc and query the database according to what he has picked andretrieve only those columns and rows.The thing is, I do not want to hardcode the queries usingOleDbDataAdapter or SqlDataAdapter, but want the query to be generatedon the fly, once the user picks his fields from the form.I'd greatly appreciate any help.Thanks,m
View 3 Replies
View Related
Nov 16, 2006
Pl tell me this senario
if we create new database in sql 2000 with size 1024 KB now we insert 500 KB data in that database. so what is actual size of database now?????????
View 3 Replies
View Related
Nov 3, 2006
Hi,
how we use syscolumns in Sql Server 2005 (Its not work in Sql Server 2005)
View 1 Replies
View Related
Dec 12, 2006
Hello All,
Is it possible to format an integer to a float value with 2 decimals in an sql query.
If yes, Please help?
View 2 Replies
View Related
Jan 24, 2007
I need to show my SUM of the 2 columns added in the query below formatted as currency. Is this possible?
SELECT SUM(QVSTDN + QVNONC) AS Total FROM INVOICE_TBL WHERE QVORDN = @QVORDN AND QVINV = @QVINV
I tried:
SELECT CONVERT(varchar(12), SUM(QVSTDN + QVNONC) , 1) AS Total FROM INVOICE_TBL WHERE QVORDN = @QVORDN AND QVINV = @QVINV
But this does not format it as currency. Any input would be helpful.
View 6 Replies
View Related
Apr 25, 2007
I imported data from a text file, where dates are currently in the format: 050729 to represent July 29, 2005.
How could I write a sql statment to put make this show up as 07/29/05?
Thanks
View 5 Replies
View Related
Jan 31, 2008
I am trying to output data from SQL Server to an email body message with a little formatting (just to make it legible)
Using this SQL statement:
select top 1 'Name', app_first_name+' '+app_last_name [name] ,'Submission Date', submission_date ,'Address', app_address+', '+app_city+', '+app_state+' '+app_zip from vw_bpa_export where app_email = dave@email.com order by submission_date desc
the resulting output displays this in the email body
"Name","Brian Vogler","Submission Date","2008-01-30 07:45:00","Address","6285 Harrow Trace, Norcross, GA 30092 "
but what I want is the text to look more like this:
Name Brian VoglerSubmission Date 2008-01-30 07:45:00Address 6285 Harrow Trace, Norcross, GA 30092
Is there a way to do this in the SQL statement (without HTML tags)?
View 3 Replies
View Related
Jun 9, 2008
In a SQLDataSource, the following line throws an error as an undefined function. FilterExpression='left$(lname,1) = "D"'
Isn't this a standard VB function and why does it produce an error. How do I fix?
Thanks
View 2 Replies
View Related
Dec 13, 2000
Hi,
In MS Access I can do the following:
Format([SW_Dttm],"Short Date")
What is the equivalent in SQL Server?
Thanks,
Bill
View 1 Replies
View Related
