Formatting Total of Subtotals in Matrix

Need your help with formating color of subtotal cell.
Here is my scenario:
Matrix with are 2 row's groups:
- RowKat
- RowSubKat
and 2 column's groups:
- ColKat
- ColSubKat
I want to have my subtotal cell of RowKat, in ColKat subtotal scope
(most outer total of subtotals), to be green where it's value is > 55
Is it possible?
KamelIs it possible to define Row Subtotal in scope of Column Subtotal?|||Hi Kamel. I had a similiar scenario some time back. I could not find
anyway of doing this. the most one could do is to format the whole
subtotal, but it can't be conditionally formatted (as in differently
colored column subtotals). I had to modify the dataset-query to have
another row, and then put the conditional formatting in that row.
On Feb 7, 5:45 pm, "kamel" <kwic...@.gmail.com> wrote:
> Need your help with formating color of subtotal cell.
> Here is my scenario:
> Matrix with are 2 row's groups:
> - RowKat
> - RowSubKat
> and 2 column's groups:
> - ColKat
> - ColSubKat
> I want to have my subtotal cell of RowKat, in ColKat subtotal scope
> (most outer total of subtotals), to be green where it's value is > 55
> Is it possible?
> Kamel|||thanks, I try that way...
...but mayby somebody know the trick?|||Is there any way to format that using "InScope" function?

Formating XML field in report

Hi:
The database we are reporting from (DB2 running on AS/400) has a field with
the data type of CLOB. We would like to perform an XSLT on this field within
the same report that contains more "normal" data (strings, numeric, etc.)
types.
This database contains error and status messages that are generated as
various processes are run. We would like to be able to present the CLOB data
on the report based on the type of process that produced the data. In other
words, if process A produced the entery in to the database, on the report for
process A, format the CLOB data this way, if process B, then the CLOB data is
represented in another way. The data in the CLOB is XML and each process has
its own schema.
Right now I am stumped. Is this possible? Can you point me in the right
direction?
Thanks.You're probably going to need to write a custom function (or perhaps a
custom assembly if you're going to need to read XSLTs from disk) to do this.
You could pass the data along with the process to your custom function which
would apply the transform and return the string to display in the textbox.
E.g. =Code.CLOBClass.Convert(Fields!CLOBData.Value,Fields!Process.Value)
--
This post is provided 'AS IS' with no warranties, and confers no rights. All
rights reserved. Some assembly required. Batteries not included. Your
mileage may vary. Objects in mirror may be closer than they appear. No user
serviceable parts inside. Opening cover voids warranty. Keep out of reach of
children under 3.
"bwschiek@.hotmail.com" <bwschiekhotmailcom@.discussions.microsoft.com> wrote
in message news:95B8ADFD-2A23-4DD5-A039-971AC84B5AF9@.microsoft.com...
> Hi:
> The database we are reporting from (DB2 running on AS/400) has a field
with
> the data type of CLOB. We would like to perform an XSLT on this field
within
> the same report that contains more "normal" data (strings, numeric, etc.)
> types.
> This database contains error and status messages that are generated as
> various processes are run. We would like to be able to present the CLOB
data
> on the report based on the type of process that produced the data. In
other
> words, if process A produced the entery in to the database, on the report
for
> process A, format the CLOB data this way, if process B, then the CLOB data
is
> represented in another way. The data in the CLOB is XML and each process
has
> its own schema.
> Right now I am stumped. Is this possible? Can you point me in the right
> direction?
> Thanks.

formating values in chart

hello to every bodu. well i am truing to create a line chart in sql server reporting services and when in the data field i put the division of 2 values. i want to have a % represerntation

does anybody knows how i can achieve that?

than u in advance

Moving to Reporting Services forum.|||

Steps:

* open the chart properties dialog by right-clicking on the chart
* go to the Y-axis tab
* set the Format code property to e.g. P1 (which results in percentage value formatting with precision of 1)

-- Robert

Formating Strings using SQL

