Formula Help

I have a report that I need to take the average of a total(s)...
For example
I have a field that is a sum of units(number) and then I have a field that is a count of payments(number). I need to average these two fields,so that I have the average # of payments?How can I do this?I would try creating a new formula and expand the "REPORT FIELDS" in the tree within the formula editor (2nd one from left) and pull the applicable summary fields in. I'm not positive that would work as I haven't tried it before.|||I've tried that but it gives me an error of

The Summary/Running Total field cannot be created.|||I just tested it in a report I have and it worked just fine. Where are you trying to use it? You will not be able to use it in the details, but it would have to fall in the same group footer as the summary.|||These calculations are in the group footer. I need to average the grand totals.

What formula are you using? These are the two fields that I need to use

(Sum ({CLCHARGE.CUNITS}, {CLPAYMNT.COMPANY}) and
Count({CLPAYMNT.CHGID},{CLPAYMNT.COMPANY})|||Sorry I didn't ask this in the previous post, but what version of CR are you using? I am on 10, so if you are earlier, maybe it doesn't support those calculations in the same way it does for me. If you are 10 or XI, then I would need to ask - did you create formulas for the summaries or did you use the INSERT / SUMMARY from the menu bar? If you created the summaries by letting CR do it via the INSERT / SUMMARY, then you create a separate formula that drags those summaries in to the formula editor (typing a "/" between them), it should work.|||I am using 8.5|||It may be that you can't do it from 8.5. I'm sorry that I couldn't help you.|||I was afraid of that...Thanks for you help anyway.|||Hi there I have an average formula setup in one of my reports using CR 8.5.

whileprintingrecords;

numbervar TotalTsec;
local numbervar AverageFix:=TotalTsec/DistinctCount ({SW_CASE.swCaseId});
numbervar LenOfDay;

Hope this points you in the right direction !

Many Thanks

Formula for Graph Size

I have a report control in my report. But I want to expand graph depending on the selected number of months (Parameter) by users.

But there is no formula option for graph control. Is there any workaround?

You might be able to do something with padding. But that would not change the space your graph takes up in the page, you would just have more (or less) white space, depending on your current data.

>L<

sql

Formattting numbers

I would like to format the number 12345 into 12,345. How can I achieve this?

in C#:
int num = 1234567890;
String numFormatted = num.ToString("#,#");

Check out this documentation for more specifics on number formatting:

http://msdn2.microsoft.com/en-us/library/0c899ak8(VS.71).aspx

|||

I would like to acheive this in reporting services.

|||

I found out. I can make it using the format tab in properties of the textbox

Formatting totals with the table footer

Here's a scenario that i'm trying to figure out.

In the table details, i return order number, item, manufacturer, and total cost of the order.

This is what it originally looked like:

Order Number Item Manufacturer Total Order Cost

1 portable DVD Company A $100

1 portable DVD Company B $100

1 portable DVD Company C $100

2 portable DVD Company B $100

2 portable DVD Company D $100

2 portable DVD Company F $100

Grand Total $600

I can get the table to look like this after hiding duplicates:

Order Number Item Manufacturer Total Order Cost

1 portable DVD Company A $100

1 portable DVD Company B $100

1 portable DVD Company C $100

2 portable DVD Company B $100

2 portable DVD Company D $100

2 portable DVD Company F $100

Grand Total $600

The problem is the grand total. It should be $200 but it takes in the all total costs in the row because I have:

=FormatCurrency(Sum(Fields!TotalCost.Value)) in the footer and it'll sum up all.

I'm stumped here. Any suggestions are greatly appreciated.

Thanks a lot for taking the time to read.

Hiding duplicates will invariably give you wrong values especially when adding. I have had the same scenario and I found the answer here on this fourm long time back. You need to do a sum of TotalOrderCost for distinct values of OrderNumber. In the Code block (available from the Report Properties dialog), you would add:

Dim orderIDs As System.Collections.Hashtable
Dim total As Double

Function MyFunc(ByVal orderID As Object, ByVal TotalOrderCost As Obect) As Double
If (orderIDs Is Nothing) Then
orderIDs = New System.Collections.Hashtable
End If
If (orderID Is Nothing) Then
MyFunc = total
Else
If (Not orderIDs.Contains(orderID)) Then
total = total + TotalOrderCost
orderIDs.Add(orderID, TotalOrderCost)
End If
MyFunc = total
End If
End Function

3.) In your report, you add a hidden textbox with the value expression to compute the value:

