Formula Help :)

I have a matrix table & have a picture next to table which should display
when the percentage of the 2 colums is less than 5%.
My guess on this formula is which doesnt work.
=IIF sum((count(iif( Fields!SwitchDispId.Value, "QueueCallDetail") = 16),
"b") )/ ((count(iif( Fields!SwitchDispId.Value, "QueueCallDetail") = 1), "b")
IS < .05
Firstly i just want to total the 2 columns (disposition 16 & 1) then get a
percentage.
I would also like to get a percentage om each row as well.
Please help.
Thanks
toddI started trying to write your formula for you this morning, but discovered
I don't really understand what you are trying to do..
If you post more details ( what is the 16 and 1 for? what is "b" for. etc),
someone will be able to help you...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Tango" <Tango@.discussions.microsoft.com> wrote in message
news:528A7104-0F8A-45A7-A38A-F0CA7C776C8D@.microsoft.com...
>I have a matrix table & have a picture next to table which should display
> when the percentage of the 2 colums is less than 5%.
> My guess on this formula is which doesnt work.
> =IIF sum((count(iif( Fields!SwitchDispId.Value, "QueueCallDetail") = 16),
> "b") )/ ((count(iif( Fields!SwitchDispId.Value, "QueueCallDetail") = 1),
> "b")
> IS < .05
> Firstly i just want to total the 2 columns (disposition 16 & 1) then get a
> percentage.
> I would also like to get a percentage om each row as well.
> Please help.
> Thanks
> todd|||Sorry wayne. I have a matrix table that has 2 colum groups. Those that have a
number 16 & the second column counts those records with a number 1. the b was
just something that i coped from a different formula.
so in summery i have various groups down the left hand side. at the top of
have a count of the number of 16's in the first column & & a count of the
number 1's in the second column.
These matrix tables are hard to understand..
Thanks
Todd
"Wayne Snyder" wrote:
> I started trying to write your formula for you this morning, but discovered
> I don't really understand what you are trying to do..
> If you post more details ( what is the 16 and 1 for? what is "b" for. etc),
> someone will be able to help you...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Tango" <Tango@.discussions.microsoft.com> wrote in message
> news:528A7104-0F8A-45A7-A38A-F0CA7C776C8D@.microsoft.com...
> >I have a matrix table & have a picture next to table which should display
> > when the percentage of the 2 colums is less than 5%.
> >
> > My guess on this formula is which doesnt work.
> >
> > =IIF sum((count(iif( Fields!SwitchDispId.Value, "QueueCallDetail") = 16),
> > "b") )/ ((count(iif( Fields!SwitchDispId.Value, "QueueCallDetail") = 1),
> > "b")
> > IS < .05
> >
> > Firstly i just want to total the 2 columns (disposition 16 & 1) then get a
> > percentage.
> >
> > I would also like to get a percentage om each row as well.
> >
> > Please help.
> > Thanks
> > todd
>
>

Formatting Numbers

I am using the following format to display a percentage: ##.##%;(##.##%);"-"
It works fine for numbers like 15.34% but when the percent is a whole number
like 4% it does not display 4.00%. Can anyone help me with this format?
Thanks in Advance
OS##0.00%
"OriginalStealth" <OriginalStealth@.discussions.microsoft.com> wrote in
message news:6D66A65C-2566-4DCC-8244-7E377ADF3954@.microsoft.com...
>I am using the following format to display a percentage:
>##.##%;(##.##%);"-"
> It works fine for numbers like 15.34% but when the percent is a whole
> number
> like 4% it does not display 4.00%. Can anyone help me with this format?
> Thanks in Advance
> OS

Formatting in a report viewer

Hi,

I have a dataset and am able to display the result in a table in a report viewer (VS 2005) but my problem is, instead of displaying the records in a row, I want to display the result columnwise.

Can I do this?

Another Question:

I have a textbox in which in I am displaying some text but the text is long and want the text to be both right and left aligned(Justify). How can I do this?

Please help me.

HI

1) For display in the columnwise u can change in the database itself using procedure.

2) For display in the right side u just apply Styles

|||

You may be able to change your SQL to return your results as you want to display them in a table. But without knowing your data, it's hard to say whether that would work. Depending on your data, you may want to use a Matrix instead of a table.

You can use Center to justify your text right and left, but the textbox still needs to be large enough to display the largest amount of text your passing to it. If you have the CanGrow property set to True, the textbox will grow (height-wise) and wrap the text to new row(s) as needed.

|||

You may left, right, or center justify text in RS 2005. Full justification is not supported in the text box.

|||

you can use a matrix report and tie the same to a dataset or variables from your stored procedure. this is sort of pivot table like in excel.

|||

Hi,

Thank you all for your time but still have some problem.

I can't change the procedure since the same procedure is being used by 3 different report.

My report should look like this:(I have 5 players(Fixed) in each race but the number of laps may differ for different race. Result to be displayed for one race at a time)

Player_Name_1 Lap1_Time Lap2_Time Lap3_Time ......

I don't have any idea about matrix report. Please tell me the steps to follow or give me some available resource to refer.

I was looking for full justification. thanks for the information that it is not supported in text box.

|||

Hi,

From your description, it seems that you are unable to modify the stored procedure since it has been on live, right?

If so, I suggest that you can retrieve the return from the procedure and save it in a data container such as a dataset. And then re-modify the table structure and save it into another datatable, so in this way, you can regard the datatable as the new datasource to be bind to your report. Another way is that you can create a new procedure which return the new structure of the data table if you want.

For your second question, you can create a new Business Intelligence Projects and choose a Report Server Project Wizard template, and in this way you can select a Matrix table instead of a tabular one.

Thanks.

Formatting Currency (rounding)

I'm trying to show currency values rounded to the nearest thousand (i.e
$797,100.12 would display as $798. I can't seem to figure out the custom
format string. Can someone help me out?Karl wrote:
> I'm trying to show currency values rounded to the nearest thousand (i.e
> $797,100.12 would display as $798. I can't seem to figure out the custom
> format string. Can someone help me out?
Try using "Format" = "P0" for the field.
P as for Percent
0 as for 0 decimals
// Jonas Montonen|||"Jonas Montonen" wrote:
> Karl wrote:
> > I'm trying to show currency values rounded to the nearest thousand (i.e
> > $797,100.12 would display as $798. I can't seem to figure out the custom
> > format string. Can someone help me out?
> Try using "Format" = "P0" for the field.
> P as for Percent
> 0 as for 0 decimals
> // Jonas Montonen
>
That didn't really do the trick. I'm not trying to show the value as a
percentage, I'm trying to show it as currency but rounded to the nearest
thousand dollars.|||I have done this for norwegan format i used # ##0,.# to show 986000 as 986
hope this helps
"Karl" wrote:
>
> "Jonas Montonen" wrote:
> > Karl wrote:
> > > I'm trying to show currency values rounded to the nearest thousand (i.e
> > > $797,100.12 would display as $798. I can't seem to figure out the custom
> > > format string. Can someone help me out?
> >
> > Try using "Format" = "P0" for the field.
> > P as for Percent
> > 0 as for 0 decimals
> >
> > // Jonas Montonen
> >
> That didn't really do the trick. I'm not trying to show the value as a
> percentage, I'm trying to show it as currency but rounded to the nearest
> thousand dollars.|||I have done this for norwegan format, I used # ##0,.# to show 986000 as 986
or 986100 as 986,1 Hope this helps
"Karl" wrote:
>
> "Jonas Montonen" wrote:
> > Karl wrote:
> > > I'm trying to show currency values rounded to the nearest thousand (i.e
> > > $797,100.12 would display as $798. I can't seem to figure out the custom
> > > format string. Can someone help me out?
> >
> > Try using "Format" = "P0" for the field.
> > P as for Percent
> > 0 as for 0 decimals
> >
> > // Jonas Montonen
> >
> That didn't really do the trick. I'm not trying to show the value as a
> percentage, I'm trying to show it as currency but rounded to the nearest
> thousand dollars.

Formatting Currency

I'm trying to display currency with ZERO decimal places... the book that I
bought says to use "C" for currency, with formats it traditionally (with 2
decimal places)
the other option was to use D0 (D-zero), which works as far as the decimal
is concerned, but doesn't add the $
should I concatenate a "$" to a amount in DO format? gotta be a better wayuse C0 ... which is the same as D0 with the "$"
"Derek in Richmond" <DerekinRichmond@.discussions.microsoft.com> wrote in
message news:01169023-D1A9-4DF9-A628-EA21A7696539@.microsoft.com...
> I'm trying to display currency with ZERO decimal places... the book that
I
> bought says to use "C" for currency, with formats it traditionally (with 2
> decimal places)
> the other option was to use D0 (D-zero), which works as far as the decimal
> is concerned, but doesn't add the $
> should I concatenate a "$" to a amount in DO format? gotta be a better
way|||Use the format pattern "C0" instead of "C". Note: any digits to the right of
the decimal place will be rounded.
"Derek in Richmond" wrote:
> I'm trying to display currency with ZERO decimal places... the book that I
> bought says to use "C" for currency, with formats it traditionally (with 2
> decimal places)
> the other option was to use D0 (D-zero), which works as far as the decimal
> is concerned, but doesn't add the $
> should I concatenate a "$" to a amount in DO format? gotta be a better way

Formatting a SSN in Reporting Services

I've got a report of peoples' SSN's and Names. The SSN is stored as a string
(nvarchar(9)). I need to display the SSN in the common way: ###-##-####;
IOW, with hyphens.
In my report, the expression to display the data looks like:
=Fields!SSN.Value
When I try to format it like this...
=Format(Fields!SSN.Value,"###-##-####")
...I see only puond signs and hyphens, with no numbers.
So I convert the string to a decimal like this...
=Format(cdec(Fields!SSN.Value),"###-##-####")
...and it displays correctly in my report. But to my mind, this isn't an
elegant solution. Isn't there some way I could format the string, itself,
without having to convert it to a decimal?
TIA,
__BirmAn alternative is to use string manipulation functions. E.g.
=Left(Fields!SSN.Value, 3) & "-" & Mid(Fields!SSN.Value, 4, 2) & " - "
Right(Fields!SSN.Value, 4)
More information about these functions is available on MSDN:
* http://msdn.microsoft.com/library/en-us/vblr7/html/vafctLeft.asp
* http://msdn.microsoft.com/library/en-us/vblr7/html/vafctMid.asp
* http://msdn.microsoft.com/library/en-us/vblr7/html/vafctRight.asp
This posting is provided "AS IS" with no warranties, and confers no rights.
"Birmbear" <Birmbear@.discussions.microsoft.com> wrote in message
news:9A6DD1E3-ABDB-488B-8B56-055C8CFCC6F1@.microsoft.com...
> I've got a report of peoples' SSN's and Names. The SSN is stored as a
> string
> (nvarchar(9)). I need to display the SSN in the common way: ###-##-####;
> IOW, with hyphens.
> In my report, the expression to display the data looks like:
> =Fields!SSN.Value
> When I try to format it like this...
> =Format(Fields!SSN.Value,"###-##-####")
> ...I see only puond signs and hyphens, with no numbers.
>
> So I convert the string to a decimal like this...
> =Format(cdec(Fields!SSN.Value),"###-##-####")
> ...and it displays correctly in my report. But to my mind, this isn't an
> elegant solution. Isn't there some way I could format the string, itself,
> without having to convert it to a decimal?
> TIA,
> __Birm
>|||You could use the substring function in your query if it is a shared
dataset, that way you only have to do it once.
"Birmbear" <Birmbear@.discussions.microsoft.com> wrote in message
news:9A6DD1E3-ABDB-488B-8B56-055C8CFCC6F1@.microsoft.com...
> I've got a report of peoples' SSN's and Names. The SSN is stored as a
> string
> (nvarchar(9)). I need to display the SSN in the common way: ###-##-####;
> IOW, with hyphens.
> In my report, the expression to display the data looks like:
> =Fields!SSN.Value
> When I try to format it like this...
> =Format(Fields!SSN.Value,"###-##-####")
> ...I see only puond signs and hyphens, with no numbers.
>
> So I convert the string to a decimal like this...
> =Format(cdec(Fields!SSN.Value),"###-##-####")
> ...and it displays correctly in my report. But to my mind, this isn't an
> elegant solution. Isn't there some way I could format the string, itself,
> without having to convert it to a decimal?
> TIA,
> __Birm
>|||Robert,
That worked like a champ. (That slapping sound you hear BTW, is my palm
hitting my forehead.) I tried a similar method, using Substring in stead of
the Mid() function, and -- here's my biggest mistake -- using a plus sign as
the concatenator instead of using the ampersand. To much C# in my immediate
past, I guess. <g>
Thanks a lot for the pointer. I really appreciate your taking the time.
__Birm
"Robert Bruckner [MSFT]" wrote:
> An alternative is to use string manipulation functions. E.g.
> =Left(Fields!SSN.Value, 3) & "-" & Mid(Fields!SSN.Value, 4, 2) & " - "
> Right(Fields!SSN.Value, 4)
> More information about these functions is available on MSDN:
> * http://msdn.microsoft.com/library/en-us/vblr7/html/vafctLeft.asp
> * http://msdn.microsoft.com/library/en-us/vblr7/html/vafctMid.asp
> * http://msdn.microsoft.com/library/en-us/vblr7/html/vafctRight.asp
>
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Birmbear" <Birmbear@.discussions.microsoft.com> wrote in message
> news:9A6DD1E3-ABDB-488B-8B56-055C8CFCC6F1@.microsoft.com...
> > I've got a report of peoples' SSN's and Names. The SSN is stored as a
> > string
> > (nvarchar(9)). I need to display the SSN in the common way: ###-##-####;
> > IOW, with hyphens.
> >
> > In my report, the expression to display the data looks like:
> >
> > =Fields!SSN.Value
> >
> > When I try to format it like this...
> >
> > =Format(Fields!SSN.Value,"###-##-####")
> >
> > ...I see only puond signs and hyphens, with no numbers.
> >
> >
> > So I convert the string to a decimal like this...
> >
> > =Format(cdec(Fields!SSN.Value),"###-##-####")
> >
> > ...and it displays correctly in my report. But to my mind, this isn't an
> > elegant solution. Isn't there some way I could format the string, itself,
> > without having to convert it to a decimal?
> >
> > TIA,
> >
> > __Birm
> >
>
>|||MPF,
Yup...and that would be the elegant solution, wouldn't it? Hmmm...it's
not a shared dataset, but still...mebbe I'm going to do exactly that. Just
for yucks and education if nothing else.
Thanks!
__Birm
"MPF" wrote:
> You could use the substring function in your query if it is a shared
> dataset, that way you only have to do it once.
>
> "Birmbear" <Birmbear@.discussions.microsoft.com> wrote in message
> news:9A6DD1E3-ABDB-488B-8B56-055C8CFCC6F1@.microsoft.com...
> > I've got a report of peoples' SSN's and Names. The SSN is stored as a
> > string
> > (nvarchar(9)). I need to display the SSN in the common way: ###-##-####;
> > IOW, with hyphens.
> >
> > In my report, the expression to display the data looks like:
> >
> > =Fields!SSN.Value
> >
> > When I try to format it like this...
> >
> > =Format(Fields!SSN.Value,"###-##-####")
> >
> > ...I see only puond signs and hyphens, with no numbers.
> >
> >
> > So I convert the string to a decimal like this...
> >
> > =Format(cdec(Fields!SSN.Value),"###-##-####")
> >
> > ...and it displays correctly in my report. But to my mind, this isn't an
> > elegant solution. Isn't there some way I could format the string, itself,
> > without having to convert it to a decimal?
> >
> > TIA,
> >
> > __Birm
> >
>
>

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 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 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 for a phone number

I have a report in which I'd like to display a phone number.
Currrently, in SQL Server, the phone numbers appear as a string a numbers eg
"4255551212"
I'd like to display them with area code, then number such as "(425) 555-1212"
I've tried playing aroud with formating options and I'm stumped...
Can someone lend a hand?Hi, ReportDude,
Try
=Format(CDbl("4255551212"), "(000) 000-0000")
HTH,
Andrei.
"ReportDude" <ReportDude@.discussions.microsoft.com> wrote in message
news:2C7B4CA4-2C40-4823-AF31-6E703F308FB4@.microsoft.com...
> I have a report in which I'd like to display a phone number.
> Currrently, in SQL Server, the phone numbers appear as a string a numbers
eg
> "4255551212"
> I'd like to display them with area code, then number such as "(425)
555-1212"
> I've tried playing aroud with formating options and I'm stumped...
> Can someone lend a hand?|||Create a function i.e. and put it in the code tab section under report
properties.
public function FormatNumber(StrPhone as String) as String
dim sRetString as String = ""
if IsNothing(StrPhone)
sRetString = ""
else
sRetString = "(" & StrPhone.SubString(0, 2) & ") " &
StrPhone.SubString(3,3) & "-" & StrPhone.SubString(6,4)
end if
return sRetString
end function
Then in your grid, lets say... for the expression of the text control...
Code.FormatNumber(Fields!Phone.Value)
Now the code I just gave you might have bugs in it - as I am in a hurry :)
So test it and correct where needed - but you get the idea.
=-Chris
"ReportDude" <ReportDude@.discussions.microsoft.com> wrote in message
news:2C7B4CA4-2C40-4823-AF31-6E703F308FB4@.microsoft.com...
>I have a report in which I'd like to display a phone number.
> Currrently, in SQL Server, the phone numbers appear as a string a numbers
> eg
> "4255551212"
> I'd like to display them with area code, then number such as "(425)
> 555-1212"
> I've tried playing aroud with formating options and I'm stumped...
> Can someone lend a hand?|||I get an error that says "There is an error on line 5 of custom code:
Expression Expected"
I don't understand this - the code does have an expression...
any ideas?
"Christopher Conner" wrote:
> Create a function i.e. and put it in the code tab section under report
> properties.
> public function FormatNumber(StrPhone as String) as String
> dim sRetString as String = ""
> if IsNothing(StrPhone)
> sRetString = ""
> else
> sRetString = "(" & StrPhone.SubString(0, 2) & ") " &
> StrPhone.SubString(3,3) & "-" & StrPhone.SubString(6,4)
> end if
> return sRetString
> end function
> Then in your grid, lets say... for the expression of the text control...
> Code.FormatNumber(Fields!Phone.Value)
> Now the code I just gave you might have bugs in it - as I am in a hurry :)
> So test it and correct where needed - but you get the idea.
> =-Chris
> "ReportDude" <ReportDude@.discussions.microsoft.com> wrote in message
> news:2C7B4CA4-2C40-4823-AF31-6E703F308FB4@.microsoft.com...
> >I have a report in which I'd like to display a phone number.
> >
> > Currrently, in SQL Server, the phone numbers appear as a string a numbers
> > eg
> > "4255551212"
> >
> > I'd like to display them with area code, then number such as "(425)
> > 555-1212"
> >
> > I've tried playing aroud with formating options and I'm stumped...
> > Can someone lend a hand?
>
>|||That is because of a line break in the code... I have recopied it from dev
studio, copy this and paste it into the code block between the function name
and end function...
Dim sRetString As String = ""
If IsNothing(StrPhone) Then
sRetString = ""
Else
sRetString = "(" & StrPhone.Substring(0, 2) & ") " & StrPhone.Substring(3,
3) & "-" & StrPhone.Substring(6, 4)
End If
Return sRetString
"ReportDude" <ReportDude@.discussions.microsoft.com> wrote in message
news:B66A1226-50F4-484C-B203-8D047E97F523@.microsoft.com...
>I get an error that says "There is an error on line 5 of custom code:
> Expression Expected"
> I don't understand this - the code does have an expression...
> any ideas?
> "Christopher Conner" wrote:
>> Create a function i.e. and put it in the code tab section under report
>> properties.
>> public function FormatNumber(StrPhone as String) as String
>> dim sRetString as String = ""
>> if IsNothing(StrPhone)
>> sRetString = ""
>> else
>> sRetString = "(" & StrPhone.SubString(0, 2) & ") " &
>> StrPhone.SubString(3,3) & "-" & StrPhone.SubString(6,4)
>> end if
>> return sRetString
>> end function
>> Then in your grid, lets say... for the expression of the text control...
>> Code.FormatNumber(Fields!Phone.Value)
>> Now the code I just gave you might have bugs in it - as I am in a hurry
>> :)
>> So test it and correct where needed - but you get the idea.
>> =-Chris
>> "ReportDude" <ReportDude@.discussions.microsoft.com> wrote in message
>> news:2C7B4CA4-2C40-4823-AF31-6E703F308FB4@.microsoft.com...
>> >I have a report in which I'd like to display a phone number.
>> >
>> > Currrently, in SQL Server, the phone numbers appear as a string a
>> > numbers
>> > eg
>> > "4255551212"
>> >
>> > I'd like to display them with area code, then number such as "(425)
>> > 555-1212"
>> >
>> > I've tried playing aroud with formating options and I'm stumped...
>> > Can someone lend a hand?
>>