Hi,
I need to format a data in the given format.
AAA-AAA-AAA Where AAA stands for any alphanumerice.
For eg. I am looking for something like this.
FORMAT('AAA-AAA-AAA','ABCDEFGHI') Should return ABC-DEF-GHI
I can get the above info using the below given sql,
Select
SUBSTRING(Field1,1,3)+'-'+SUBSTRING(Field2,4,3)+'-'+SUBSTRING(Field3,7,3)
from Table
Any other alternatives?
What is the best way to achieve this?
Thanks & Regards,
AshishFormatting should always be a client side task.
No alternatives.
HTH, Jens Suessmeyer.
"Ashish P K" <AshishP@.icode.com> schrieb im Newsbeitrag
news:%23XttOQ%23SFHA.208@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I need to format a data in the given format.
> AAA-AAA-AAA Where AAA stands for any alphanumerice.
> For eg. I am looking for something like this.
> FORMAT('AAA-AAA-AAA','ABCDEFGHI') Should return ABC-DEF-GHI
> I can get the above info using the below given sql,
> Select
> SUBSTRING(Field1,1,3)+'-'+SUBSTRING(Field2,4,3)+'-'+SUBSTRING(Field3,7,3)
> from Table
> Any other alternatives?
> What is the best way to achieve this?
> Thanks & Regards,
> Ashish
>|||You could store the format mask in an extended property for the column, then
on the client side reference the extended property.
Not sure if I agree with the below statement ... there are always
alternatives. You could for example add an additional computed column to th
e
table that returns the data in the format you want to enforce or use a view.
If you are worried about different programmers displaying formatted data
inconsistently then formatting it as part of the result set is definitely a
way to create a consistent format of data across applications.
"Jens Sü?meyer" wrote:

> Formatting should always be a client side task.
> No alternatives.
> HTH, Jens Suessmeyer.
> "Ashish P K" <AshishP@.icode.com> schrieb im Newsbeitrag
> news:%23XttOQ%23SFHA.208@.TK2MSFTNGP10.phx.gbl...
>
>|||"Chris Stransky" <ChrisStransky@.discussions.microsoft.com> wrote in message
news:0AA9C3F9-1FCB-4CA7-9808-48362B172CAF@.microsoft.com...
> You could store the format mask in an extended property for the column,
> then
> on the client side reference the extended property.
> Not sure if I agree with the below statement ... there are always
> alternatives. You could for example add an additional computed column to
> the
> table that returns the data in the format you want to enforce or use a
> view.
> If you are worried about different programmers displaying formatted data
> inconsistently then formatting it as part of the result set is definitely
> a
> way to create a consistent format of data across applications.
I don't agree with this part.
I have functions client side that handle all formatting: currency, dates,
telephone numbers...
All programmers use these functions in our ASP pages.
The functions also format data depending on user language.
At least this way, if management wants to change formatting, let's say for
dates, I need only to modify the function that handles this, not look at all
tables that have a datetime column.

formating string to time

Hello,

I have the following question.

I have a report where i do a lot of time calculation

in one colomn i have values in seconds.

for example i have a total of 246 seconds

how can i format this to view as 00:04:06 or even 4 mins 6 secs

Vincent

Hello Vincent,

Try putting this in your textbox's expression...

=cStr(Floor(Fields!TotalSeconds.Value / 60)) + " mins "
+ cStr(Fields!TotalSeconds.Value - Floor(Fields!TotalSeconds.Value / 60) * 60) + " secs"

Hope this helps.

Jarret

|||

Hello Jarret,

This works great.

Thank you.

Vincent

|||

Hi,

How can i add the hours also to it ?

I'm still really quite new to all this stuff, but i'm working on it.

Thanks

Vincent

|||

For hours, try this...

=cStr(Floor(Fields!TotalSeconds.Value / 3600)) + " hours "
+ cStr(Floor(Fields!TotalSeconds.Value / 60) - Floor(Fields!TotalSeconds.Value / 3600) * 60) + " mins "
+ cStr(Fields!TotalSeconds.Value - Floor(Fields!TotalSeconds.Value / 60) * 60) + " secs"

Jarret

Formating Sql Results

In the following Query I would like the results to look like:

2003/03/03 10PM

Now it looks like:

2003/03/03 10

Is there any way to convert the 10 to a 10pm when its already part of an expression?