=Sum(Code.MyFunc(Fields!OrderID.Value, Fields!TotalOrderCost.Value))

4.)In the footer of the table, you add a textbox with the value expression:

=Code.MyFunc(Nothing, Fields!TotalOrderCost.Value)

to return the total value.

Hope this helps....

|||

Thanks for your help.

I'll give it a try.

Formatting Suggestions - Please Help

Hello,

I have a matrix with a dynamic number of columns (1-10). The trouble is that hiding one ore more columns still leaves space reserved for all 10 columns, which is ugly. This is because the size of the TextBox that oversees the columns is not dynamic, and it is set to the size of all 10 text boxes.

In other words, a matrix with 5 columns looks like this:

Item Total

Col1 Col2 Col3 Col4 Col5 Total

5 5 5 5 5 25

While a matrix with 2 columns (the last 3 have visibility set to false) looks like this:

Item Total

Col1 Col2 Total

5 5 25

I am going crazy trying to solve this one. Does anyone have any ideas at all that can help me? Merging all the columns into a single column would not work well for me, as each column is a drill-down for the others. And making each column small (.1in), doesn't work either because there is no "no-wrap" property.

ANY suggestions would be appreciated. How have others dealt with this issue?

Michael

I have also found that the invisible function is SQL reports is no good. What determines how many columns there will be on the report? Can you use that determination to choose between different reports -- say one that is a two column report and another that is a five column report? Don't use invisibility at all.|||

Greg,

That's what I did. I created a small, medium, and large version of my report (1-2, 3-5, 6-10), that seems to be reasonable even when the visibility column is hidden for some of the columns. The problem is that I have a dozen reports I want to use with the variable column in the left axis, and that means I need to create 36 reports instead of 12. Fortunately, the top axis auto-fits items so I only need one report for the top axis, or else I would be in BIG trouble.

sql

Formatting output from a SQL table

I have used a Winform textbox to enter the following data into a nvarchar field:

1. Choice number one

2. Choice number two

3 Choice number three

etc.

How do I print out the nvarchar field to look like the above? Can I do this in Reporting Services or something else? Thanks.

why can't you? have you tried?

doesn't sound difficult.

|||You could use the ReportViewer control in your application, create a report in local mode and use a datatable as the data source for the local report.sql

formatting numbers in Crystal Reports

:confused:

Hello,

I need to format my numbers as follows:
In my ttx file I have a field called Quantity with a datatype of Number and the value that i used is 1.00
I need to know how to how to get the value of 1 and not 1.

I also have another line
Quantity number 1.500

and when i simply put that on the report, it prints the number 2. What am i doing wrong? This is what I have as code so far.

if int({tester.quantity2}) = {tester.quantity2} then
formula = totext({tester.quantity2}, "#####.#####")
else
formula = totext({tester.quantity2})
end if

Thanks
NadishaHi,

Try to set the format at design time. Right Click the formula/database field. Goto Format option, there you can find info about formatting under "Number" tab.

Formatting numbers

I have a column in a dataset and i would like to change the number format of the column so that it has comma's separating the thousands place.

Hi,

Function Fixbigint(lng as long) as string
Dim nfi As System.Globalization.NumberFormatInfo = _
New System.Globalization.CultureInfo _
("en-US", False).NumberFormat
' lng=9223372036854775807
nfi.NumberGroupSeparator =","
return lng.ToString("N", nfi)
end function

s it any useful.

kiki

|||

Is that for Sql.

|||

NVM i figured it out again.

Formatting number in sql

How do you convert the following Access query to SQL Server ?
Format([tblA].[PRICE],"0.0000") AS Price
Thanks.SELECT Price = CONVERT(DECIMAL(10,4), tblA.Price)
..
Or, you could let the client application format for you.
"fniles" <fniles@.pfmail.com> wrote in message
news:%23hU7bAp%23FHA.2420@.TK2MSFTNGP12.phx.gbl...
> How do you convert the following Access query to SQL Server ?
> Format([tblA].[PRICE],"0.0000") AS Price
> Thanks.
>|||fniles wrote:

> How do you convert the following Access query to SQL Server ?
> Format([tblA].[PRICE],"0.0000") AS Price
> Thanks.
Access is an application development environment as well as a database.
SQL Server isn't. The client application is what controls how your
numeric values are formatted, not SQL Server. You need to consult the
documentation for whatever client environment you are running.
David Portas
SQL Server MVP
--

Formatting number

I have a phone number as 0396526888. if I were to format this to say +61 3
9652 6888 how would I go about working on this.
Any help would be helpful. thanks heaps
Regards
RidhimaOn Sep 3, 12:26 am, Ridhima Sood
<RidhimaS...@.discussions.microsoft.com> wrote:
> I have a phone number as 0396526888. if I were to format this to say +61 3
> 9652 6888 how would I go about working on this.
> Any help would be helpful. thanks heaps
> Regards
> Ridhima
Assuming that there was a field called PhoneNumber set to 0396526888,
you would use an expression similar to the following:
= "+61 " + Left(Fields!PhoneNumber.Value, 2) + " " + Fields!
PhoneNumber.Value.Substring(3, 4) + " " + Right(Fields!
PhoneNumber.Value, 4)
Of course, you could use the 'Mid' function in place of the
'Substring' function. Also, you might want to use CStr(CInt(...))
around the 'Left' part of the expression to get rid of the zero.
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||You could also simply use the following format code:
+61 ## #### ####
Assuming you have a static length (10) and you will alway append +61.
Let me know if you have questions...
"EMartinez" wrote:
> On Sep 3, 12:26 am, Ridhima Sood
> <RidhimaS...@.discussions.microsoft.com> wrote:
> > I have a phone number as 0396526888. if I were to format this to say +61 3
> > 9652 6888 how would I go about working on this.
> >
> > Any help would be helpful. thanks heaps
> >
> > Regards
> > Ridhima
>
> Assuming that there was a field called PhoneNumber set to 0396526888,
> you would use an expression similar to the following:
> = "+61 " + Left(Fields!PhoneNumber.Value, 2) + " " + Fields!
> PhoneNumber.Value.Substring(3, 4) + " " + Right(Fields!
> PhoneNumber.Value, 4)
> Of course, you could use the 'Mid' function in place of the
> 'Substring' function. Also, you might want to use CStr(CInt(...))
> around the 'Left' part of the expression to get rid of the zero.
> Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>sql

Formatting negative numbers without space between minus sign and number

