Formulas in a Graph?? Wont work!

Hi all, Happy New yrs!

I have a problem.

I am trying to make a graph in Crystal 8.5. The values I want to use to populate this graph are from formulas.

When I am in the graph expert thing, those formulas dont show up... and i have no clue why...

Is there something special I have to do to accomplish this?

Thanks in advance!
-ThadSo should I assume this isnt possible?

Thanks!

Formatting values being passed to Sql

I have a generic page with some fields on it, I would like to be able to "groom" the values so that the data is nice and consistent.

Example: input 1 -- user types in "bob" or "BOB" or "Bob" or "JOE" etc...

the resulting record in Sql would be "Bob", "Fred", "Joe"
nice and formted out.

Any suggestions?string str1, str2 , formattedname
str1 = (name.ToUpper()).SubString(0,1)
str2 = (name.ToLower()).SubString(1)

formattedname = String.concat(str1,str2)

wher name is the string that contains the name to format. hope this helps

Formatting Question

I have an old DB2 app that has date values in 4 fields (i.e. Date1_MO, Date1_DA, Date1_CN, Date1_YR). I have several MSAccess queries that I convert this to a date by doing the following:

CDate(Date1_MO & "/" & Date1_DA & "/" & Date1_CN & Format(Date1_YR,"00"))

Piece of cake...however I am struggling with this in MSSQL.

Mostly I am fighting formatting the Year. As you can see, If I were to concatinate the above values i would come up with something like 3/9/204 for a date of March 9, 2004. (Each field is a numeric value).

I have gotten this far...

select CAST(Date1_MO as varchar(2))+ '/' + CAST(Date1_DA as varchar(2))
+ '/' + CAST(Date1_CN as varchar(2))+ CAST(Date1_YR as varchar(2)) as Date1
From tPrices

I still need to convert the whole string to a date, but more importantly, I cannot figure out how to get the last element (Year) to format as '04' instead of '4'. I can't concatinate a 0 in front of it for obvious reasons. (Athough I was tempted, just joking)

I looked through a lot of the T-SQL docs but have come up dry.

Anyway HELP!!!!!!Try this for the last 2 digits of the year:

right('0'+CAST(Date1_YR as varchar(2)), 2)|||Came to the same conclusion about the same time you replied...

Just playing with the conversion now.

Thanks for your response...

Formatting Parameter Values

I want to enter part of a parameter value as an expression similar to
formatting a global fields e.g. Format(Globals.ExecutionTime, "D").
My parameter is returning XXXX 2006, but I only want the year part of
the parameter passed to the expression.
Can this be done.?If it's a date, then =Datepart(DateInterval.Year, Parameters!myParm.Value)
or you could do the following:
=DatePart(DateInterval.Year,
DateTime.Parse(Parameters!myParam.Value.ToString()))
You could also do this:
=Parameters!myParam.Value.ToString().SubString(4,4) /* assuming 0-based
strings */
"Andy" <andy.williams1971@.ntlworld.com> wrote in message
news:1152626782.474747.226720@.35g2000cwc.googlegroups.com...
>I want to enter part of a parameter value as an expression similar to
> formatting a global fields e.g. Format(Globals.ExecutionTime, "D").
> My parameter is returning XXXX 2006, but I only want the year part of
> the parameter passed to the expression.
> Can this be done.?
>|||This worked a treat!
Thanks
Tim Dot NoSpam wrote:
> If it's a date, then =Datepart(DateInterval.Year, Parameters!myParm.Value)
> or you could do the following:
> =DatePart(DateInterval.Year,
> DateTime.Parse(Parameters!myParam.Value.ToString()))
> You could also do this:
> =Parameters!myParam.Value.ToString().SubString(4,4) /* assuming 0-based
> strings */
> "Andy" <andy.williams1971@.ntlworld.com> wrote in message
> news:1152626782.474747.226720@.35g2000cwc.googlegroups.com...
> >I want to enter part of a parameter value as an expression similar to
> > formatting a global fields e.g. Format(Globals.ExecutionTime, "D").
> >
> > My parameter is returning XXXX 2006, but I only want the year part of
> > the parameter passed to the expression.
> >
> > Can this be done.?
> >|||No worries. Remember that [almost] whatever you can do in VB.NET, you can
do in RS...
-Tim
"Andy" <andy.williams1971@.ntlworld.com> wrote in message
news:1152630530.714237.206660@.h48g2000cwc.googlegroups.com...
> This worked a treat!
> Thanks
>
> Tim Dot NoSpam wrote:
>> If it's a date, then =Datepart(DateInterval.Year,
>> Parameters!myParm.Value)
>> or you could do the following:
>> =DatePart(DateInterval.Year,
>> DateTime.Parse(Parameters!myParam.Value.ToString()))
>> You could also do this:
>> =Parameters!myParam.Value.ToString().SubString(4,4) /* assuming 0-based
>> strings */
>> "Andy" <andy.williams1971@.ntlworld.com> wrote in message
>> news:1152626782.474747.226720@.35g2000cwc.googlegroups.com...
>> >I want to enter part of a parameter value as an expression similar to
>> > formatting a global fields e.g. Format(Globals.ExecutionTime, "D").
>> >
>> > My parameter is returning XXXX 2006, but I only want the year part of
>> > the parameter passed to the expression.
>> >
>> > Can this be done.?
>> >
>