SELECT CONVERT(varchar(8), DATEPART(yyyy, Time_stamp)) + '/' + CONVERT(varchar(8), DATEPART(mm, Time_stamp)) + '/' + CONVERT(varchar(8), DATEPART(dd, Time_stamp)) + ' ' + CONVERT(varchar(8), DATEPART(hh, Time_stamp)) AS Expr1, count(*) FROM dbo.Transactions $WHERECLAUSE$ and type_id=74 GROUP BY CONVERT(varchar(8), DATEPART(yyyy, Time_stamp)) + '/' + CONVERT(varchar(8), DATEPART(mm, Time_stamp)) + '/' + CONVERT(varchar(8), DATEPART(dd, Time_stamp)) + ' ' + CONVERT(varchar(8), DATEPART(hh, Time_stamp))CONVERT(varchar(8), DATEPART(hh, Time_stamp),100)|||Originally posted by Satya
CONVERT(varchar(8), DATEPART(hh, Time_stamp),100)

changing:
CONVERT(varchar(8), DATEPART(hh, Time_stamp))

to:
CONVERT(varchar(8), DATEPART(hh, Time_stamp), 100)

Doesn't change the output at all?|||Hi,

Replace
CONVERT(varchar(8), DATEPART(hh, Time_stamp),100)
With
Select substring ( Replace(convert(varchar, getdate(), 100), substring(convert(varchar, getdate(), 100), charindex(':', getdate()), 3), ''), 11, len(convert(varchar, getdate(),100)))

i think give u the desire result.

Cheers,
Gola munjal

Originally posted by Will trever
In the following Query I would like the results to look like:

2003/03/03 10PM

Now it looks like:

2003/03/03 10

Is there any way to convert the 10 to a 10pm when its already part of an expression?

SELECT CONVERT(varchar(8), DATEPART(yyyy, Time_stamp)) + '/' + CONVERT(varchar(8), DATEPART(mm, Time_stamp)) + '/' + CONVERT(varchar(8), DATEPART(dd, Time_stamp)) + ' ' + CONVERT(varchar(8), DATEPART(hh, Time_stamp)) AS Expr1, count(*) FROM dbo.Transactions $WHERECLAUSE$ and type_id=74 GROUP BY CONVERT(varchar(8), DATEPART(yyyy, Time_stamp)) + '/' + CONVERT(varchar(8), DATEPART(mm, Time_stamp)) + '/' + CONVERT(varchar(8), DATEPART(dd, Time_stamp)) + ' ' + CONVERT(varchar(8), DATEPART(hh, Time_stamp))|||Thanks for fine tuning...Gola|||one more solution...

select convert(varchar,getdate(),111) +
' ' +
left(convert(varchar,getdate(),108),2) +
right(convert(varchar,getdate(),100),2)

Formating results in Query Analyzer

I have a table as follows:
A NO Balance [decimal (16,5)]
------ ---------------- ------
18.00 001000000000000 -37.982
19.00 002000000000000 -17.656

In the Query analyzer when I try:
SELECT * FROM TABLE1

A NO Balance [decimal (16,5)]
------ ---------------- ------
18.00 001000000000000 -37.98
19.00 002000000000000 -17.66

I need the query analyzer to format them as they are in the tables.
Help please.it's weird
it's if you round up the balance data

try this

select convert(decimal(16,5),balance)|||Did not work! Same results!!!

Originally posted by Karolyn
it's weird
it's if you round up the balance data

try this

select convert(decimal(16,5),balance)|||are you sure that Balance is really stored as -37.982|||Yes, I am sure

Originally posted by Karolyn
are you sure that Balance is really stored as -37.982|||what is you're max precision ?

SELECT @.@.MAX_PRECISION|||38.0

Originally posted by Karolyn
what is you're max precision ?

SELECT @.@.MAX_PRECISION|||where do you see the data -37.982
?|||From the Enterprise Manager

Originally posted by Karolyn
where do you see the data -37.982
?|||SET NUMERIC_ROUNDABORT on
SET ARITHABORT ON

SELECT * FROM TABLE1

does an error occur ?

are you REALLY only doing Select * from Table1 ?|||No error occured,
I am sure 100% that I am doing only a select *!!

