Data Formatting Problem

Jun 27, 2007

Hello,

I'm looking for solutions to a formatting problem.
I have two stand alone textboxes on a report body that I would like to have display side by side with very little white space between them.

Example: TextBox 1 contents (variable length) = "Muddy Waters"
Textbox 2 contents (variable length) = "B.Sc"

Current display: Muddy Waters B.Sc

Desired display: Muddy Waters B.Sc

Here is my problem.
Simple concatenation of values into 1 textbox would work if the font size and font family were the same for both textboxes; unfortunately this isn't the case. The font size and family for each textbox is different. Is there a richtext control I can place on the report and load with preformatted rich text?

Any suggestions would be appreciated.

Thanks,
Roy M

View 5 Replies

Data Formatting

Mar 13, 2001

Does anyone familiar with a tool or a quick way to put apostrophes and commas around some values, so they could be used in a query ? Example, if I have a bunch of zip codes such as:
10001
10003
10009
54321
89043
etc
Let's say I have a couple of hundreds of them. I need to be able to see them as -

'10001','10003','10009','54321','89043' etc

Appreciate very much your time.

Ivan

View 3 Replies View Related

Formatting Data Help!!

Apr 12, 1999

Hello Everyone,

I have been requested to take a number of columns and concatenate them into one column with a specifc format/layout. For example I need to take this, (noting all columns are of different datatypes:

col_1 col_2 col_3 col_4 col_5 col_6 col_7
PK #1 John Doe 123 Elm Street Anywhere NM 55660
#2
#3
etc..

...and create this on a different table (noting col_2 is of datatype "text":
col_1 col_2
PK #1 John Doe
123 Elm Street
Anywhere NM 55660
#2
#3
etc..

Can this type of formatting be done?
I know how to concatenate data and use the convert function, but how do you add in CR-LF to get the proper formatting??

Any help would be greatly appreciated.
Troy

View 1 Replies View Related

Getting Formatting Into Data

Sep 8, 2006

I am new to php/MySQL and am working on an online manual. The data is divided into Title and Content. The title is easy as it is one line but the content is meant to be in a number of paragraphs.

The Content field is currently set as "blob" and the text comes out as just one paragraph. Wha can I do to get paragraphs etc into the text?

Thanks.

View 1 Replies View Related

Formatting Data

Dec 5, 2006

Hi all,

I have data in a SQL Server table of type varchar(6). Each record in the column is a number of 2, 3 or 4 digits.

e.g. 22, 130, 1000, 60 etc...

I want to update all the values in the column so that they are always 4 characters long and have leading zeros.

e.g. 0022, 0130, 1000, 0060 etc...

How could I go about doing this?

Thanks
Kabir

View 2 Replies View Related

Formatting Data With FOR XML And Sp_makewebtask?

Aug 18, 2004

Hi all,

I have a question about using FOR XML and sp_makewebtask. This (see below) is a simplified example where I have a table with 3 columns, the two first columns hold varius data and the third column (Subdata3) holds duplicated data.

My question is: Is it possible to transform the hierachy of data (see "Prefered XML output" for details) when transforming from SQL to XML using either FOR XML EXPLICIT or by using some sort of more advanced sp_makewebtask template (or some other technique perhaps)? And if, how? Any help welcome!

Cheers,
Christian

----------------------

--- sp_makewebtask ---
EXEC sp_makewebtask
@outputfile = c: empoutput.xml,
@query = 'SELECT Subdata1, Subdata2, Subdata3 FROM Data WHERE Subdata3 = X FOR XML AUTO, ELEMENTS',
@templatefile ='c: emp emplate.tpl'

--- c: emp emplate.tpl ---
<?xml version="1.0" encoding="UTF-8"?>
<Data>
<%begindetail%>
<%insert_data_here%>
<%enddetail%>
</Data>

--- c: empoutput.xml ---
<?xml version="1.0" encoding="UTF-8"?>
<Data>
<Subdata>
<Subdata1>455</Subdata1>
<Subdata2>12312</Subdata2>
<Subdata3>1</Subdata3>
</Subdata>
<Subdata>
<Subdata1>435345</Subdata1>
<Subdata2>675</Subdata2>
<Subdata3>1</Subdata3>
</Subdata>
<Subdata>
<Subdata1>133323</Subdata1>
<Subdata2>976</Subdata2>
<Subdata3>1</Subdata3>
</Subdata>
</Data>

--- Prefered XML output ---
<?xml version="1.0" encoding="UTF-8"?>
<Data Subdata3="1">
<Subdata>
<Subdata1>455</Subdata1>
<Subdata2>12312</Subdata2>
</Subdata>
<Subdata>
<Subdata1>435345</Subdata1>
<Subdata2>675</Subdata2>
</Subdata>
<Subdata>
<Subdata1>133323</Subdata1>
<Subdata2>976</Subdata2>
</Subdata>
</Data>

View 1 Replies View Related

Formatting Money Data Type

Jul 17, 2001

Hi, How would I convert(or format) money data type, so the output will be like: 123,456.78 or 12,345.67
(In other words how would I insert a comma which separates hundred from thousand...)

Thanks a lot,
Andy

View 1 Replies View Related

Visual Formatting Of Data Returned From A SAP Database

Apr 3, 2014

I am working with extracting data from a SAP database, and I would like to perform some 'visual formatting' on the returned data. Because the SQL code that I am writing will be used in a custom Report it is not possible for me to use '3rd Party' formatting tools. Furthermore because the database is hosted on an MS SQL 2008 R2 RDBMS it is not possible to use the IIF() function.

Here is a sample of the data that I am getting back at the moment. URL...Here is how I would like the data to be returned (with the exception that the blue lines will ideally be blank cells). URL...

I have come to believe that I should be using the ROW_NUMBER() function, along with the OVER() function and possibly the 'partition by' keywords.Here is my original code -

Code:
select
td.ItemCode as 'Item Code'
, td.Dscription as 'Item Description', td.Quantity as 'Order Qty'
, titm.OnOrder as 'PO Qty'
, th.CardCode as 'BP Code', th.CardName as 'BP Name'
, th.DocNum as 'Sales Order Number'
, tsp.SlpName as 'Sales Person'
, twhs.WhsCode as 'Whs Code'
, isnull(tbloc.BINLABEL, '') as 'BIN Label', isnull(cast(tbloc.QUANTITY as nvarchar(20)), '') as 'BIN Qty'

code]...

Notice above that I only see the 'Seq' and no other data. How can I go about modifying this code so that I see all of the data columns I desire? If I further 'refine' the above, by adding case statements and the individual columns that I want to see (as opposed to using the * wildcard) I get a long list of error messages.

View 9 Replies View Related

Formatting Returned Data To Multiple Columns?

Oct 9, 2014

I need to return a single column (Name.company) but I want the data returned to be viewed in multiple columns.

First is that possible? Second if so how?

My data looks like this:

University X
University X - 1
University X - 2
University X - 3
College X
College X - 1
College X - 2
College X - 3
Cmty College X
Cmty College X - 1
Cmty College X - 2
etc.

How can I get it to return only:

University X
College X
Cmty College X

View 20 Replies View Related

Matrix Data Area Conditional Formatting Off Max Value

Jan 23, 2007

I have a matrix where i'm trying to have the report select the maximum value in the data area (not including the subtotals) and bold that value.

month
day1
day2
day3
day4
total

Jan
10
15
5
12
42

Feb
5
8
3
11
27

Total
15
23
8
23
69

I'm sure i will have to use the IIF function in conjunction with the InScope function.

However i'm unsure on how to isolate to the max value and make sure the Inscope function does not affect the totals.

any idea's are appreciated.

View 3 Replies View Related

Very Simple Getting Started Question About Formatting Data

Jan 2, 2008

Okay, I know this is a very simple question, but here it is.

I have a select statement that returns a value such as: 9902.7400000000.

In the select statement it just selecting Value. In the select statement, can I format the output to be with just two decimal places? I know I should know the answer to this and how to do it, but thank you for any help.

View 3 Replies View Related

Linking SQL Server Databases AND Data Formatting Like InputMask

Mar 11, 2004

Hi

I have two questions : 1) Linking SQL Server Databases
2) Data Formatting like InputMask