formatting issues for excel

hi,

I have a column of numbers which export fine to excel when i use the formatting '$0'.

If any of the values in the column = 0 i dont want them to show so i added this line to the value =iif(Fields!Budget.Value = 0, "", Fields!Budget.Value). This works fine except when i export to excel it now doesnt read them as values and you have to convert all the cells individually. I guess this is becuase i have substituted the string "" when the data = 0 and excel does the formatting for the whole column not each cell individually. What should i do so it exports to excel correctly? Is there another way to show nothing in a cell and it still registers as a number?

geoff

WHat about Nothing rather than "" ?

=iif(Fields!Budget.Value = 0, Nothing, Fields!Budget.Value).

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

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 a portion of a String in a TextBox

I have a report that shows a string containing "wildcard" values. I
would like to be able to change the formatting (Bold, Italicized, Red)
for the wildcard values within the string. I've been examining the
conditional formatting functionality, but it seems only to apply to the
whole textbox value not a portion of it.
Has anyone tackled this before?
TIA,
EricI had similar issue when combining string and number field and solved it with
Format function:
=Fields!industry.Value & " (" & Format(Fields!marketPct.Value, "#0.0%") & ")"
You can probably something similar for color.
"Ferd Biffle" wrote:
> I have a report that shows a string containing "wildcard" values. I
> would like to be able to change the formatting (Bold, Italicized, Red)
> for the wildcard values within the string. I've been examining the
> conditional formatting functionality, but it seems only to apply to the
> whole textbox value not a portion of it.
> Has anyone tackled this before?
> TIA,
> Eric
>

Formatted Values in Stored Procedures

Hello,
I have to put some data from an SQL Server 2000 DB to a
Word document containing some numeric values (money).
I use a stored procedure like this
select field1 + ' ' + field2 + ' ' + field3 ...
from table1 where ...
So I get back one long string. How can I get all numeric
values in this string formatted? There is no format
function like in VB/A.
Second: I'd like to have a tab (Ascii 9) instead of ' ':
How can I get this?
Klaus
www.trappdata.deFormatted like what?
Take a look at this
DECLARE @.v MONEY
SELECT @.v = 1322323.6666
SELECT CONVERT(VARCHAR,@.v,0) --1322323.67 Rounded but not formatted
SELECT CONVERT(VARCHAR,@.v,1) --1,322,323.67 Formatted with commas
SELECT CONVERT(VARCHAR,@.v,2) --1322323.6666 No formatting
If you have a decimal field it doesn't work with the convert function.
The work around is to cast it to money
DECLARE @.v2 DECIMAL (36,10)
SELECT @.v2 = 13243543.56565656
SELECT CONVERT(VARCHAR,CONVERT(MONEY,@.v2),1) --13,243,543.57 Formatted
with commas
http://sqlservercode.blogspot.com/

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 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

FORMAT_STRING "Currency" returns different currencies

hi!

i'm just getting to know SSAS. i created a standard measure and a calculated measure. the standard measure returns my values in euros, whereas the calculated measure displays them in swiss francs. the analysis server language (which one can set in sql server management studio) is German (Germany). my operating system language is German (Switzerland) though. seems as if calculated measures would read their standard value out of the system language, instead of the SSAS language - is that true? and how can it be fixed?

thank you,

Nico

Please check the following blog - http://www.sqljunkies.com/WebLog/mosha/archive/2005/10/13/mdx_format_currency.aspx it should help you to set up the formatting of currencies the way you want it.