Originally posted by Karolyn
SET NUMERIC_ROUNDABORT on
SET ARITHABORT ON

SELECT * FROM TABLE1

does an error occur ?

are you REALLY only doing Select * from Table1 ?|||try contacting BlindMan or Breitt Keiser

Formating Reports To Print

How can I fixa report that displays correctly on the web page (Report
manager) but when I either print or convert to .pdf, some of the text boxes
shifts to a second and maybe a third ?
The report is actualy smaller than a regular letter page.
Any ideas ?You will have to build the report to render as a pdf. In doing so the
web formatting may look out of allignment but the pdf file will look
right. It is very difficult to get them both to look good.
Also, sometimes placing rectangles behind your report items will
prevent them from going outside the report boundries.
Another thing to check is to make sure the "report" page size and
"body" size properties are matching or similar (ie. set the report page
size to 11 x 8.5 for a Landscape print and set the body size to 10.75 x
8 to fit within the page boundries) and only work with in these
boundries. Take some time to try out different margin settings as
well. Work with it a little and usually you can come up with something
that works in the web and pdf worlds.
Let me know how it goes!

Formating of columns in output of a SQL Statement in Query Analyzer

Hi guys

I want to format the result of a SQL Statement carried out in the query analyzer. Example:

suppose that you have this table:

col1 col2
-------- --------
abcdefg bdbsjjdasjdh
bdfjsjdf hasdasjdasj
jhsdjhd asjdhashdas
hasjdhj ahsjdhajshdj

and I want this output:

col1 col2
---- ------
abcdefg bdbsjjdasjdh
bdfjsjdf hasdasjdasj
jhsdjhd asjdhashdas
hasjdhj ahsjdhajshdjand the difference is?|||What is the difference between two outputs?|||above the example is not appearing like I want but I want to delimite the size of a column setting the tool.|||Originally posted by joelperez
Hi guys

I want to format the result of a SQL Statement carried out in the query analyzer. Example:

suppose that you have this table:

col1 col2
-------- --------
abcdefg bdbsjjdasjdh
bdfjsjdf hasdasjdasj
jhsdjhd asjdhashdas
hasjdhj ahsjdhajshdj

and I want this output:

col1 col2
---- ------
abcdefg bdbsjjdasjdh
bdfjsjdf hasdasjdasj
jhsdjhd asjdhashdas
hasjdhj ahsjdhajshdj

left(col1,10) ?|||Originally posted by snail
left(col1,10) ?

yes left function for me!!!

Thanks!!!|||...or use CAST or CONVERT to specify the column widths of the resulting fields.|||Originally posted by blindman
...or use CAST or CONVERT to specify the column widths of the resulting fields.

Thanks for your recommendation blindman

Formating Numbers with Commas

I am pulling numbers from a SQl Table and adding some together and
doing other calculations. How do your format the numbers to insert a
comma and show thousands?Display formatting is always done In the front end, of course This is
the basic idea of a tiered architecture, which is more fundamental than
just SQL.

Formating Numbers with Commas

I am pulling several numbers from a SQL table, adding them and doing
various calculaitons. The numbers do not display a comma to separate
thousands. What is a way to format this?Presentation and formatting are usually done in the client, not the
server. In this case, for example, many countries do not use a comma
for separating thousands, so your client application can check the
user's locale and apply the correct formatting.

Simon|||There's no easy way to do this. For some reason I did need output like
this and wasn't able to use a front end to do the formatting, so I made
my own function.

Use as so:

SELECT dbo.Format_Number(513434512.2344)

Output is $513,434,512.23

Yes, it rounds and adds a dollar sign. But you can change it around.
:)

HTH,
Jennifer

CREATE FUNCTION Format_Number (@.N decimal(18,2))
RETURNS nVarChar(30)

AS

BEGIN

Declare @.NRnd Decimal(18,2)
Declare @.Dollar nVarChar(30)
Declare @.Dollar2 nVarChar(30)
Declare @.L int
Declare @.A int
Declare @.B int
Declare @.C int
Declare @.Cents nvarchar(20)
Declare @.NC nvarchar(30)

Set @.NC = Cast(@.N as Nvarchar(30))