1) I want to know if there is a way to link two seperate SQL server databases (it's tables) within the same server (local server). I created one Global database - with clients, etc. tables. The reason I did it like this is that I have to handle three seperate invoices databases, but using the same clients, etc. tables to get their information from. I used MS Access like this, linking Clients, etc tables from the Clients Database with the three other Databases. It worked fine but now I need some new features like setting the InvoiceNumber to something different, eg . 04/0001 for this year and then 05/0001 for next year. My Access databases can't change this numbers - it's just a autonumber I used. I know some Visual Basic code can make a change like perhaps adding a new column like InvYear and then adding it together with InvoiceNumber to get the Invoice Number, but I want to change it to SQL Server because I want to learn how SQL Server works. I am just starting out and not doing courses.

2) The other thing is I want to know if there is some feature in SQL Server to make data input easier, like a InputMask in Access where you can let a text column be formatted as you want. Eg. for a telephone number, it can store it in the format you made; like (011) 123 4567, not like you typed it : 0111234567.

I would appreciate some help.

Thank you

Corn�

View 3 Replies View Related

SQL Server 2014 :: Export Data Without HTML Formatting

Oct 22, 2015

I need to export some Database data into a text file. My Query looks like this:

SELECT Category1, Category2, Category3
FROM dbo.tbl1
WHERE Category1 = 'JP-4'
AND Category2> 4;