How can I format these numbers without a space between the minus sign
and the number. I use the following "Format" for my numbers:
# ### ### ### (and have also tested to add ;-# ### ### ###) without
success!
-90000000.12345 formats to - 90 000 000 when I would like to have -90
000 000 Numbers like -1 should also be accpted... -1 instead of - 1...
Is there an easier whay to format this number? And how can I remove
the space...
Thank youI tried working with your example and several variations on the ### format
string. The space between the "-" and the value seems to built-in to that
format string. Please see the following for additional ideas:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconformattingoverview.asp
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Andreas Kardell" <akardell@.home.se> wrote in message
news:67424f9d.0410130707.1e79c943@.posting.google.com...
> How can I format these numbers without a space between the minus sign
> and the number. I use the following "Format" for my numbers:
> # ### ### ### (and have also tested to add ;-# ### ### ###) without
> success!
> -90000000.12345 formats to - 90 000 000 when I would like to have -90
> 000 000 Numbers like -1 should also be accpted... -1 instead of - 1...
> Is there an easier whay to format this number? And how can I remove
> the space...
> Thank you|||Hi Andreas
I found the following format worked in some instances ie for numbers 999 or
999999 etc.
=iif(Fields!ActualQty.Value < 0, "# ###"," # ###")
I too am having fun with conditional formating at the moment.
Fiona
"Bruce Johnson [MSFT]" wrote:
> I tried working with your example and several variations on the ### format
> string. The space between the "-" and the value seems to built-in to that
> format string. Please see the following for additional ideas:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconformattingoverview.asp
>
> --
> Bruce Johnson [MSFT]
> Microsoft SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Andreas Kardell" <akardell@.home.se> wrote in message
> news:67424f9d.0410130707.1e79c943@.posting.google.com...
> > How can I format these numbers without a space between the minus sign
> > and the number. I use the following "Format" for my numbers:
> > # ### ### ### (and have also tested to add ;-# ### ### ###) without
> > success!
> >
> > -90000000.12345 formats to - 90 000 000 when I would like to have -90
> > 000 000 Numbers like -1 should also be accpted... -1 instead of - 1...
> >
> > Is there an easier whay to format this number? And how can I remove
> > the space...
> >
> > Thank you
>
>|||Thank you Bruce!
I'm not too familiar with .NET Framework yet so I solved it by putting
the following code into the value field instead and leaving the format
field empty:
=IIF(Fields!fQuantity.Value < 0, "-", "") +
Ltrim(Format(Abs(Fields!fQuantity.Value), "# ### ### ###"))
I'm not too happy about this workaround but I use it for now.
Thanks a lot
"Bruce Johnson [MSFT]" <brucejoh@.online.microsoft.com> wrote in message news:<ulD7npUsEHA.3324@.TK2MSFTNGP15.phx.gbl>...
> I tried working with your example and several variations on the ### format
> string. The space between the "-" and the value seems to built-in to that
> format string. Please see the following for additional ideas:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconformattingoverview.asp
>
> --
> Bruce Johnson [MSFT]
> Microsoft SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Andreas Kardell" <akardell@.home.se> wrote in message
> news:67424f9d.0410130707.1e79c943@.posting.google.com...
> > How can I format these numbers without a space between the minus sign
> > and the number. I use the following "Format" for my numbers:
> > # ### ### ### (and have also tested to add ;-# ### ### ###) without
> > success!
> >
> > -90000000.12345 formats to - 90 000 000 when I would like to have -90
> > 000 000 Numbers like -1 should also be accpted... -1 instead of - 1...
> >
> > Is there an easier whay to format this number? And how can I remove
> > the space...
> >
> > Thank you|||Well, I don't have the same experience.
I am using SP1, but you are probably using that too.
Here's the format string I use in the format property of the TextBox.
#,#;-#,#;0
If you need to use it in code, the syntax would be:
String.Format("{0:#,#;-#,#;0}", Field.Value)
The result is just what you say you are wanting.
BTW, if it still does not work I suggest using CUSTOM CODE (look it up
in the help) to standardize/centralize the code you use to do the
formatting.
BR//Jerry|||#,#;-#,#;0

formatting decimal places in a query in MS SQL

Hey - I have a quick question and know that it is probably pretty simple, but I am stumped. I have a query where I need to make a colum a number that looks like a percent with 2 significant digits:

i.e.,
SELECT tblNumericCovert.number1, tblNumericCovert.number2, [number1]/[number2] AS testDiv
FROM tblNumericCovert

where testDiv needs to spit out results like this ###.##

I am totally lost, if anyone can help, I would appreciate it.cast(cast(YourValue as decimal(10,2)) as varchar(6)) + '%'

But really, data formatting issues should be handled by the interface, not sql server. Do your formatting in Crystal, VB, ASP, Excel, or whatever else you are using to display results.|||It will do the rounding though:

select right('000000'+ltrim(str(123.87745, 6, 2)), 6) + '%'|||Probably better to use ROUND for rounding, so you know what you are trying to accomplish when you look at your code...

SELECT ROUND(127.4355,2) = 127.4400|||Makes sense.|||Originally posted by rdjabarov
Makes sense.
That works perfectly! Thanks. I've always used Access which is a breeze and I'm trying to use SQL more. I really appreciate all of y'alls help on this.

Formatting Data Output