Set @.NRnd = Round(@.N, 0, 1)
Set @.Dollar2 = ''
Set @.Dollar = Cast(@.NRnd as NvarChar(30))
Set @.Dollar = Substring(@.Dollar,1, Len(@.Dollar) - 3)

Set @.C = PATINDEX('%.%',@.NC)
Set @.Cents = Substring(@.NC, @.C, 3)
Set @.L = Len(@.Dollar)
Set @.A = @.L/3

Set @.B = 3
While @.A >= 0
Begin
Set @.Dollar2 = Substring(@.Dollar,@.L - @.B + 1,3) + ',' + @.Dollar2
Set @.B = @.B + 3
Set @.A = @.A - 1
End
If Left(@.Dollar2,1) = ','
Set @.Dollar2 = Substring(@.Dollar2, 2, Len(@.Dollar2))

Return '$' + Substring(@.Dollar2,1, Len(@.Dollar2)-1) + @.Cents
END|||>> For some reason I did need output like this and wasn't able to use a
front end to do the formatting, so I made my own function. <<

Since this is a fundamental violation of software engineering
prtinciples, might you share with us WHAT that reason was? It is worth
a paper in a journal.|||It was a totally stupid reason, of course. :) My boss wanted an email
output of a query emailed to him on a daily basis, so I set up a job to
do that. And then he came back and said, it sure would be nice if
those dollar amounts looked like dollars, and could the output be
changed. So being completely new and straight out of school I did as
asked.

Let me know how that paper comes out, will you? ;)|||--CELKO-- (jcelko212@.earthlink.net) writes:
> Since this is a fundamental violation of software engineering
> prtinciples, might you share with us WHAT that reason was? It is worth
> a paper in a journal.

The world is not always as ideal as you may want to be. There are probably
tons of business reports out there that are run from no other front end
than Query Analyzer, or similar tool. For some reason, someone started to
do it in QA, probably because it was a little urgent, and not possible to
pack into something better. Then that temporary hack became permaent etc.
Until one day, the requirements goes beyond what is really healthy to do
in SQL.

Anoher reason could be that the front-end tool is hopelessly difficult
to use...

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Formating numbers in SSIS

I have a Amount field which is declared as Decimal. the data for this will be somethin like this 0.152

output need it to be -

00000.1520

How can I do this?

You'll have to convert it a string and prepend/append the zeroes.

-Jamie

|||Thank You. I converted the field into string and prepended the zero's. but it was too many steps. i wish SSIS had some kind of format function available to do this.|||

Godai B wrote:

Thank You. I converted the field into string and prepended the zero's. but it was too many steps. i wish SSIS had some kind of format function available to do this.

Too many steps? Really?

"0000" + (DT_STR, 20, 1252) 0.1520

That doesn't seem like too many to me. Can you give me examples of format functions like you require?

-Jamie

|||

this field is derived from 2 columns in a sql server table and the datatype is decimal(18,3)

the output format needs to be 000000.0000 .

the input field can have a single digit or upto six digits before the decimal point. if it has a single digit then i need to prepend "00000", if there are 2 digits then i prepend "0000" and so on.

same with digits after the decimal point.

ex: Input -->10.120 Output --> 000010.1200

Input --> .1 Output --> 000000.1000

what i did was, used the findstring function in the derived column to find the decimal point position and then got the predecimal digits and postdecimal digits. then with the help of length function i prepended or appended the zero's and then finally concatenated the predecimal and postdecimal digits.

Hope u can help me with an easy way to do this.

|||

I would use the FINDSTRING function as you have done to get the whole and the mantissa. But instead of using LENGTH I would just do this:

RIGHT("000000" + [wholepart], 6) + "." + REVERSE(RIGHT(REVERSE([mantissapart] + "0000"), 4))

Yeah, maybe a format function would be good.

-Jamie

Formating numbers