This works fine to get the data, however there is some html formatting in the table entries such as

`,,
,` etc.

So ideally I need to remove those when exporting the data to the text file. I've tried to do it with a simple replace query but that didn't work. I've also got an issue with line splits and would need to remove the ( ).

The Data format is something like this:

Category1: JP-4
Category2: 4
Category3:Neque porro quisquam est qui dolorem ipsum quia dolor amet, consectetur, adipisci velit
Category4:Neque porro quisquam est qui dolorem ipsum quia dolor

I got it to work like this with the replace function:

SELECT REPLACE(REPLACE("PHOTOGRAPHS",'',''),'','')
FROM dbo.khia_tbl
WHERE Category1= 'JP-4'
AND Category2> 4;

But the issue is that I've got 15 columns in total and that I need to do it for several different tags for each column so
,
,

as well as "" and different spaces so that would be a lot and I thought there must be a better/more efficient way of doing it...

View 1 Replies View Related

Date Formatting - Pull Information From Data String

Jul 9, 2013

'17686568 - Bill Statement - 11/16/2006 - Stm. Date - 10/27/2006'

Above is the data string that I am trying to pull the information from. Here is the function I'm currently using:

cast(substring(c.itemname,charindex('Bill Statement - ',c.itemname)+18,10) as varchar)

...which gives me what i want most of the time, but beacuse the date is not equally formatted through out the database and the date can look like 5/4/2012 and using the above formula it will show up as 5/4/2012 -

So my question is how can i trim off the dash part when the data shows up like 5/4/2012 - ?

View 3 Replies View Related

Data Driven Subscription Report Not Formatting In Adobe Correctly

Oct 16, 2007

I have a report that has ten pages (essentially ten different reports). Each page has one, large main chart and then three smaller charts stacked on top of each other off to the right. The layout is in landscape. When I render the report in Reporting Services, the layout looks fine. If I export it to Adobe, it is also fine. However, when the report is emailed as a PDF attachment, the main chart on each page is completely missing. Has anybody experienced something simliar? I was having issues with the layout, and decreasing the height of each page fixed everything, but created this new problem. I am using Adobe 7.0. Thank you.

View 1 Replies View Related

Reporting Services :: SSRS Databar - Data Label Formatting

Nov 8, 2015

Data Label formatting issue in SSRS Databar? Refer the below screenshot:

View 3 Replies View Related

Data Access :: Date Formatting (Convert Minutes Into HH:MM Format)

Jun 20, 2015

Please find below my query and result , how to display [Total Service Time ] in HH:Min format (Currently values in minutes)

Query:
SELECT DISTINCT dbo.sectn_dept.sectn_sc AS Customer,
MONTH(dbo.incident.date_logged) AS Month_Number, DATENAME(month,
dbo.incident.date_logged) AS Month, YEAR(dbo.incident.date_logged) AS Year,
dbo.incident.incident_ref PM_ref,
dbo.product.product_n "Product",

[Code] ....

Result:
Need to Display [Total Service Time] in below Format:

But Some values are repeating ....