Formating Dates

In my chart report, my x-axis date is displaying as 9/1/05, 10/1/05, etc. I
would like to display the date as 9/05, 10/05, etc, is it possible?
Thanks,
JimmyTry the value in the format tab if the list provides the format select it .
otherwise try giving dd/mm in the text box of the format tab.
Amarnath
"jcl_tw" wrote:
> In my chart report, my x-axis date is displaying as 9/1/05, 10/1/05, etc. I
> would like to display the date as 9/05, 10/05, etc, is it possible?
> Thanks,
> Jimmy|||Amarnath,
Tried using "mm/yy" in the Format Code field. The date shows up as 00/05
for all the months.
Jimmy
"Amarnath" wrote:
> Try the value in the format tab if the list provides the format select it .
> otherwise try giving dd/mm in the text box of the format tab.
> Amarnath
> "jcl_tw" wrote:
> > In my chart report, my x-axis date is displaying as 9/1/05, 10/1/05, etc. I
> > would like to display the date as 9/05, 10/05, etc, is it possible?
> >
> > Thanks,
> > Jimmy|||Try using instr function to seperate the mm/yy ofcourse use date to string
convertion.
Amarnath
"jcl_tw" wrote:
> Amarnath,
> Tried using "mm/yy" in the Format Code field. The date shows up as 00/05
> for all the months.
> Jimmy
> "Amarnath" wrote:
> > Try the value in the format tab if the list provides the format select it .
> > otherwise try giving dd/mm in the text box of the format tab.
> >
> > Amarnath
> >
> > "jcl_tw" wrote:
> >
> > > In my chart report, my x-axis date is displaying as 9/1/05, 10/1/05, etc. I
> > > would like to display the date as 9/05, 10/05, etc, is it possible?
> > >
> > > Thanks,
> > > Jimmy|||Jimmy,
Why dont you try this
=Format(DatePart("D",Fields!Date.Value),"#0") & "\" &
Format(Datepart("M",Fields!Date.Value),"#0")
jcl_tw wrote:
> Amarnath,
> Tried using "mm/yy" in the Format Code field. The date shows up as 00/05
> for all the months.
> Jimmy
> "Amarnath" wrote:
> > Try the value in the format tab if the list provides the format select it .
> > otherwise try giving dd/mm in the text box of the format tab.
> >
> > Amarnath
> >
> > "jcl_tw" wrote:
> >
> > > In my chart report, my x-axis date is displaying as 9/1/05, 10/1/05, etc. I
> > > would like to display the date as 9/05, 10/05, etc, is it possible?
> > >
> > > Thanks,
> > > Jimmy|||RajDeep,
Try your solution but unfortunately, the "Format Code" field under "Show
Labels" in the chart doesn't allows that many characters.
Jimmy
"RajDeep" wrote:
> Jimmy,
> Why dont you try this
> =Format(DatePart("D",Fields!Date.Value),"#0") & "\" &
> Format(Datepart("M",Fields!Date.Value),"#0")
> jcl_tw wrote:
> > Amarnath,
> >
> > Tried using "mm/yy" in the Format Code field. The date shows up as 00/05
> > for all the months.
> >
> > Jimmy
> >
> > "Amarnath" wrote:
> >
> > > Try the value in the format tab if the list provides the format select it .
> > > otherwise try giving dd/mm in the text box of the format tab.
> > >
> > > Amarnath
> > >
> > > "jcl_tw" wrote:
> > >
> > > > In my chart report, my x-axis date is displaying as 9/1/05, 10/1/05, etc. I
> > > > would like to display the date as 9/05, 10/05, etc, is it possible?
> > > >
> > > > Thanks,
> > > > Jimmy
>|||use MM/yy
mm refers to minutes, MM to month.
jcl_tw wrote:
> RajDeep,
> Try your solution but unfortunately, the "Format Code" field under "Show
> Labels" in the chart doesn't allows that many characters.
> Jimmy
> "RajDeep" wrote:
> > Jimmy,
> >
> > Why dont you try this
> >
> > =Format(DatePart("D",Fields!Date.Value),"#0") & "\" &
> > Format(Datepart("M",Fields!Date.Value),"#0")
> >
> > jcl_tw wrote:
> > > Amarnath,
> > >
> > > Tried using "mm/yy" in the Format Code field. The date shows up as 00/05
> > > for all the months.
> > >
> > > Jimmy
> > >
> > > "Amarnath" wrote:
> > >
> > > > Try the value in the format tab if the list provides the format select it .
> > > > otherwise try giving dd/mm in the text box of the format tab.
> > > >
> > > > Amarnath
> > > >
> > > > "jcl_tw" wrote:
> > > >
> > > > > In my chart report, my x-axis date is displaying as 9/1/05, 10/1/05, etc. I
> > > > > would like to display the date as 9/05, 10/05, etc, is it possible?
> > > > >
> > > > > Thanks,
> > > > > Jimmy
> >
> >|||Jen,
It works!
Thanks,
Jimmy
"Jen" wrote:
> use MM/yy
> mm refers to minutes, MM to month.
> jcl_tw wrote:
> > RajDeep,
> >
> > Try your solution but unfortunately, the "Format Code" field under "Show
> > Labels" in the chart doesn't allows that many characters.
> >
> > Jimmy
> >
> > "RajDeep" wrote:
> >
> > > Jimmy,
> > >
> > > Why dont you try this
> > >
> > > =Format(DatePart("D",Fields!Date.Value),"#0") & "\" &
> > > Format(Datepart("M",Fields!Date.Value),"#0")
> > >
> > > jcl_tw wrote:
> > > > Amarnath,
> > > >
> > > > Tried using "mm/yy" in the Format Code field. The date shows up as 00/05
> > > > for all the months.
> > > >
> > > > Jimmy
> > > >
> > > > "Amarnath" wrote:
> > > >
> > > > > Try the value in the format tab if the list provides the format select it .
> > > > > otherwise try giving dd/mm in the text box of the format tab.
> > > > >
> > > > > Amarnath
> > > > >
> > > > > "jcl_tw" wrote:
> > > > >
> > > > > > In my chart report, my x-axis date is displaying as 9/1/05, 10/1/05, etc. I
> > > > > > would like to display the date as 9/05, 10/05, etc, is it possible?
> > > > > >
> > > > > > Thanks,
> > > > > > Jimmy
> > >
> > >
>|||you dont need to mention anything in the format code,for value you will
have Fields!Date.Value and under that if you can see some thing like
label,write the expression posted there.
even though you got the solution ,it will be useful when it is
important
Regards
Raj Deep.A
jcl_tw wrote:
> RajDeep,
> Try your solution but unfortunately, the "Format Code" field under "Show
> Labels" in the chart doesn't allows that many characters.
> Jimmy
> "RajDeep" wrote:
> > Jimmy,
> >
> > Why dont you try this
> >
> > =Format(DatePart("D",Fields!Date.Value),"#0") & "\" &
> > Format(Datepart("M",Fields!Date.Value),"#0")
> >
> > jcl_tw wrote:
> > > Amarnath,
> > >
> > > Tried using "mm/yy" in the Format Code field. The date shows up as 00/05
> > > for all the months.
> > >
> > > Jimmy
> > >
> > > "Amarnath" wrote:
> > >
> > > > Try the value in the format tab if the list provides the format select it .
> > > > otherwise try giving dd/mm in the text box of the format tab.
> > > >
> > > > Amarnath
> > > >
> > > > "jcl_tw" wrote:
> > > >
> > > > > In my chart report, my x-axis date is displaying as 9/1/05, 10/1/05, etc. I
> > > > > would like to display the date as 9/05, 10/05, etc, is it possible?
> > > > >
> > > > > Thanks,
> > > > > Jimmy
> >
> >