I have a number 600.00 and would like to format it to look like 600 (no
decimals). How do I do that?Hi Donna,
If you want the number to round up use FormatNumber({your number},0)
If you want just what is left of the decimal use Floor({your number})
"DONNA" wrote:
> I have a number 600.00 and would like to format it to look like 600 (no
> decimals). How do I do that?|||The Format string "#" forces an integer numeral (100000), and "#,#" forces
an integer with commas in the right places (100,000).
There are also other possible format strings, like "D0" for "Decimal with 0
places after the point"
"DONNA" <DONNA@.discussions.microsoft.com> wrote in message
news:CC8FF726-6494-403A-9E9E-D4B76DED0804@.microsoft.com...
>I have a number 600.00 and would like to format it to look like 600 (no
> decimals). How do I do that?

Formating number and code

Hi, I have another question about code in RS.
I have a table cell with FORMAT of "$#,##0,0".
If I have any number in the cell I want to display it.
If I have 0 (zero) I want the cell to be empty.
I know how to do it with an expression:
iif (Fileds!myFiled.Value == 0,"", Fileds!myFiled.Value)
Now I want to do it with a code section:
=Code.hideIfZero(Fileds!myFiled.Value)
The problem is that the function should return Integer and if the
value is zero I need to return empty string.
It does't say any thing about returning the string but when it trying
to use the format on a string it give me a worning and print #ERORR in
the cell.
is there a solution for this?
Do you know the syntax to format the number in the function before the
return?
(Sorry for the dumb question but I know C# not VB.net and there is no
intelisance in that editor).
Thanks a lot!On Jul 2, 8:35 am, nicknack <roezo...@.gmail.com> wrote:
> Hi, I have another question about code in RS.
> I have a table cell with FORMAT of "$#,##0,0".
> If I have any number in the cell I want to display it.
> If I have 0 (zero) I want the cell to be empty.
> I know how to do it with an expression:
> iif (Fileds!myFiled.Value == 0,"", Fileds!myFiled.Value)
> Now I want to do it with a code section:
> =Code.hideIfZero(Fileds!myFiled.Value)
> The problem is that the function should return Integer and if the
> value is zero I need to return empty string.
> It does't say any thing about returning the string but when it trying
> to use the format on a string it give me a worning and print #ERORR in
> the cell.
> is there a solution for this?
> Do you know the syntax to format the number in the function before the
> return?
> (Sorry for the dumb question but I know C# not VB.net and there is no
> intelisance in that editor).
> Thanks a lot!
You will most likely want to return a null (or Nothing in terms of
SSRS-VB.NET), that way there is no error when trying to convert an
empty string to an integer/decimal/etc. Or you could try using
InScope. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||you could add 2 more fields, one with the formatted text, and another
that tests whether the original is 0, then displays an empty string or
the formatted field.

Formating Ntext

Hi,
I am using SQLServer 2000
I currently have a table with just one field in it of type (ntext), the
reason for this is there is a very large amount of data in it of an XML
format. The data looks something like below althought this is only a small
sample there is actually a lot more in this field:-
<Main>This is going to be a lot of text.....<Main/><Paragraph>To buy a
house in the UK now requires a very large deposit, more than 5 times the
amount that was necessary in 1999 <Paragraph/><House
Type>Detached<HouseType/><Price>￡300,000<Price/><Location>Berkshire<Locatio
n/>
<Garage>Yes<Garage/><Bedrooms>4<Bedrooms/><
What I want is to break out the data into columns so you have something like
Paragraph HouseType Price Location
Garage Bedrooms
To buy a house... Detached ￡300,000 Berkshire Yes 4
I want to ignore the data between the <Main> Tags
Generally I think I need to break the data up based on the foward slash and
work left from there, I think it's going to be based on a left/charindex
principal but I also have to do a convert to varchar in the first place as
you are unable to do much with the data in it's current data type.
If anyone can help it would be much appreciated.
Thanks PDAre you going to do this exactly once, or regularly?
In either case, I think it would be better to have an app pull the whole
value out, parse it using XML or RegEx or whatever, and generate the INSERT
statement or procedure call necessary to break the data out.
Going forward, it would probably be better to store the individual pieces of
data and build the XML when selecting. Or, if you can move to SQL Server
2005, you can use the new XML datatype, which provides a whole slew of
options to make your implementation choice harder.
A
"Phil" <Phil@.discussions.microsoft.com> wrote in message
news:46877615-148D-4912-992D-AAF2DB435243@.microsoft.com...
> Hi,
> I am using SQLServer 2000
> I currently have a table with just one field in it of type (ntext), the
> reason for this is there is a very large amount of data in it of an XML
> format. The data looks something like below althought this is only a
> small
> sample there is actually a lot more in this field:-
> <Main>This is going to be a lot of text.....<Main/><Paragraph>To buy a
> house in the UK now requires a very large deposit, more than 5 times the
> amount that was necessary in 1999 <Paragraph/><House
> Type>Detached<HouseType/><Price>￡300,000<Price/><Location>Berkshire<Locat
ion/>
> <Garage>Yes<Garage/><Bedrooms>4<Bedrooms/><
> What I want is to break out the data into columns so you have something
> like
> Paragraph HouseType Price Location
> Garage Bedrooms
> To buy a house... Detached ￡300,000 Berkshire Yes
> 4
> I want to ignore the data between the <Main> Tags
> Generally I think I need to break the data up based on the foward slash
> and
> work left from there, I think it's going to be based on a left/charindex
> principal but I also have to do a convert to varchar in the first place as
> you are unable to do much with the data in it's current data type.
> If anyone can help it would be much appreciated.
> Thanks PD|||Hi Aaron,
Thanks for the reply, I was going to go down the RegEx route but as this is
only a one of I just wanted to make it as quick and simple as possible so I
wanted to opt for querying it straight out of the field!!!.
Thanks PD
"Aaron Bertrand [SQL Server MVP]" wrote:

> Are you going to do this exactly once, or regularly?
> In either case, I think it would be better to have an app pull the whole
> value out, parse it using XML or RegEx or whatever, and generate the INSER
T
> statement or procedure call necessary to break the data out.
> Going forward, it would probably be better to store the individual pieces
of
> data and build the XML when selecting. Or, if you can move to SQL Server
> 2005, you can use the new XML datatype, which provides a whole slew of
> options to make your implementation choice harder.
> A
>
> "Phil" <Phil@.discussions.microsoft.com> wrote in message
> news:46877615-148D-4912-992D-AAF2DB435243@.microsoft.com...
>
>

Formating issues when exporting to excel

I have several columns in a report that have conditional formating. If
the criteria is met the data in the row is supposed to be green,
otherwise it is black. When I export this to excel, excel looks at the
format of the first row and then sets the formating for all the cells
to that color. Is there a way to make sure excel formats each
individual cell correctly?Also noticed that when i export the report when i run the report
through Visual Studio it exports it correctly. Is this a version issue
with RS?|||Hi, I have the same problems to set the color for each cell. Do you find the
solution? If does, please post back. Thank you.
"acahn@.planetmagpie.com" wrote:
> I have several columns in a report that have conditional formating. If
> the criteria is met the data in the row is supposed to be green,
> otherwise it is black. When I export this to excel, excel looks at the
> format of the first row and then sets the formating for all the cells
> to that color. Is there a way to make sure excel formats each
> individual cell correctly?
>|||Hi Jim,
I have not figured out the problem. I'm hopeing that when we migrate to
reporting services for 2005, that it fixes the issue...
"Jim Pan" wrote:
> Hi, I have the same problems to set the color for each cell. Do you find the
> solution? If does, please post back. Thank you.
> "acahn@.planetmagpie.com" wrote:
> > I have several columns in a report that have conditional formating. If
> > the criteria is met the data in the row is supposed to be green,
> > otherwise it is black. When I export this to excel, excel looks at the
> > format of the first row and then sets the formating for all the cells
> > to that color. Is there a way to make sure excel formats each
> > individual cell correctly?
> >
> >

formating integer

hello. A question please. I am new on Sqlserver. Could anyone say me how must I format an integer to string. example

I have:

0001 (integer field of 4 positions) and I'd want to see it "0001" (string)

Thanks...

It seems like there is a better way of doing this, but one way is something like:

select right('000'+convert(varchar(4), 1), 4) as [4-digit integer]

-- 4-digit integer
--
-- 0001

|||

Thanks. The problem is that I gave an example for the first field. (0001). But It could be 0010...

|||

What am I missing?

select right('000'+convert(varchar(4), 10), 4) as [4-digit integer]