Format Y axis label as percentage

I have entered the code as P0 when formatting my Y axis to show 0% to 100%. This showed me percent values as 0% to 10,000%. Then after reading the forums I put the major gridline interval as 0.2 to get jumps of 20% but this just puts a lot of lines on the chart as 0,0.2,0.4 till 10,000%.

How can i fix this one ..

Thanks

Kiran

Kiranvukkadala wrote:

I have entered the code as P0 when formatting my Y axis to show 0% to 100%. This showed me percent values as 0% to 10,000%. Then after reading the forums I put the major gridline interval as 0.2 to get jumps of 20% but this just puts a lot of lines on the chart as 0,0.2,0.4 till 10,000%.

How can i fix this one ..

Thanks

Kiran

Have you set the minimum value on the scale to zero and the maximum value on the scale of the Y-axis to 100?

|||

Yes I have done so. I have tried removing that too

No luck

|||

Possibly the issue is your data. What is the maximum value of your data? Have you divided this into your field?

For example, if you try to display change as a fraction of a dollar:

Then one penny should be 1% of a dollar. To get 1%, you take 1/100 = .01 (1%)

If you didn't divide by 100, however, then one penny would be 1.00 (100%).

A dime would be 1000% and so on.

One dollar would be BEHOLD 10000% (There's that 10000 percent).

Most likely you have just put the field as raw data, not as a percentage and expected the format expression to do the division for you.

Try dividing by 100!!!!

|||

I have got a stacked chart and on the data fields I have put in 2 fields

% received and % latewhic combine together to give 100%.

The data points is actuallys et to give teh actual counts of these fields.

So would this count as raw data?

If I divide this percentage number by 100 it goes really small on the graph but the scale remains the same. How can I modify the scale.

I appreciate the time.

Kiran

|||

Oh I thought you were saying your data was going to 10,000%. Try changing your Y-scale to a minimum of zero and maximum of 1.

|||

Thanks for the replies.

I have tried that possibility too. This option is successfull in creating the axis from 1% to 100% but the data itself goes wrong. There is no split between the received and late numbers and its either 100% received or 100% late. I dont know why it does that. My assumption is that since maximum value of 1 is specified only the first 1% of the data is displayed.

Kiran

Format text box with parentheses if negative

The users want to see negative values like this:

(-$1,000)

How do I format negative values that way without some major iif statement?

Hello,

I don't think you can unless you use an IIf statement.

=IIf(Fields!Amount.Value < 0, "(" & Fields!Amount.Value & ")", Fields!Amount.Value)

Hope this helps.

Jarret

|||

Haven't tested this formula I think it is similar to what Excel uses.

$#,##0_);(-$#,##0)

or

x,xx0.00_;(x,xx0.00(_)

cheers,

Andrew

|||

I have tested this one though...

#0.00;(#0.00)

cheers,

Andrew

|||

Try to use

FormatNumber(field!Num.Value, 0, 0, -1, 0)

This might work for you.

-Rohit

|||

Well, I've learned my something new for today. I'm guessing when you use a semicolon, the left side is the format for positive numbers and the right is for negative numbers? Thanks Andrew!

This one worked for me.

$#,##0.##;(-$#,##0.##)

Jarret

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 Numbers

I have a report that returns a column with number values that I want to round
to 1 decimal place. I am able to do this using the format expression #,##0.0.
However, some line items have a null value. For these line items I need to
display "-".
However, I need to do it using a format expression so that when the report
is exported to Excel the column won't be automatically converted to a string
value. Does anyone know if this is possible?Hi,
>I have a report that returns a column with number values that I want to
>round
> to 1 decimal place. I am able to do this using the format expression
> #,##0.0.
> However, some line items have a null value. For these line items I need to
> display "-".
This worked for me:
Concatenate a zero to the value...
=IIF(Fields!YourData.Value + 0 = 0, "-", Format(Fields!YourData.Value,
"#,##0.0"))
HTH!
Kind regards - Fred|||Hi Fred,
Thanks for your prompt reply! I have tried your method. This works OK in
terms of the format in the report, however, when I export the report to MS
Excel 2003, the column value is automatically converted to a text string, and
therefore no number operations such as SUM or AVERAGE can be performed on the
column. Is there a format expression I can use on the column in the report
that will prevent this from occuring?
"Fred Block" wrote:
> Hi,
> >I have a report that returns a column with number values that I want to
> >round
> > to 1 decimal place. I am able to do this using the format expression
> > #,##0.0.
> > However, some line items have a null value. For these line items I need to
> > display "-".
> This worked for me:
> Concatenate a zero to the value...
> =IIF(Fields!YourData.Value + 0 = 0, "-", Format(Fields!YourData.Value,
> "#,##0.0"))
> HTH!
> Kind regards - Fred
>
>|||On Feb 16, 8:03 am, TK-UK <T...@.discussions.microsoft.com> wrote:
> Hi Fred,
> Thanks for your prompt reply! I have tried your method. This works OK in
> terms of the format in the report, however, when I export the report to MS
> Excel 2003, the column value is automatically converted to a text string, and
> therefore no number operations such as SUM or AVERAGE can be performed on the
> column. Is there a format expression I can use on the column in the report
> that will prevent this from occuring?
>
> "Fred Block" wrote:
> > Hi,
> > >I have a report that returns a column with number values that I want to
> > >round
> > > to 1 decimal place. I am able to do this using the format expression
> > > #,##0.0.
> > > However, some line items have a null value. For these line items I need to
> > > display "-".
> > This worked for me:
> > Concatenate a zero to the value...
> > =IIF(Fields!YourData.Value + 0 = 0, "-", Format(Fields!YourData.Value,
> > "#,##0.0"))
> > HTH!
> > Kind regards - Fred- Hide quoted text -
> - Show quoted text -
Can you take the nulls out in the database query side instead? i.e.
an expression like YourData = isnull(t1.number_column,0) in SQL Server.|||Hi...
> Can you take the nulls out in the database query side instead? i.e.
> an expression like YourData = isnull(t1.number_column,0) in SQL Server.
..and/or maybe display a "zero" instead of the "-" which is most likely why
Excel is seeing strings.
Regards - Fred|||Hi again Fred,
I can't display 0 instead of nulls because the column is displaying an
average. Therefore, to display a 0 would be incorrect, and would also affect
any aggregation computations performed on the column such as an overall
average of all line items. Do you possible know of any other ways?
Tom
"Fred Block" wrote:
> Hi...
> > Can you take the nulls out in the database query side instead? i.e.
> > an expression like YourData = isnull(t1.number_column,0) in SQL Server.
> ...and/or maybe display a "zero" instead of the "-" which is most likely why
> Excel is seeing strings.
> Regards - Fred
>
>

Format data in a column

I need to format the data in 2 columns in a SQL view. Each column contains
numeric values. They both need to be formatted so that they are padded with
leading zeros if needed. Example: Column 1 is set to a length of 9, so if
the value of 999999 it needs to be formatted as 000999999. Column 2 is the
same way but has a lenght of 15. Can someone help me out with this? ThanksForgot to mention I am using a MS Access 2003 front end and a SQL DB.
"CD" wrote:
> I need to format the data in 2 columns in a SQL view. Each column contain
s
> numeric values. They both need to be formatted so that they are padded wi
th
> leading zeros if needed. Example: Column 1 is set to a length of 9, so i
f
> the value of 999999 it needs to be formatted as 000999999. Column 2 is th
e
> same way but has a lenght of 15. Can someone help me out with this? Thanks[/colo
r]|||Try,
update t1
set
c1 = right(replicate('0', 9) + c1, 9),
c2 = right(replicate('0', 15) + c2, 15)
AMB
"CD" wrote:
> Forgot to mention I am using a MS Access 2003 front end and a SQL DB.
> "CD" wrote:
>|||CD,
While we can write the T-SQL to do this, I'd recommend using Access 2003 to
handle the formatting of the data - more versatile.
HTH
Jerry
"CD" <CD@.discussions.microsoft.com> wrote in message
news:7680B761-2580-42A1-B555-B78E776A5B10@.microsoft.com...
> Forgot to mention I am using a MS Access 2003 front end and a SQL DB.
> "CD" wrote:
>|||You can create a user degined function for this:
CREATE FUNCTION fn_0Int
(
@.input as int,
@.CharLength as int
)
returns varchar(255)
AS
begin
return
right(replicate('0',@.CharLength)+convert
(varchar(255),@.input),@.CharLength)
end
GO
print dbo.fn_0Int(999999,9)
000999999
print dbo.fn_0Int(999999,15)
000000000999999
"CD" <CD@.discussions.microsoft.com> wrote in message
news:7680B761-2580-42A1-B555-B78E776A5B10@.microsoft.com...
> Forgot to mention I am using a MS Access 2003 front end and a SQL DB.
> "CD" wrote:
>|||Jerry,
I had the same thoughts, but I couldn't find where I could do this.
"Jerry Spivey" wrote:

> CD,
> While we can write the T-SQL to do this, I'd recommend using Access 2003 t
o
> handle the formatting of the data - more versatile.
> HTH
> Jerry
> "CD" <CD@.discussions.microsoft.com> wrote in message
> news:7680B761-2580-42A1-B555-B78E776A5B10@.microsoft.com...
>
>|||On Wed, 28 Sep 2005 11:55:03 -0700, CD wrote:
>I need to format the data in 2 columns in a SQL view. Each column contains
>numeric values. They both need to be formatted so that they are padded wit
h
>leading zeros if needed. Example: Column 1 is set to a length of 9, so if
>the value of 999999 it needs to be formatted as 000999999. Column 2 is the
>same way but has a lenght of 15. Can someone help me out with this? Thanks[/color
]
CREATE VIEW FormattedData
AS
SELECT RIGHT(REPLICATE('0', 9) + CAST(Column1 AS varchar), 9) AS Column1
, RIGHT(REPLICATE('0',15) + CAST(Column2 AS varchar),15) AS Column2
FROM YourTable
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Format Currency - values include NaN

Hi, my report includes a calculated column which I wish to display as
currency.
Unfortunately, some of the calculated values are non-numeric. My report
shows these values as 'NaN'. I would like to display something a bit more
meaningful to the user such as '-'. Is there a way of doing this?Nic wrote:
> I would like to display
> something a bit more meaningful to the user such as '-'. Is there a
> way of doing this?
Write a piece of custom-code like this:
Function CheckValue(value as object) as object
If isnumeric(value) Then
Return FormatCurreny(value)
Else
Return "This is no numeric value"
End If
End Function
and put an expression in the field like:
=code.CheckValue(Fields!MyField.Value)
Maybe you have to play with field-property..i assumed "Standard"
regards
Frank
www.xax.de|||Frank,
Thanks for the information. However, I tried the function you suggested and
the isnumeric function appears to return true for those values which are NaN.
In any case, my report still contains NaN values, which have not been
replaced by the 'This is no numeric value' message. I know that the function
is being used, and I can change what is displayed by changing what is
returned when the isnumeric function is evaluated as true.
Any suggestions?
"Frank Matthiesen" wrote:
> Nic wrote:
> > I would like to display
> > something a bit more meaningful to the user such as '-'. Is there a
> > way of doing this?
> Write a piece of custom-code like this:
> Function CheckValue(value as object) as object
> If isnumeric(value) Then
> Return FormatCurreny(value)
> Else
> Return "This is no numeric value"
> End If
> End Function
> and put an expression in the field like:
> =code.CheckValue(Fields!MyField.Value)
> Maybe you have to play with field-property..i assumed "Standard"
> regards
> Frank
> www.xax.de
>
>
>|||Hi,
I have done a bit more investigation, and have come up with the following,
which seems to work. I would dearly like to know whether this can be
simplified, Franks suggestion seems neater, but I cannot get it to work - any
ideas why?
Function CheckValue(value as object) as object
If IsNum(value) Then
Return FormatCurrency(value)
Else
Return "-"
End If
End Function
Function IsNum(value as object) as boolean
Try
Decimal.Parse(value)
Return True
Catch
Return False
End Try
End Function
"Nic" wrote:
> Frank,
> Thanks for the information. However, I tried the function you suggested and
> the isnumeric function appears to return true for those values which are NaN.
> In any case, my report still contains NaN values, which have not been
> replaced by the 'This is no numeric value' message. I know that the function
> is being used, and I can change what is displayed by changing what is
> returned when the isnumeric function is evaluated as true.
> Any suggestions?
> "Frank Matthiesen" wrote:
> > Nic wrote:
> >
> > > I would like to display
> > > something a bit more meaningful to the user such as '-'. Is there a
> > > way of doing this?
> >
> > Write a piece of custom-code like this:
> >
> > Function CheckValue(value as object) as object
> > If isnumeric(value) Then
> > Return FormatCurreny(value)
> > Else
> > Return "This is no numeric value"
> > End If
> > End Function
> >
> > and put an expression in the field like:
> > =code.CheckValue(Fields!MyField.Value)
> >
> > Maybe you have to play with field-property..i assumed "Standard"
> >
> > regards
> >
> > Frank
> > www.xax.de
> >
> >
> >
> >
> >

format column data

Hi,

I am entering values from .Net app to a table in Sql 2k. The Identity field needs to be in 'ABC0000012' format (3 fixed letters and 7 digits - incremented by 1). My code MUST BE within a stored_proc. I am trying to format the column to have reqd. number of preceeding zeros. How can I get those ?

Pls help.
Sam

I am not sure exactly what you mean but I am going to take a guess.... Does this help?

Foo = "ABC" & SubStr(CStr(10000000 + id),2,7)

|||

Sara.. Thanks for guess.. here is what I am trying:

I have a constant prefix to which I need to apend value in the Identity column (should be 7 digits) -- so the total alpha numeric number will be 10 places:

examples:
say ID column value is 2 my final output should look like ABC0000002
say ID column value is 123 my final output should look like ABC0000123
say ID column value is 123456 my final output should look like ABC0123456

question is how to pad ID column value with preceeding zeroes?

Thanks, Sam

|||SELECT
'ABC' + right('0000000'+ convert(varchar,[id]), 7)
, othercolumns
FROM
yourTable

Format a Percent, remove decimals.

I have the percent calculation for a pice chart in SRS, my chart has 4
values. it looks like:
=(Fields!Estimated_Value.Value)/Sum(Fields!Estimated_Value.Value)
My results look like
42.61%
25.63%
12.35%
19.41%
How do I format my result to remove the decimals and round the values
up?=Format((Fields!Estimated_Value.Value)/Sum(Fields!
Estimated_Value.Value),"###")|||Hi,
dineshasanka's answer was nearly right but it will round numbers up and down
i.e. 4.6% will round to 5% and 4.3% will round to 4%. To always round up
you need to add 0.5 to the calculated answer:-
=Format((Fields!Estimated_Value.Value)/Sum(Fields! Estimated_Value.Value) +
0.5,"###")
In the example above 4.6% + 0.5 becomes 5.1% and thus rounds down to 5%,
4.6% + 0.5 is 5.1% rounding down to 5%.
Hope it helps,
Dale
<dineshasanka@.gmail.com> wrote in message
news:ed19510b-9395-45a7-a055-4b62cba0e4df@.34g2000hsz.googlegroups.com...
> =Format((Fields!Estimated_Value.Value)/Sum(Fields!
> Estimated_Value.Value),"###")
>|||Thank you both, well my requirements changed a bit to needing to do
the same thing for a count, I opened another post for this issue but I
am not getting any feedback,
http://groups.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_thread/thread/2be09ac8fcf5e49a/28cc4cc0db0e38a2#28cc4cc0db0e38a2

format a numeric values with commas

hi,
I have requirement.
I want to format a numeric value with commas. is there
any function to use in transact-sql query to do this.
for example if the numeric value is 27519.37 then it
should be formatted as 27,519.37
if the numeric value is 1221512.34 then it should be
formatted as 1,221,512.34
regards,
hari.
declare @.list_price numeric(20,6)
set @.list_price = 1221512.34
select
convert(varchar(100),cast(@.list_price as money),1)
Steve Kass
Drew University
hari wrote:

>hi,
> I have requirement.
> I want to format a numeric value with commas. is there
>any function to use in transact-sql query to do this.
>for example if the numeric value is 27519.37 then it
>should be formatted as 27,519.37
> if the numeric value is 1221512.34 then it should be
>formatted as 1,221,512.34
>regards,
>hari.
>
>
|||hi Steve,
Thanks for that. it is working fine.
But for the whole numbers like 123456 it is not doing so.
it is formatting to 1,234,567.00 (with two zeroes after
decimal). i want it to be formatted as 1,234,567
how to format 1234567 to 1,234,567.
thanks,
hari.

>--Original Message--
>declare @.list_price numeric(20,6)
>set @.list_price = 1221512.34
>select
> convert(varchar(100),cast(@.list_price as money),1)
>Steve Kass
>Drew University
>hari wrote:
>.
>
|||Dear Friend
Do you have the VBA Code which convert a numeric values to spelling in MS Access/Visual Basic
For exmple
"1234" converts to "one thousand two hundred thirty four Only"