View 2 Replies View Related

SQL Reporting Services 2005: Formatting One Cell With Several Lines Of Data

Feb 28, 2008

Is it possible to format a single cell with many lines of data. For instance, if I wanted to list an entire address in one cell like this:

123 Main St.
Apt. 1
Austin, TX 78759

Would that be possible through some kind of special formatting? Maybe with or something like that?

View 1 Replies View Related

Formatting

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

SQL Formatting.

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

SQL Formatting

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

Formatting In Sql Query

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

SQL Formatting A SUM As Currency

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

Formatting Dates

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

Text Formatting In SQL

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

Formatting A Filter

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

Formatting Dates

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

Formatting Values

Jun 19, 2000

I have values in my database that I'd like to display as $xxx,xxx.xx. Some of these values use the money datatype and others use float.

I'm building a string in a stored procedure that is eventually passed to Visual Basic. I want to format these values in the above format in the string that is passed. I've searched all through MSN and I couldn't find anything related to VB's FORMAT function in SQL Server.

Does Transact-SQL have any functions that could format these values for me?

View 1 Replies View Related

SQL 6.5 Query Formatting Help

Feb 1, 1999

I am trying to run a query for the company that I work for and I am getting the right data but more than needed. the query is like this

select distinct(companyname),max(calldate) from tablename

what I want to see is the latest call made to a customer for each company
but what I get back is the company listed several times with the dates of each call. I only want the latest. Any help?

View 1 Replies View Related

Xp_sendmail - Formatting

Jun 12, 2001

Requirement:
On Insert, Trigger xp_sendmail to complete pre-formatted email with specific content and send to given email address.

Question:
What tools/methods are available to develop a template that SQL Mail's (SQL Server) xp_sendmail function can utilise without resorting to attachments.

e.g. - how do Amazon.com format and send their automated shipping confirmaion emails ??

View 1 Replies View Related

Date Formatting

Oct 10, 2001

I'm currently getting a date in this format 2001-10-08 10:35:45
(yyyy-mm-dd hh:mm:ss) how would I convert the date to this format
2001-10-08/10:35:45 (yyyy-mm-dd/hh:mm:ss)? The only thing added was the / between the date and time.

Thanks in advance,

Ed

View 2 Replies View Related

Date Formatting

Aug 6, 2004

Hi,

I need to know how i can format the date so that the query results will come out as the format mm/dd/yy hh:mm:ss?

Thanks alot.

View 1 Replies View Related

Formatting Output

Oct 7, 2004

Hi, I am running a script which inserts certain rows into a table and at the end of the execution, I do a select statement to show the inserted data as output in the results pane and it is showing as truncated...How can I show the full results..
Here is my script to show the results.
----------
SET NOCOUNT ON
DECLARE @errorCount INT
SELECT @errorCount = COUNT(*) FROM error_report WHERE id != - 2 ANDid != - 5
IF @errorCount = 0
BEGIN
INSERT INTO error_report VALUES( '' , - 1 , 'No error found.' )
END
INSERT INTO error_report VALUES( '' , - 2 , 'The Report was generated on ' + CAST(CONVERT(VARCHAR(23), GETDATE(), 1) AS VARCHAR) )
GO
SELECT table_name + ' table has bad data at id = ' + CAST(CONVERT(VARCHAR(23), id) AS VARCHAR) + ' (' + CAST(reason AS VARCHAR) + ')'FROM error_report WHERE id > 0
SELECT table_name + ' table has bad data (' + CAST(reason AS VARCHAR) + ')' FROM error_report WHERE id = 0
SELECT reason FROM error_report WHERE id = - 1
SELECT ''
SELECT reason FROM error_report WHERE id = - 2
SET NOCOUNT OFF
GO
-------------------------
The Results in the bottom pane looks like this below
------------------
NODETABLE table has bad data (There are 2 duplicate subclass)
Propertytable table has bad data (at Parentid = 2000000859 and p)
Propertytable table has bad data (at Parentid = 10122 and proper)
-------------------
But, when I do a select, they are like this below
----

NODETABLE0There are 2 duplicate subclass name: Specification
Propertytable0at Parentid = 2000000859 and propertyid = 721
Propertytable0at Parentid = 10122 and propertyid = 9

View 5 Replies View Related