In a report that has a table that contains a number field, how do you
format the output to contain commas between thousands?On Mar 7, 10:15 am, robin9...@.hotmail.com wrote:
> In a report that has a table that contains a number field, how do you
> format the output to contain commas between thousands?
Select the cell(s) that need the thousands format (in Layout view) and
in the 'Format' field of the Properties window, enter: #,0
Regards,
Enrique Martinez
Sr. SQL Server Developer|||i already set the format to the fields, but even my windows regional
settings configuration is SPANISH MEXICO (wich means comma for thousands an
a period for decimals) im still seeing my reports with periods for thousands
and comma for decimals
what else i should check?
"EMartinez" <emartinez.pr1@.gmail.com> wrote in message
news:1173303498.820749.284820@.n33g2000cwc.googlegroups.com...
> On Mar 7, 10:15 am, robin9...@.hotmail.com wrote:
>> In a report that has a table that contains a number field, how do you
>> format the output to contain commas between thousands?
>
> Select the cell(s) that need the thousands format (in Layout view) and
> in the 'Format' field of the Properties window, enter: #,0
> Regards,
> Enrique Martinez
> Sr. SQL Server Developer
>|||Thanks, that worked.
On 7 Mar, 21:38, "EMartinez" <emartinez...@.gmail.com> wrote:
> On Mar 7, 10:15 am, robin9...@.hotmail.com wrote:
> > In a report that has a table that contains a number field, how do you
> > format the output to contain commas between thousands?
> Select the cell(s) that need the thousands format (in Layout view) and
> in the 'Format' field of the Properties window, enter: #,0
> Regards,
> Enrique Martinez
> Sr. SQL Server Developer|||On Mar 8, 3:56 am, robin9...@.hotmail.com wrote:
> Thanks, that worked.
> On 7 Mar, 21:38, "EMartinez" <emartinez...@.gmail.com> wrote:
> > On Mar 7, 10:15 am, robin9...@.hotmail.com wrote:
> > > In a report that has a table that contains a number field, how do you
> > > format the output to contain commas between thousands?
> > Select the cell(s) that need the thousands format (in Layout view) and
> > in the 'Format' field of the Properties window, enter: #,0
> > Regards,
> > Enrique Martinez
> > Sr. SQL Server Developer
You're welcome.
Regards,
Enrique Martinez
Sr. SQL Server Developer

Formatting Data

I have a table with two tables with colums that have a number value.
Example
Column One Column Two
3.5799999999999998E-2 3.5800000000000001
3.5799999999999998E-2 3.5800000000000001
4.0000000000000001E-2 4.0
3.5799999999999998E-2 3.5800000000000001
3.5799999999999998E-2 3.5800000000000001
I need to format the data so that the both tables and colums are the same.
Please help!!!!!!!!!!!!!!!!!!!1)What are the existing column data types?
2)If they are different , do an ALTER TABLE r=to make the same data type.
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:A8DAEF06-2F30-49D5-BBAA-87A37A0B5F52@.microsoft.com...
> I have a table with two tables with colums that have a number value.
> Example
>
> Column One Column Two
> 3.5799999999999998E-2 3.5800000000000001
> 3.5799999999999998E-2 3.5800000000000001
> 4.0000000000000001E-2 4.0
> 3.5799999999999998E-2 3.5800000000000001
> 3.5799999999999998E-2 3.5800000000000001
> I need to format the data so that the both tables and colums are the same.
> Please help!!!!!!!!!!!!!!!!!!!
>|||>> I need to format the data so that the both tables and colums are the same
. <<
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It looks like "number value" means a FLOAT and a
DECIMAL, so you are both vague and ambigous.
It also shows that the poster does not understand a tiered
artchitecture. Formattig is always done in the front end and never in
the database. Doesn't anyone learn basic programming concepts any
more?|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1141916365.071352.130990@.j33g2000cwa.googlegroups.com...
same. <<
> It looks like "number value" means a FLOAT and a
> DECIMAL, so you are both vague and ambigous.
CELKO, that statement is both repetative and redundant.
On a serious note, it is possible that the OP is trying to change the data
type to avoid problems with precision, although we can't be sure since they
are, in fact, both vague and ambiguous.|||Ok
I am totally new to SQL or any programming for that matter.
The Data Type is “Float” and Length is “8” this is the same for both
columns
My goal is to write a query that would give me the results where the values
are not equal
With the Data like this I am getting all rows when I run that query.
Thanks
Chris
"Jim Underwood" wrote:

> "--CELKO--" <jcelko212@.earthlink.net> wrote in message
> news:1141916365.071352.130990@.j33g2000cwa.googlegroups.com...
> same. <<
> CELKO, that statement is both repetative and redundant.
> On a serious note, it is possible that the OP is trying to change the data
> type to avoid problems with precision, although we can't be sure since the
y
> are, in fact, both vague and ambiguous.
>
>|||That's a start. Now we know that both columns are floats and you are trying
to compare them, but the values stored are physically different by very
small (insignificant) amounts.
The question is, what difference is significant? Do you want these values
to be rounded to two decimal places then compared, or do you need precision
of 8 places?
What do the values actually represent? What are you doing with them, and
how are they being populated in the first place?
Properly understanding the problem and the desired results is crucial to
developing the right solution.
The following code will convert the values to decimal data type with 2
decimal values:
select cast(column1 as decimal(6,3)) as Column1, cast(column2 as
decimal(6,3)) as Column2 from SomeTable
where cast(column1 as decimal(6,3)) = cast(column2 as decimal(6,3))
When dealing with database questions, providing DDL and sample data is often
helpful as well.
Check out the following link for more details.
http://www.aspfaq.com/etiquette.asp?id=5006
Also, I apologize if I insulted you. It was meant merely as a joke. As for
CELKO, don't worry about him, he beats up everyone who misunderstands SQL in
any way. Every time someone suggests that records and rows are the same
thing he drowns a kitten.
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:A154B16F-8E61-4714-85C0-7193EECE2FB8@.microsoft.com...
> Ok
> I am totally new to SQL or any programming for that matter.
>
> The Data Type is "Float" and Length is "8" this is the same for both
columns
> My goal is to write a query that would give me the results where the
values
> are not equal
> With the Data like this I am getting all rows when I run that query.
> Thanks
> Chris
>
> "Jim Underwood" wrote:
>
the
data
they|||This is how I wrote the query
select cast
([Value] as decimal(6,3)) as [Value], cast([P02_029_COTERM-RATE] as
decimal(6,3)) as [P02_029_COTERM-RATE] from dbo.Copy_P2_AFS
where cast([Value] as decimal(6,3)) <> cast([P02_029_COTERM-RATE] as
decimal(6,3))
and the result I am getting is as follows
Value P02_029_C0rterm-Rate
.036 3.580
.036 3.580
.040 4.000
.036 3.580
.036 3.580
.036 3.580
.040 4.000
.036 3.580
What I am doing wrong
Thanks
"Jim Underwood" wrote:

> That's a start. Now we know that both columns are floats and you are tryi
ng
> to compare them, but the values stored are physically different by very
> small (insignificant) amounts.
> The question is, what difference is significant? Do you want these values
> to be rounded to two decimal places then compared, or do you need precisio
n
> of 8 places?
> What do the values actually represent? What are you doing with them, and
> how are they being populated in the first place?
> Properly understanding the problem and the desired results is crucial to
> developing the right solution.
> The following code will convert the values to decimal data type with 2
> decimal values:
> select cast(column1 as decimal(6,3)) as Column1, cast(column2 as
> decimal(6,3)) as Column2 from SomeTable
> where cast(column1 as decimal(6,3)) = cast(column2 as decimal(6,3))
> When dealing with database questions, providing DDL and sample data is oft
en
> helpful as well.
> Check out the following link for more details.
> http://www.aspfaq.com/etiquette.asp?id=5006
> Also, I apologize if I insulted you. It was meant merely as a joke. As f
or
> CELKO, don't worry about him, he beats up everyone who misunderstands SQL
in
> any way. Every time someone suggests that records and rows are the same
> thing he drowns a kitten.
> "Chris" <Chris@.discussions.microsoft.com> wrote in message
> news:A154B16F-8E61-4714-85C0-7193EECE2FB8@.microsoft.com...
> columns
> values
> the
> data
> they
>
>|||Actually, I should have noticed this sooner...
If you look more closely at your original data...
Column One Column Two
3.5799999999999998E-2 3.5800000000000001
3.5799999999999998E-2 3.5800000000000001
4.0000000000000001E-2 4.0
3.5799999999999998E-2 3.5800000000000001
3.5799999999999998E-2 3.5800000000000001
Note the "E-2" at the end of the first column. This is scientific notation
indicating that the decimal point should be moved two places to the left.
The numbers in teh first row above are in fact 0.0358 and 3.580, so they
will not be made equal through rounding. in these examples, column2 =
Column1 * 100.
What is this data, where does it come from, and why are you comparing the
columns?
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:18C6145C-5D4C-4CBD-8739-3D4C77CDF214@.microsoft.com...
> This is how I wrote the query
> select cast
> ([Value] as decimal(6,3)) as [Value], cast([P02_029_COTERM-RATE] as
> decimal(6,3)) as [P02_029_COTERM-RATE] from dbo.Copy_P2_AFS
> where cast([Value] as decimal(6,3)) <> cast([P02_029_COTERM-RATE] as
> decimal(6,3))
>
> and the result I am getting is as follows
>
> Value P02_029_C0rterm-Rate
> .036 3.580
> .036 3.580
> .040 4.000
> .036 3.580
> .036 3.580
> .036 3.580
> .040 4.000
> .036 3.580
> What I am doing wrong
> Thanks
> "Jim Underwood" wrote:
>
trying
values
precision
and
often
for
SQL in
are
the
since|||>> I am totally new to SQL or any programming for that matter. <<
Okay, I will go easier on you, but SQL is a reallllly bad first
programming language. It is a declarative language that is used only
for data base access. Powerful but strange to someone with only High
School algebra and business procedures for his model of algorithms.
You can do a simple "WHERE col1 <> col2" and SQL will follow the IEEE
Floating rules. The next question is, since this is your first
programming language, do you know how floating point numbers work?
You will need to research this.|||Jim,
I am not sure how I should write the query to do what you are saying.
We have to ERP system and I need to make sure the tax rate in each system
are the same. These rate change each month. Can you correst the following
query to get the result I need.
select cast
([Value] as decimal(6,3)) as [Value], cast([P02_029_COTERM-RATE] as
decimal(6,3)) as [P02_029_COTERM-RATE] from dbo.Copy_P2_AFS
where cast([Value] as decimal(6,3)) <> cast([P02_029_COTERM-RATE] as
decimal(6,3))
Thanks
You are the best
Chris
"Jim Underwood" wrote:

> Actually, I should have noticed this sooner...
> If you look more closely at your original data...
> Column One Column Two
> 3.5799999999999998E-2 3.5800000000000001
> 3.5799999999999998E-2 3.5800000000000001
> 4.0000000000000001E-2 4.0
> 3.5799999999999998E-2 3.5800000000000001
> 3.5799999999999998E-2 3.5800000000000001
> Note the "E-2" at the end of the first column. This is scientific notatio
n
> indicating that the decimal point should be moved two places to the left.
> The numbers in teh first row above are in fact 0.0358 and 3.580, so they
> will not be made equal through rounding. in these examples, column2 =
> Column1 * 100.
> What is this data, where does it come from, and why are you comparing the
> columns?
> "Chris" <Chris@.discussions.microsoft.com> wrote in message
> news:18C6145C-5D4C-4CBD-8739-3D4C77CDF214@.microsoft.com...
> trying
> values
> precision
> and
> often
> for
> SQL in
> are
> the
> since
>
>

Formatting a textbox

I need to format a phone number parameter the user enters. Currently
when I print the parameter entered it displays like this ##########. I
want to format it to look like ###-###-####. Does anyone have any
ideas for making it look like that?E,
RS has a Custom Formatting built in where you can type in ###-###-####
for the Format.
But for phone Numbers the DB probably returns it as a string. You must
first convert it to a Number... a LONG in this case.
So go to report Properties go to Custom Code and put this in there:
Public Function convertToIntegerNow(tempVar2 As String)
convertToIntegerNow= CLng(tempVar2)
End Function
in the cell type in
=Code.convertToIntegerNow(Fields!Phone_Field_name.Value)
Then right click on the cell go to properties, change the format of the
cell to ###-###-####
Hope this helps
regards,
Stas K.

Formatting a Text box

I need to format a phone number parameter the user enters. Currently
when I print the parameter it displays like this ##########.
I want to format it to look like ###-###-####.
It can't be done with SQL, it must be done in the VB of that textbox.
Does anyone have any ideas' It would save me from pulling my last
hair out!!Down and Dirty, but:
=Mid(Fields!homephone.Value,1,3) & "-" & Mid(Fields!homephone.Value,4,3) &
"-" & Mid(Fields!homephone.Value,7,4)
You can substitute the Parameter value as Parameters!PHNumber.Value in place
of Fields!homephone.Value if they are entering the phone number in the
parameter box.
Rodney Landrum
"E" <ericfreiman@.mtgsi.com> wrote in message
news:1144097404.116802.178020@.v46g2000cwv.googlegroups.com...
>I need to format a phone number parameter the user enters. Currently
> when I print the parameter it displays like this ##########.
> I want to format it to look like ###-###-####.
> It can't be done with SQL, it must be done in the VB of that textbox.
> Does anyone have any ideas' It would save me from pulling my last
> hair out!!
>|||Hi,
Just select the cell you want to display with format right click and click
properties and select Format tab on the format field select "..." and click
custom and type "###-####"
Amarnath
"E" wrote:
> I need to format a phone number parameter the user enters. Currently
> when I print the parameter it displays like this ##########.
> I want to format it to look like ###-###-####.
> It can't be done with SQL, it must be done in the VB of that textbox.
> Does anyone have any ideas' It would save me from pulling my last
> hair out!!
>