Formating Date Display

How to I set the =User!Language on a Reporting Services report to display
based on the locale of the user. I'd like to make sure date fields are
displayed using the correct format.On Nov 19, 1:48 am, Greg Larsen <gregalar...@.removeit.msn.com> wrote:
> How to I set the =User!Language on a Reporting Services report to display
> based on the locale of the user. I'd like to make sure date fields are
> displayed using the correct format.
I found an example online that shows it should be something like:
lblTime.Text = String.Format("{0:T}", rightNow);
lblDate.Text = String.Format("{0:d}", rightNow);
-- Scott

Formating bound data

How do I change, or add to, the following code so as to be able to format the display:

this.txtAppointmentDate.DataBindings.Add(new Binding ("Text", dsData.Tables["Results"],"AppointmentDate")

The data is stored in SQL Server as datetime and I want only the date portion to show in the TextBox.

Thanks in advance

Hi,

thats more a GUI related question, but anyway:

http://www.codeproject.com/vb/net/databindingconcepts.asp

HTH;, Jens Suessmeyer.

http://ww.sqlserver2005.de

Formating a number in VB 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 tryingto use the format on a string it give me a worning and print #ERORR inthe 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!

Hi,

had a similar problem recently.

Try returning "Nothing" (it's a keyword, sth like null), instead of empty string.

|||

Hi,

I tried that but if I return a number it showen as 0.0 instead of an empty cell :(

Any more ideas?

Thanks.

|||

Hmm,

it works from me. However, I set the value as an expression "Iif(...)" no custom functions defined.

And I've set the "Format" property of that cell to "n2".

|||

Well, its didn't worked for me. maybe it because I'm useing Cutom code function to return the value.

I also trued th N2 format but same resualt.

Thanks for the help!

Formating

Hi ,
I want to display the a numeric field column in two decimal
places.
48.00
48.56
But in the Qty is a Number then it is showing as 48.0. How I can get in two
decimal places
Need Help
BabzWhere do you want to display these numbers? What tool are you talking about?
This is an issue with the client tool you are using.
--
Linchi Shea
linchi_shea@.NOSPAMml.com
"Babz" <notknown@.nodomain.com> wrote in message
news:eL0dzx4lDHA.2080@.TK2MSFTNGP10.phx.gbl...
> Hi ,
> I want to display the a numeric field column in two decimal
> places.
> 48.00
> 48.56
> But in the Qty is a Number then it is showing as 48.0. How I can get in
two
> decimal places
> Need Help
> Babz
>|||Hi
I want this thing in the sql query only.
In the database it is stored as 48.0000. 54.0000, 76.5786.
Even after rounding the value to 2 decimals it is showing in the 4 decimal
places.
I want the data in the following format
48.00
54.00
76.58
Need Help
Babz
"Linchi Shea" <linchi_shea@.NOSPAMml.com> wrote in message
news:Oi#15D5lDHA.2616@.TK2MSFTNGP11.phx.gbl...
> Where do you want to display these numbers? What tool are you talking
about?
> This is an issue with the client tool you are using.
> --
> Linchi Shea
> linchi_shea@.NOSPAMml.com
>
> "Babz" <notknown@.nodomain.com> wrote in message
> news:eL0dzx4lDHA.2080@.TK2MSFTNGP10.phx.gbl...
> > Hi ,
> > I want to display the a numeric field column in two decimal
> > places.
> >
> > 48.00
> > 48.56
> > But in the Qty is a Number then it is showing as 48.0. How I can get in
> two
> > decimal places
> >
> > Need Help
> >
> > Babz
> >
> >
>|||I strongly suggest you follow Linchi's suggestion and have your client tool to format the data. If
you are saying that you are using Query Analyzer, I cannot imagine the format being relevant, as I
trust that you can understand the difference between a value and the presentation and format of such
a value. I do assume that you don't use Query Analyzer to present data to end users.
Having said that, if you *really* want to do this in QA, you need to convert the data to a datatype
for which QA presents two digits to the right of the decimal point. ROUND does *not* change
datatype, it only changes the value. So, you need to use CAST for this. Example:
SELECT CAST(3.1 AS decimal(5,2))
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Babz" <notknown@.nodomain.com> wrote in message news:OfDZbj5lDHA.3688@.TK2MSFTNGP11.phx.gbl...
> Hi
> I want this thing in the sql query only.
> In the database it is stored as 48.0000. 54.0000, 76.5786.
> Even after rounding the value to 2 decimals it is showing in the 4 decimal
> places.
> I want the data in the following format
> 48.00
> 54.00
> 76.58
> Need Help
> Babz
>
> "Linchi Shea" <linchi_shea@.NOSPAMml.com> wrote in message
> news:Oi#15D5lDHA.2616@.TK2MSFTNGP11.phx.gbl...
> > Where do you want to display these numbers? What tool are you talking
> about?
> > This is an issue with the client tool you are using.
> >
> > --
> > Linchi Shea
> > linchi_shea@.NOSPAMml.com
> >
> >
> > "Babz" <notknown@.nodomain.com> wrote in message
> > news:eL0dzx4lDHA.2080@.TK2MSFTNGP10.phx.gbl...
> > > Hi ,
> > > I want to display the a numeric field column in two decimal
> > > places.
> > >
> > > 48.00
> > > 48.56
> > > But in the Qty is a Number then it is showing as 48.0. How I can get in
> > two
> > > decimal places
> > >
> > > Need Help
> > >
> > > Babz
> > >
> > >
> >
> >
>|||THANX FOR REPLYING. I am using dataset object to fetch the data and binding
it with the data list view. In this case how I can format in the front end.
I Know it is .Net related question but still any body knows this can answer
for this.
Thanx and Regards
Babz
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:eeYBLp5lDHA.2364@.TK2MSFTNGP11.phx.gbl...
> I strongly suggest you follow Linchi's suggestion and have your client
tool to format the data. If
> you are saying that you are using Query Analyzer, I cannot imagine the
format being relevant, as I
> trust that you can understand the difference between a value and the
presentation and format of such
> a value. I do assume that you don't use Query Analyzer to present data to
end users.
> Having said that, if you *really* want to do this in QA, you need to
convert the data to a datatype
> for which QA presents two digits to the right of the decimal point. ROUND
does *not* change
> datatype, it only changes the value. So, you need to use CAST for this.
Example:
> SELECT CAST(3.1 AS decimal(5,2))
> --
> Tibor Karaszi, SQL Server MVP
> Archive at: http://groups.google.com/groups?oi=djq&as
ugroup=microsoft.public.sqlserver
>
> "Babz" <notknown@.nodomain.com> wrote in message
news:OfDZbj5lDHA.3688@.TK2MSFTNGP11.phx.gbl...
> > Hi
> > I want this thing in the sql query only.
> > In the database it is stored as 48.0000. 54.0000, 76.5786.
> >
> > Even after rounding the value to 2 decimals it is showing in the 4
decimal
> > places.
> >
> > I want the data in the following format
> >
> > 48.00
> > 54.00
> > 76.58
> >
> > Need Help
> >
> > Babz
> >
> >
> >
> > "Linchi Shea" <linchi_shea@.NOSPAMml.com> wrote in message
> > news:Oi#15D5lDHA.2616@.TK2MSFTNGP11.phx.gbl...
> > > Where do you want to display these numbers? What tool are you talking
> > about?
> > > This is an issue with the client tool you are using.
> > >
> > > --
> > > Linchi Shea
> > > linchi_shea@.NOSPAMml.com
> > >
> > >
> > > "Babz" <notknown@.nodomain.com> wrote in message
> > > news:eL0dzx4lDHA.2080@.TK2MSFTNGP10.phx.gbl...
> > > > Hi ,
> > > > I want to display the a numeric field column in two
decimal
> > > > places.
> > > >
> > > > 48.00
> > > > 48.56
> > > > But in the Qty is a Number then it is showing as 48.0. How I can
get in
> > > two
> > > > decimal places
> > > >
> > > > Need Help
> > > >
> > > > Babz
> > > >
> > > >
> > >
> > >
> >
> >
>|||Sorry, I'm not that versed with the GUI elements in .NET. Perhaps some other jumps in here, or you
might want to post a new post with a descriptive subject and also include some relevant .net group
in that post.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Babz" <notknown@.nodomain.com> wrote in message news:e8WAyV7lDHA.2272@.tk2msftngp13.phx.gbl...
> THANX FOR REPLYING. I am using dataset object to fetch the data and binding
> it with the data list view. In this case how I can format in the front end.
> I Know it is .Net related question but still any body knows this can answer
> for this.
> Thanx and Regards
> Babz
> "Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> wrote in message news:eeYBLp5lDHA.2364@.TK2MSFTNGP11.phx.gbl...
> > I strongly suggest you follow Linchi's suggestion and have your client
> tool to format the data. If
> > you are saying that you are using Query Analyzer, I cannot imagine the
> format being relevant, as I
> > trust that you can understand the difference between a value and the
> presentation and format of such
> > a value. I do assume that you don't use Query Analyzer to present data to
> end users.
> >
> > Having said that, if you *really* want to do this in QA, you need to
> convert the data to a datatype
> > for which QA presents two digits to the right of the decimal point. ROUND
> does *not* change
> > datatype, it only changes the value. So, you need to use CAST for this.
> Example:
> > SELECT CAST(3.1 AS decimal(5,2))
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > Archive at: http://groups.google.com/groups?oi=djq&as
> ugroup=microsoft.public.sqlserver
> >
> >
> > "Babz" <notknown@.nodomain.com> wrote in message
> news:OfDZbj5lDHA.3688@.TK2MSFTNGP11.phx.gbl...
> > > Hi
> > > I want this thing in the sql query only.
> > > In the database it is stored as 48.0000. 54.0000, 76.5786.
> > >
> > > Even after rounding the value to 2 decimals it is showing in the 4
> decimal
> > > places.
> > >
> > > I want the data in the following format
> > >
> > > 48.00
> > > 54.00
> > > 76.58
> > >
> > > Need Help
> > >
> > > Babz
> > >
> > >
> > >
> > > "Linchi Shea" <linchi_shea@.NOSPAMml.com> wrote in message
> > > news:Oi#15D5lDHA.2616@.TK2MSFTNGP11.phx.gbl...
> > > > Where do you want to display these numbers? What tool are you talking
> > > about?
> > > > This is an issue with the client tool you are using.
> > > >
> > > > --
> > > > Linchi Shea
> > > > linchi_shea@.NOSPAMml.com
> > > >
> > > >
> > > > "Babz" <notknown@.nodomain.com> wrote in message
> > > > news:eL0dzx4lDHA.2080@.TK2MSFTNGP10.phx.gbl...
> > > > > Hi ,
> > > > > I want to display the a numeric field column in two
> decimal
> > > > > places.
> > > > >
> > > > > 48.00
> > > > > 48.56
> > > > > But in the Qty is a Number then it is showing as 48.0. How I can
> get in
> > > > two
> > > > > decimal places
> > > > >
> > > > > Need Help
> > > > >
> > > > > Babz
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||Have you ensured that your client tool actually holds the value with decimal
places?
The screen output most likely reflects the actual value held.
"Babz" <notknown@.nodomain.com> wrote in message
news:eL0dzx4lDHA.2080@.TK2MSFTNGP10.phx.gbl...
> Hi ,
> I want to display the a numeric field column in two decimal
> places.
> 48.00
> 48.56
> But in the Qty is a Number then it is showing as 48.0. How I can get in
two
> decimal places
> Need Help
> Babz
>

Format to 2 decimal places?

Hi, i need to display my data in 2 decimal places but now i'm getting results after some calculation (for eg. 2.336224). How can i round it off to 2.34?

Hi,

In the report designer, right click the cell where you want to display the above number then go to properties and then to the format tab and enter Format Code as "0.00". It should format the 2.336224 to 2.34.

Hope it helps.

Rajiv

|||hehe.. thanks so much! It's so helpful.. I came across this when i was exploring the program but didnt occur to me when i need it.. thanks!

Format similar to Excel accounting format (paren around negatives, - for 0)

I know that it's possible to get () around negatives in Reporting
Services, but I have yet to find a way to have it display 0 as a -
(hyphen). I've found that I can use an IIF but that means that the -
will be a string and not actually representative of 0 when exported to
Excel. Anyone have a solution, or is this just a shortcoming of
Reporting Services?On Aug 17, 12:19 pm, TrueDis <swro...@.gmail.com> wrote:
> I know that it's possible to get () around negatives in Reporting
> Services, but I have yet to find a way to have it display 0 as a -
> (hyphen). I've found that I can use an IIF but that means that the -
> will be a string and not actually representative of 0 when exported to
> Excel. Anyone have a solution, or is this just a shortcoming of
> Reporting Services?
Have you set the format property using the iif statement and then set
the value property as Nothing or 0 if below zero? If so, you should be
able to avoid the string representation in Excel. You should use
something like this in the Format property.
=iif(Fields!SomeItem.Value < 0, "-(#,0)", "#,0")
and then use the regular expression in the Value property:
=Fields!SomeItem.Value
If this does not work out, you should be able to use the cast as int
functionality in the value property.
=iif(Fields!SomeItem.Value < 0, CInt(Nothing), Fields!SomeItem.Value)
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||On Aug 18, 9:21 pm, EMartinez <emartinez...@.gmail.com> wrote:
> On Aug 17, 12:19 pm, TrueDis <swro...@.gmail.com> wrote:
> > I know that it's possible to get () around negatives in Reporting
> > Services, but I have yet to find a way to have it display 0 as a -
> > (hyphen). I've found that I can use an IIF but that means that the -
> > will be a string and not actually representative of 0 when exported to
> > Excel. Anyone have a solution, or is this just a shortcoming of
> > Reporting Services?
> Have you set the format property using the iif statement and then set
> the value property as Nothing or 0 if below zero? If so, you should be
> able to avoid the string representation in Excel. You should use
> something like this in the Format property.
> =iif(Fields!SomeItem.Value < 0, "-(#,0)", "#,0")
> and then use the regular expression in the Value property:
> =Fields!SomeItem.Value
> If this does not work out, you should be able to use the cast as int
> functionality in the value property.
> =iif(Fields!SomeItem.Value < 0, CInt(Nothing), Fields!SomeItem.Value)
> Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
What I'm hoping to do is have 0 show up as a dash like this: -
Will this do that?

format of point labels

Can I display number values with their corresponding percentage values in pie
charts like I can in Excel? (Ex. 45, 7.7%) If so, how? I've gotten either
one or the other to appear, but not both at the same time.You would need to write an expression to concatenate the calculated values.
E.g.:
=Sum(Fields!x.Value) & ", " & Format(Sum(Fields!x.Value) /
Sum(Fields!x.Value, "DataSetName"), "P1")
Robert M. Bruckner
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"dataGirl" <dataGirl@.discussions.microsoft.com> wrote in message
news:8025F487-40B6-46E8-97D4-FABA0507B9EB@.microsoft.com...
> Can I display number values with their corresponding percentage values in
> pie
> charts like I can in Excel? (Ex. 45, 7.7%) If so, how? I've gotten
> either
> one or the other to appear, but not both at the same time.

Format numeric to display dollar value $1,000.00

Hi,
Currently, I have a numeric field stored a value of 1000, how can I covert
it to varchar value and display it as $1,000.00
Thanks>> Currently, I have a numeric field stored a value of 1000, how can I cover
t it to varchar value and display it as $1,000.00 <<
Why are you formatting data in the back end? The basic principle of a
tiered architecture is that display is done in the front end and never
in the back end. This a more basic programming principle than just SQL
and RDBMS.
You are the kid in the class that the other students make fun of
because he just does not understand ...|||I need to store a string of combine text that should should appear ....
$1,000.00
I'm wondering, if there that's a way to do that.
"--CELKO--" wrote:

> Why are you formatting data in the back end? The basic principle of a
> tiered architecture is that display is done in the front end and never
> in the back end. This a more basic programming principle than just SQL
> and RDBMS.
> You are the kid in the class that the other students make fun of
> because he just does not understand ...
>|||The following example illustrates how this can be achieved:
CREATE TABLE dbo.currency
(
col1 INT
)
INSERT dbo.currency SELECT 1000
INSERT dbo.currency SELECT 10
INSERT dbo.currency SELECT 999999
SELECT '$' + CONVERT(VARCHAR(20), CAST(col1 AS MONEY), 1)
FROM dbo.currency
HTH
- Peter Ward
WARDY IT Solutions
"slimla" wrote:
> I need to store a string of combine text that should should appear ....
> $1,000.00
> I'm wondering, if there that's a way to do that.
>
> "--CELKO--" wrote:
>|||Extract from my blog:
http://sqlblogcasts.com/blogs/tonyr...1/429.aspx....
Introduction
Application programmers and Business Intelligent professionals are faced
with having to format data - taylored into what the users want. We have two
choices as to where we do this processing, keep it in the database using the
facilities of the database engine, for instance T-SQL, standard SQL dialect,
CLR, XML or whatever the product has to offer or we can bring the data out
of the database and down into the front end application or middle tier and
format the data there (keep the database for store and retreive only).
My Opinion
The IT industry is full of rules and best practices unfortunetly some of
these rules and best practices aren't based on current technology or
business problems, in fact some of the rules and best practices are based on
techniques adopted in the 70's and 80's on mainframes or early client server
architecture.
No product is just a database anymore, sure SQL Server stores and retrieves
data but it also offers us a lot more, in fact its moving more towards being
the middle and data tiers in the three tier architecture now that SQL Server
can be a web service and the inclusion of CLR.
Data formatting, be it paging, value concatenation should always been done
where it is most efficient to do it. Consider (and benchmark) where its most
efficient to do this, would you really drag 1 million rows into the middle
tier or client browser only to get page 2 of 20 rows? It doesn't make sense.
Relating this to a well known expert, --CELKO--, he states that you should
NEVER do formatting in the database and it should always be done in the
front end. Think this through, take value concatenation for instance, say
you need to create a list of values for a given product category, for
instance for a given person show the mailing lists they belong to. In the
database this will be held in rows, so if a person belongs to 5 mailing
lists there will be 5 rows, now, say the user requires the values to be
normalised so they are displayed on just one line entry. We have two
choices, drag the 5 rows down to the front end or middle tier and use a 4GL
to process the data or we can use some of the extensions available in SQL
Server to do this.
Example
create table mailing_list (
individual_name nvarchar(100) not null,
list_name nvarchar(10) not null
)
insert mailing_list ( individual_name, list_name ) values( 'tony r', 'List
A' )
insert mailing_list ( individual_name, list_name ) values( 'tony r', 'List
B' )
insert mailing_list ( individual_name, list_name ) values( 'tony r', 'List
C' )
insert mailing_list ( individual_name, list_name ) values( 'joe r', 'List
A' )
insert mailing_list ( individual_name, list_name ) values( 'joe r', 'List
B' )
insert mailing_list ( individual_name, list_name ) values( 'alex r', 'List
A' )
select distinct
individual_name,
list = substring(
( select ', ' + list_name as [text()]
from mailing_list m2
where m2.individual_name = m1.individual_name
for xml path(''), elements )
, 3, 100 )
from mailing_list m1
Gives this result :-
alex r List A
joe r List A, List B
tony r List A, List B, List C
Now, just how easy was that! It only takes a few lines of SQL and you have
also saved a lot of network traffic back out to the middle tier or front
end.
So, my point is this: whatever you do - always think through what you are
doing, don't just follow 'rules' blindly!
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1150411335.797938.9320@.f6g2000cwb.googlegroups.com...
> Why are you formatting data in the back end? The basic principle of a
> tiered architecture is that display is done in the front end and never
> in the back end. This a more basic programming principle than just SQL
> and RDBMS.
> You are the kid in the class that the other students make fun of
> because he just does not understand ...
>|||slimla wrote:
> Hi,
> Currently, I have a numeric field stored a value of 1000, how can I covert
> it to varchar value and display it as $1,000.00
> Thanks
you can also set up a compute column to have string instead of integer.
select '$' + cast(<int value> as varchar(20))|||Tony,
Very well said!
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam|||Thanks All
"P. Ward" wrote:
> The following example illustrates how this can be achieved:
> CREATE TABLE dbo.currency
> (
> col1 INT
> )
> INSERT dbo.currency SELECT 1000
> INSERT dbo.currency SELECT 10
> INSERT dbo.currency SELECT 999999
> SELECT '$' + CONVERT(VARCHAR(20), CAST(col1 AS MONEY), 1)
> FROM dbo.currency
> HTH
> - Peter Ward
> WARDY IT Solutions
>
> "slimla" wrote:
>|||I like that method, sort of gives you a formatting tier within the data
tier - nice and central and it doesn't effect storage, gives you a standard
view for people to use and code against... A lot more easier and
maintainable than using views as well...
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"BurgerKING" <syi916@.gmail.com> wrote in message
news:1150461726.914638.202330@.i40g2000cwc.googlegroups.com...
> slimla wrote:
>
> you can also set up a compute column to have string instead of integer.
> select '$' + cast(<int value> as varchar(20))
>

format number with decimals

I need to know to format a field that has decimals.

If the decimal portion is .00 then I do not want to display it.

For example:
1234.01 would display as 1234.01
1234.00 would display as 1234

Any help would be great thanks.

Dean

I found the answer:

if int({field})= {field} then
formula = totext({field},"##########")
else
formula = totext({field},"##########.##")
end ifHi,
Try this

if right(Cstr({feild}),3)=".00" then
mid(cstr({feild}),1,len(cstr({feild}))-3)
else
cstr({feild});
Madhivanan