-- 4-digit integer
--
-- 0010

|||

Waldrop wrote:

What am I missing?

select right('000'+convert(varchar(4), 10), 4) as [4-digit integer]

-- 4-digit integer
--
-- 0010

Nothing. That works fine.

SELECT right('0000' + cast([your_column] as varchar(4)),4)
FROM your_table
|||

I'm NEW !!!! Sorry !!!!. Another question please. I must put your statement in a string var. How can I do the '000' in my string var ?

Thanks...

|||

declare @.myString varchar(4)

set @.myString = right('000'+convert(varchar(4), 7), 4)

set @.myString = right('0000' + cast([your_column] as varchar(4)),4)

set @.myString = '0007'

|||

I'm so sorry. I don't understand. I forgot to say that the statement goes in an store procedure. My problem is how do I put the (') in a string var.

Thanks...

|||

An example of adding quote characters into the string variable:

declare @.myString varchar(6)
declare @.quoteChar char(1)

set @.myString = '''0007''' -- Loads '0007' (including quotes) into @.myString variable
set @.quoteChar = char(39) -- Loads a quote char ' into @.quoteChar variable
set @.quoteChar = '''' -- Loads a quote char ' into @.quoteChar variable

select @.myString as [@.myString],
@.quoteChar as [@.quoteChar]

-- - Output: -

-- @.myString @.quoteChar
-- -
-- '0007' '

declare @.anotherString varchar(4)

set @.anotherString = '0007' -- Loads '0007' (without quotes) into @.anotherString variable
set @.myString = char(39) + @.anotherString + '''' -- concatenates quote characters around '0007'

select @.anotherString as [@.anotherString],
@.myString as [@.myString]

-- - Output: -

-- @.anotherString @.myString
-- --
-- 0007 '0007'

|||Thanks. It works !!!!!

formating in SqlServer vs Reporting Services

All else being equal, is it faster to use functions like ROUND and TRIM
in the SELECT statement of the query and let the db process it or to
set formating in the layout of the report and let Reporting Services
handle it?
In my case, all is not equal. We have the DB on a very powerful machine
that at sometimes is very overtaxed and runs smoothly at others. RS is
moving to a decently powerful server that it will have exclusively.
Thanks
Louis Ryder
SSRS Report DeveloperMy two cents. Formatting is a presentation concern. Rounding to 2 decimals
can be handled in the presentation layer (i.e., RS) using format strings.
TRIM isn't necessary unless you have some smokin' character columns...
-Tim
"Louis Ryder" <lroskind@.ryderauto.com> wrote in message
news:1152203807.627376.268390@.p79g2000cwp.googlegroups.com...
> All else being equal, is it faster to use functions like ROUND and TRIM
> in the SELECT statement of the query and let the db process it or to
> set formating in the layout of the report and let Reporting Services
> handle it?
> In my case, all is not equal. We have the DB on a very powerful machine
> that at sometimes is very overtaxed and runs smoothly at others. RS is
> moving to a decently powerful server that it will have exclusively.
> Thanks
> Louis Ryder
> SSRS Report Developer
>

formating in crystal reports

How to suppress the display of a template field or the text field in crystal report on its value being NULL or empty string.

This works only for numbers, it was not possible for me to do so for strings.

Can anybody help me in this regard.

This is a forum for discussion of SQL Server Reporting Services, not Crystal Reports...you might get lucky, but I don't think anyone will be able to answer your question here. Try the businessobjects website and see if they have some sort of public forum system -- I bet you'll have better responses there?

formating h:mm:ss in a report (SSRS2005)

Hello,

I'm trying to format a numeric value (in seconds) to the format

h:mm:ss (hours:minutes:seconds).

Example: 89 seconds should formated as 0:01:29

The value is not a date/time value - so I'm not able to use time formatting.

Is there a solution available - any ideas?

best regards

-flyall-

Hello flyall,

This should do what you want:

=Format(Floor(Fields!NumField.Value / 3600), "0")

+ ":"

+ Format(Floor(Fields!NumField.Value / 60) mod 60, "00")

+ ":"

+ Format(Floor(Fields!NumField.Value mod 60), "00")

Hope this helps.

Jarret