Formatting a number field within a text box

I have a number of occassions to place a database field inside a bunch of
hardcoded text. But, it seems I can't do this if I want to format the number
(or field). Below is an example.
Your score this <DB Field1> is <DB Field2>%.
For DB Field1, the database stores a date/time format (6/1/2004 12:00:00AM).
I want to format it so it says June 2004. This is no problem, until I put
the field in an expression.
For DB Field2, the database stores the number as 69.15 (as an example). I
need 69.2. I can do this with no problem - as long as I don't try to put it
in an expression.
Is this normal behavior and is there any way around it?
Thanks
LeighEnter the following in the cell\textbox.
="Your score on " & Format(Fields!DBFiel1,"Y") & " was " &
Format(Fields!DBField2,"N1") & "%"
"Leigh" wrote:
> I have a number of occassions to place a database field inside a bunch of
> hardcoded text. But, it seems I can't do this if I want to format the number
> (or field). Below is an example.
> Your score this <DB Field1> is <DB Field2>%.
> For DB Field1, the database stores a date/time format (6/1/2004 12:00:00AM).
> I want to format it so it says June 2004. This is no problem, until I put
> the field in an expression.
> For DB Field2, the database stores the number as 69.15 (as an example). I
> need 69.2. I can do this with no problem - as long as I don't try to put it
> in an expression.
> Is this normal behavior and is there any way around it?
> Thanks
> Leigh

formatting

I have a textbox on a report containing a number I want formatted in
scientific notation. When I bring up the properties of the textbox, there
is a formatting option. I select number and am given a choice of
1.234123e+003. This works great, but I really don't want to show that many
number in the format. I would rather have the number something sensible,
say 1.23e+03. It looks like the custom format option might give the ability
to format the number this way, but I can't find any examples on how to use
it.
Any ideas?
Thanks.They're listed in
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconcustomnumericformatstrings.asp?frame=true.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Leon Chuck Gosslin" <powerb@.inel.gov> wrote in message
news:ekCx1oveEHA.2916@.TK2MSFTNGP12.phx.gbl...
> I have a textbox on a report containing a number I want formatted in
> scientific notation. When I bring up the properties of the textbox, there
> is a formatting option. I select number and am given a choice of
> 1.234123e+003. This works great, but I really don't want to show that
many
> number in the format. I would rather have the number something sensible,
> say 1.23e+03. It looks like the custom format option might give the
ability
> to format the number this way, but I can't find any examples on how to use
> it.
>
> Any ideas?
>
> Thanks.
>
>|||Thanks, man...I owe you a cold beer...
Leon.
"Ravi Mumulla (Microsoft)" <ravimu@.online.microsoft.com> wrote in message
news:%23YUg5wveEHA.3200@.TK2MSFTNGP09.phx.gbl...
> They're listed in
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconcustomnumericformatstrings.asp?frame=true.
> --
> Ravi Mumulla (Microsoft)
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Leon Chuck Gosslin" <powerb@.inel.gov> wrote in message
> news:ekCx1oveEHA.2916@.TK2MSFTNGP12.phx.gbl...
> > I have a textbox on a report containing a number I want formatted in
> > scientific notation. When I bring up the properties of the textbox,
there
> > is a formatting option. I select number and am given a choice of
> > 1.234123e+003. This works great, but I really don't want to show that
> many
> > number in the format. I would rather have the number something
sensible,
> > say 1.23e+03. It looks like the custom format option might give the
> ability
> > to format the number this way, but I can't find any examples on how to
use
> > it.
> >
> >
> >
> > Any ideas?
> >
> >
> >
> > Thanks.
> >
> >
> >
> >
>

formatt telephone number

I have searched this forum and the web and am not finding the answer.

I have a local report using the Microsoft Report Viewer add in for VS 2005 Express. I am trying to format a text field in a report that is a telephone number.

Instead of 8008898890 I want (800) 889-8890

Any suggestions? I have tried all of the format options such as {0:(###) ###-####}

None of them are working.

Joe

For anyone who is wondering here is the answer:

=Format(CDbl(Fields!yourfield.value), "(###) ###-####")

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?