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..
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.
I know this question has been asked here before, but it was ridiculously answered : http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=98764&SiteID=1
I have a text object on my crystal report, to which I programmatically want to assign text in CS. It all works fine, except that if I have html tags in there, the text doesn't get formatted, it just displays the html tags as text as well.
Now in thread linked above, the answer was: right click the text object, and format the text object, hit paragraph text and select text interpretation to HTML. The only problem is, that for a text object, it doesn't have the text interpretation option. It's only an option for a formula field. It says so in the tech support link posted inside the post itself!!!
So if you have any idea how I can do this, please someone help me out. I am new to crystal, and there's not much helpful info on it online. I've searched for this for hours, and no solution.
I have a sql database where i have descriptions of products stored. I insert text using a simple sql insert and that seems to work fine. But when i try to get these texts and display them in a gridview with an object data source thet brake rows are not displayed. The text is just display with now row brakes at all. How can i fix this so that i get it displayed in rows? Because its almost unreadable like ths.
Hi, I am trying to work ouyt how to dislplay text from an SQL database onto the page whilst keeping the correct formatting, mainly the paragraph breaks. When I try using a label the text comes out as one long line instead of being seperated accordingly as it was done when written in a multiline textbox. I am currently achieving this by writing the text to a txt file and then displaying it dynamically by inserting each line into a new table row and then adding these to a table, but I would much rather store them in the databse along with the other information that is collected with it instead. Someone told me to use a textbox to achieve this but the problem is that this is not crawlable for the search engine.
I have database fields that contain RTF text. If I use the default Textbox in SSRS 2008 Report Designer, I get all the RTF formating characters, which makes for a VERY ugly report.Is there another control I can place on my report that will take the RTF formated text and display it properly? I can do this in Crystal Reports, but I'm converting to SSRS 2008 so I need to be able to handle RTF formated text.
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.
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?"
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
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
I have a pivot table that connects to our data warehouse via a PowerPivot connection. Â The data contains a bunch of comment fields that are each between 250 and 500 characters. Â I've set the columns in this pivot table to have the 'Wrap Text' set to true so that the user experience is better, and they can view these comment fields more clearly.
However, whenever I refresh the data, the text wrapping un-sets itself. Â Interestingly, the 'Wrap Text' setting is still enabled, but I have to go and click it, then click it again to actually wrap the text. Â This is very burdensome on the user, and degrading the experience.
Any way to make this text wrapping stick so that I don't have to re-set it every time I refresh the data?
I have simple pivot table (below screenshot with info redacted) that displays a population number ("N" below), this is the denominator, a cumulative numerator number (below "#") and a simple cumulative percent that just divides the numerator by the denominator. It cumulates from top to bottom. The numerator and percent are cumulative using the below functions. There are two problems with the numerator and percent:
1. When there is not a number for the numerator, there is no value displayed for both the numerator and the percent..There should be a zero displayed for both values. 2. When there has been a prior number for the numerator and percent (for a prior month interval) but there is no number for the numerator in the current month interval, the prior month number and percent are not displayed in the current month interval--see the 3rd yellow line, this should display "3" and "16.7%" from the second yellow line.Here is the formula for the numerator:
=CALCULATE(count(s1Perm1[entity_id]),FILTER(ALL(s1Perm1[ExitMonthCategory]),s1Perm1[ExitMonthCategory] <= MAX(s1Perm1[ExitMonthCategory]))) Here is the formula for the percent: =(CALCULATE(countrows(s1Perm1),FILTER(ALL(s1Perm1[ExitMonthCategory]),s1Perm1[ExitMonthCategory] <= MAX(s1Perm1[ExitMonthCategory]))))/(CALCULATE(COUNTROWS(s1Perm1),ALL(s1Perm1[Exit],s1Perm1[ExitMonthCategory])))
I've never used SQL for anything but simple copies and queries, and now I need to do something that's probably simple, as well, but I don't know how to start. I need to update values in a text field with new values from another table. I can do it individually with an update statement (Set [field1] = 'newvalue' where [field1] = 'oldvalue'). But I have 400 different values and a bunch of different tables that need to be updated, and can't imagine that's the only solution. I can make up a simple table that will have all the old values in one column, and the new values in the next. I need a statement that look at the old value, then fetch the new value from the table I made, and replace the old value with the new.
I've looked at various help files and tried to search for a solution elsewhere, but I'm not coming up with anything. I guess I just don't know where to look. Thanks in advance for any help you can give me.
I used to do this with classic asp but I'm not sure how to do it with .net.Basically I would take a table of Categories, Then I would loop through those. Within each loop I would call another stored procedure to get each item in that Category. I'll try to explain, Lets say category 2 has a player Reggie Bush and a player Drew Brees, and category 5 has Michael Vick, but the other categories have no items.Just for an example.. Category Table: ID Category1 Saints2 Falcons3 Bucaneers4 Chargers5 FalconsPlayer Table:ID CategoryID Player News Player Last Updated1 1 Reggie Bush Poetry in motion 9/21/20062 1 Drew Brees What shoulder injury? 9/18/20063 5 Michael Vick Break a leg, seriously. 9/20/2006 Basically I would need to display on a page:SaintsReggie BushPoetry in MotionFalconsMichael VickBreak a leg, seriously.So that the Drew Brees update doesnt display, only the Reggie Bush one, which is the latest.I have my stored procedures put together to do this. I just don't know how to loop through and display it on a page. Right now I have two datareaders in the code behind but ideally something like this, I would think the code would go on the page itself, around the html.
I have an access database (access 95 Version7)dumping a delimited text file onto my server. I am then using DTS in SQL 2000 to import the file into a table.
My issue is that each time the DTS runs, it imports the whole text file each time, this is causing duplicate records.
So I created a transformation script as follows :
Function Main()
If DTSSource("counter") <= DTSDestination("counter") Then Main = DTSTransformStat_SkipRow Else
The theory behind the If statement, is if it sees that the counter field is less than or equal to what is there, it will skip the record and move forward. For some reason this is not working.
Does anyone have a workaround or another solution to this problem
I am using DTS to import text to a table. This mostly works except for the 'DateTime' datatype where I always run into either 'Arithmetic overflow' or 'Unable to convert varchar to DateTime format.' I can convert same data without incident in Access.
Hello!, I have two applications that import data from text files to Access 97 tables. One of this applications is in Access 97 and the other in VB6, know they are requesting me to import this text information to a SQL table. I do not have any experience with SQL Server, can some one give a idea of what I could do to accomplish this?, I would appreciate a lot anyone's help!, thanks! ...
Hi, all. I'm fairly new to SQL, and I have been trying to create a tablefrom a text file. I have been looking at this for days, and can't find theproblem. I get a syntax error " Line 55: Incorrect syntax near'DateUpdated'." Here is the query. Any suggestions would be appreciated,as I am trying to learn and improve.Use ACHgoif exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[ImportFiles]') and OBJECTPROPERTY(id, N'IsProcedure') =1)drop procedure [dbo].[ImportFiles]GOCREATE Procedure ImportFiles@FilePath varchar(1000),@MergeProc varchar(128) = 'MergeData'ASDECLARE @cmd varchar(2000),@Command_String varchar(3000)DECLARE @FileName varchar(1000),@File varchar(1000)CREATE table ##Import (datarow varchar(200))CREATE table #Dir (datarow varchar(200))DROP TABLE ACHParticipantsselect @cmd = 'dir /B' + @FilePathdelete #Dirinsert #Dir exec master..xp_cmdshell @cmddelete #Dir where datarow is null or datarow like '%not found%'while exists (select * from #Dir)BEGINselect @FileName = min(datarow) from #Dirselect @file= @FilePath + @FileNameselect @cmd = 'bulk insert'select @cmd = @cmd + ' ##Import'select @cmd = @cmd + ' from'select @cmd = @cmd + ' @File,'select @cmd = @cmd + ' with (FIELDTERMINATOR='''''select @cmd = @cmd + ',ROWTERMINATOR = '':'')'truncate table ##Import-- import the dataexec (@cmd)-- remove filename just importeddelete #Dir where datarow = @FileNameexec @MergeProcENDdrop table ##Importdrop table #DirGOif exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[MergeData]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)drop procedure [dbo].[MergeData]GOCREATE PROCEDURE MergeDataASCREATE table ACHParticipants(RoutingNum varchar(9),OfficeCode varchar(1),ServicingFRBNum varchar(9),RecordType varchar(1),ChangeDate varchar(8),NewRoutingNum varchar(9),BankName varchar(36),BankAddress varchar(36),City varchar(20),State varchar(2),Zipcode varchar(10),Phone varchar(14),StatusCode varchar(1),DataView varchar(1),Filler varchar(5),DateUpdated datetime)INSERT INTO ACHParticipants(Routing_Number, Office_Code, Servicing_FRB_Number, Record_Type_Code, Change_Date, New_Routing_Number, Customer_Name, Address, City, State_Code, Zipcode, Telephone, Institution_Status_Code, Data_View_Code, Filler, DateUpdated)SELECT Substring(DataRow,1,9) AS RoutingNum,Substring(DataRow,10,1) AS OfficeCode,Substring(DataRow,11,9) AS ServicingFRBNum,Substring(DataRow,20,1) AS RecordType,convert(datetime,Substring(DataRow,21,6)) AS ChangeDate,Substring(DataRow,27,9) AS NewRoutingNum,Substring(DataRow,36,36) AS BankName,Substring(DataRow,72,36) AS BankAddress,Substring(DataRow,108,20) AS City,Substring(DataRow,128,2) AS State,Substring(DataRow,130,5) + '-' + Substring(DataRow,135,4) AS Zipcode,Substring(DataRow,139,3) + '-' + Substring(DataRow,142,3) + '-' +Substring(DataRow,145,4) AS Phone,Substring(DataRow,149,1) AS StatusCode,Substring(DataRow,150,1) AS DataView,Substring(DataRow,151,5) AS FillerDateUpdated datetime AS DateUpdatedFROM ##ImportGOThanks,Karen
What is the best way to parse large amounts of formatted text data into a table so that it can be retrieved with as much formatting retained as possible - particularly paragraphs? Will each paragraph need to be inserted into its own row to be retrieved as a paragraph?
I am using sql server express edition and when i try to apply full text indexing on the table - the setting is set to "NO" and i am unable to set it to Yes please advise - thanks Jeff
I m using SQL Server 2000. I have Tabel named Topic and have a column name lineage. lineage has data like following: //////546707//546707//546707/43213/ Now I want to get records who has only one "/" in it's crreponding lineage column. Can somone tell me how to do that in SQL Server 2000? Thanks Khushbu
I have portions of data coming in as text files containing new records and updates of existing records. The solution I've figured out till yet is to import a portion of data into some intermediate table and then run a stored procedure to migrate the data into the real table. Any ideas how to do this in a more efficient way? Thanks in advance,
I am familiar with the MySQL Load Data command to load an external ascii file into a database table, but am having trouble finding a T-Sql command that is equivalent without creating an executable...any help would be appreciated...
Anbody please help I am trying to export a text file to a table using enterprise manager and all tasks But the process keeps adding strange charater like squares at the end of each line and also replaces each empty line in the text file with a record in the table with that square type character. I used the following code to delete all rows with that character (as a work around) but no joy. I am losing hope.
How to convert a SQL table into Text file? I have a table and I want to extract the values with the field names above to a text file. The query should also allow me to define the starting position of the fields in the text file.
