Formula to view records

Hi,
I am having a report which takes a parameter to view records based on the parameter value passed to it.

I need to check the length of the parameter, if the length of the parameter value is "4", it should compare the parameter value with one field value, if the length is more than 4, then it should compare the value with another field and shoe me the records.

I have made the formula, but it doesn't show records, where as there are records in the table.

how could i do that ? Is anybody there, who could help me in that?

Thanks in advance.

Regards

DeepakIf len(parameter)=4 then
--do some work
else
--do some other work

Do you want to do this from Front end application?

Formatting subtotals in Matrix reports

I am trying to alter the color of a cell of a subtotal based on the value of the subtotal.
Example: =IIF(Subtotal.Value < 20 ,"Brown","Blue")
This doesn't work. How do I refer to the value of a subtotal inside an expression' I think it has something to do with InScope(). I have definitely clicked the little green tab and am getting the correct properties window.Please see this posting:
http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=c9b1dcda-cd35-4669-bb9b-4076ad21ed43
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Getting started and liking it" <Getting started and liking
it@.discussions.microsoft.com> wrote in message
news:5BC824BC-61A2-44A8-BA0B-FD0163954F8E@.microsoft.com...
> I am trying to alter the color of a cell of a subtotal based on the value
of the subtotal.
> Example: =IIF(Subtotal.Value < 20 ,"Brown","Blue")
> This doesn't work. How do I refer to the value of a subtotal inside an
expression' I think it has something to do with InScope(). I have
definitely clicked the little green tab and am getting the correct
properties window.

Formatting question

I want to show a parameter date minus 1 year, how can I do this?
="PERIOD MINUS 1 YEAR: " & Parameters!startdate.Value & " to " &
Parameters!stopdate.ValueTry:
="PERIOD MINUS 1 YEAR: " &
System.DateTime.Parse(Parameters!startdate.Value).AddYears(-1).ToShortDateString()
& " to " &
System.DateTime.Parse(Parameters!stopdate.Value).AddYears(-1).ToShortDateString()
or something simular
"Chris Patten" <ChrisPatten@.discussions.microsoft.com> wrote in message
news:33AB832F-0620-4A7C-B2FC-5D38A36C7429@.microsoft.com...
>I want to show a parameter date minus 1 year, how can I do this?
> ="PERIOD MINUS 1 YEAR: " & Parameters!startdate.Value & " to " &
> Parameters!stopdate.Value|||That is the shizzle my fizzle!!
(or however that should sound, thanks!)
Chris
"Oleg Yevteyev" wrote:
> Try:
> ="PERIOD MINUS 1 YEAR: " &
> System.DateTime.Parse(Parameters!startdate.Value).AddYears(-1).ToShortDateString()
> & " to " &
> System.DateTime.Parse(Parameters!stopdate.Value).AddYears(-1).ToShortDateString()
> or something simular
> "Chris Patten" <ChrisPatten@.discussions.microsoft.com> wrote in message
> news:33AB832F-0620-4A7C-B2FC-5D38A36C7429@.microsoft.com...
> >I want to show a parameter date minus 1 year, how can I do this?
> >
> > ="PERIOD MINUS 1 YEAR: " & Parameters!startdate.Value & " to " &
> > Parameters!stopdate.Value
>
>sql

Formatting Problem

I would like to change the background of a cell if its value is 'Test'
I have tried this thru both custom code and just thru a simple iif statement both which produce errors. I put the following iif statemnt in

=iif(Fields!change_type.value="Test","Cyan","Red")

and get the following error:

c:\variset4\reports_microsoft\ToolPredByOp.rdl The background color expression for the textbox 'CHANGE_TYPE' refers to the field 'change_type'. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope.

Any suggestions?disregard my last message - my line was correct - the problem was that Reporting services is Case sensitive. I should have had my field names in all caps. I found a good ei was trying to do at:

http://www.sqlservercentral.com/columnists/bknight/reportingservicesconditionalformatting.asp

if anyone is interested...|||

Hi.....

May be ur Prblm is Caps letter..or U Can also try Like this....
=iif(Fields!change_type.value="Test","Cyan","Red")
As
use ur Text Box Name instead of Field value.... ok write this Code in BackGround -- <Expression...>

=iif(ReportItems!textbox1.value="Test","Cyan","Red")

Best Regards......

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

More Begin and End dates, I have the following
=Parameters!BEG.Value &" through "& Parameters!END.Value
Its giving me date + time. How can I format this to remove the time?Try this one
=FormatDateTime(Parameters!BEG.Value,2) & " through " &
FormatDateTime(Parameters!BEG.Value,2)
--
Do you really need to print that email?
"BLAW" wrote:
> More Begin and End dates, I have the following
> =&" through "& Parameters!END.Value
> Its giving me date + time. How can I format this to remove the time?
>|||that did it, thanks!

Formatting numbers in SQL

Hi
I need to format the output of a numeric value from SQL-server with a
chosen thousand-separator and decimal-separator.
The Access equivalent would be something like:
Select format(myNumber, "#.###,##") as myFormattedNumber from myTable;
I have searched the net for a solution, and found something about
FORMAT_STRING, but that doesn't seem to be usable in SQL-Server...
Can anyone help me with this?
Regards,
Johnny Nordtvedt, NorwayIn an N-Tier environment it's better to leave presentational features
out of the database and do them in your client/middle tier. Some users
might prefer to configure a different numeric format so it's probably
not something you ought to fix in a SELECT statement.
TSQL does have a CONVERT function, which provides some limited
formatting capability but only if you convert your numerics to a
string. Also, AFAIK the CONVERT function only supports comma as a
thousands separator by default. In reality you should find it much
easier to do the formatting in the client control or form that displays
the data.
David Portas
SQL Server MVP
--|||My problem is that these values is displayed in a Word-document, and the
program that collects these values and inserts them into word, is not
available for formatting, and will not ever be available. So basically, I
am stuck with formatting this in SQL-server.
Regards,
Johnny Nordtvedt, Oslo, Norway|||Is it possible to make a user defined function to do this? And how would I
go by to do this?
Still hope that someone can help me with this. I am certain someone has
done this before?
Regards,
Johnny Nordtvedt, Oslo, Norway|||DECLARE @.numeric NUMERIC(10,2)
SET @.numeric = 123456.78
SELECT
REPLACE(REPLACE(REPLACE(
CONVERT(VARCHAR(10),CAST(@.numeric AS MONEY),1)
,',','~') ,'.',',') ,'~','.')
David Portas
SQL Server MVP
--|||--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
There are formatting functions on Merge fields in Word. Read the Word
Help file (in English version):
Field Types and Switches
Field Reference
Switches
How to: Highlight merge field, hit Alt-9. Add switches at end of
field:
/# will give numeric format.
/#.#,## I believe will format the number 99999.00 as 99.999,00
Also, read the Word Help articles on Merge Fields.
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBQlWjTIechKqOuFEgEQLidACfWKC4/ST6Pc1f6yJC3xaIoOO4dC8AoKLM
k5KfIvuctrzjBfvZduWw4W0t
=lpsE
--END PGP SIGNATURE--
Johnny, Norway wrote:
> My problem is that these values is displayed in a Word-document, and the
> program that collects these values and inserts them into word, is not
> available for formatting, and will not ever be available. So basically, I
> am stuck with formatting this in SQL-server.

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 in sql query

Hello All,

Is it possible to format an integer to a float value with 2 decimals in an sql query.

If yes, Please help?

SELECT

CONVERT(DECIMAL(4,2),9)

You can replace number 9 above with your integer or integer column.

|||

Thanks Limno,

You are a life saver.

Formatting for Phone?

In my report I am combing 2 fields as

=Fields!PROPERTY.Value & " - " & Fields!PHONE.Value

this results in PropertyName - 5555551212

How would I add in formatting for phone so my resulting display is (555) 555-1212?

Jim Seidel wrote:

In my report I am combing 2 fields as

=Fields!PROPERTY.Value & " - " & Fields!PHONE.Value

this results in PropertyName - 5555551212

How would I add in formatting for phone so my resulting display is (555) 555-1212?

I'm doing this from memory, but something like this:

= "(" & Left(Fields!PHONE.Value,3) & ") " & Right(Left(Fields!PHONE.Value,6),3) & "-" & Right(Fields!PHONE.Value,4)

The full expression would be this:

= Fields!PROPERTY.Value & " - " & "(" & Left(Fields!PHONE.Value,3) & ") " & Right(Left(Fields!PHONE.Value,6),3) & "-" & Right(Fields!PHONE.Value,4)|||Awesome Memory, it worked perfectly !|||Great, glad I could help. You'll get used to it. Does it make sense?

Getting the middle characters is a little awkward. I can't remember whether there is a Mid function defined or not, so I just use that.|||Yes, like you say, it just comes with practice

Formatting Dates

I have a Text box containing the following info:
="Effective: " & Parameters!PMStartDate.Value & " to "
& Parameters!PMEndDate.Value
How do I control the format of the date? I want the Format to be dd/MMM/yyyy.You will need to use Format(). See
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vafctFormat.asp
for more information.
Below is a sample expression based on your parameter names:
="Effective: " & Format(Parameters!PMStartDate.Value, "Long Date") & "
to " & Format(Parameters!PMEndDate.Value, "Short Date")
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"SAcanuck" <SAcanuck@.discussions.microsoft.com> wrote in message
news:9A6A54AE-1EE6-4A94-9A5C-244E77DD42A8@.microsoft.com...
> I have a Text box containing the following info:
> ="Effective: " & Parameters!PMStartDate.Value & " to "
> & Parameters!PMEndDate.Value
> How do I control the format of the date? I want the Format to be
dd/MMM/yyyy.|||Try this:
1. Pull up the report properties dialog, go to the code tab, and paste the
following into the code textbox:
public shared Function Convert(dt As datetime) As String
return dt.ToString("dd/MM/yyyy")
End Function
2. Paste the following into the textbox in question: ="Effective: " &
Code.Convert(Parameters!PMStartDate.Value) & " to " &
Code.Convert(Parameters!PMEndDate.Value)
The .NET datetime format strings are explained in detail in
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconcustomdatetimeformatstrings.asp?frame=true.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"SAcanuck" <SAcanuck@.discussions.microsoft.com> wrote in message
news:9A6A54AE-1EE6-4A94-9A5C-244E77DD42A8@.microsoft.com...
> I have a Text box containing the following info:
> ="Effective: " & Parameters!PMStartDate.Value & " to "
> & Parameters!PMEndDate.Value
> How do I control the format of the date? I want the Format to be
dd/MMM/yyyy.|||My favorite way of formatting strings is
=String.Format("From {0:d} to {1:d}", Parameters!
From.Value, Parameters!To.Value)
>--Original Message--
>I have a Text box containing the following info:
>="Effective: " & Parameters!PMStartDate.Value & " to "
>& Parameters!PMEndDate.Value
>How do I control the format of the date? I want the
Format to be dd/MMM/yyyy.
>.
>

Formatting Date in SRS

I am trying to format a date value as XX/XX/XX when I place a "d" in the format property it formats it as XX/XX/XXXX any ideas on how to change it to XX/XX/XX?

thanks in advance

use dd/MM/yy

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 field

I'm trying to format a text box color using an expression
= (Field!Name.Value < 0, "Red", "Black") but system send this error
The color expression for the textbox 'Field name' contains an error
[BC30451] name 'Iff' is not declared.
Please let me know what can I douse =Iif(Field!Name.Value < 0, "Red", "Black")
"Yoya" wrote:
> I'm trying to format a text box color using an expression
> = (Field!Name.Value < 0, "Red", "Black") but system send this error
> The color expression for the textbox 'Field name' contains an error
> [BC30451] name 'Iff' is not declared.
> Please let me know what can I do

formating in crystal reports

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

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

Can anybody help me in this regard.

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

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

Hello,

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

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

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

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

Is there a solution available - any ideas?

best regards

-flyall-

Hello flyall,

This should do what you want:

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

+ ":"

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

+ ":"

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

Hope this helps.

Jarret

Formating

I have a field which returns a decimal (3.0000) i only want the whole
value(3) not the parts (.0000) to be displayed in the report.
when i right click on the textbox and select propeties then under the
'format' section i click on 'number' and select '1234' hit 'ok' and preview
the report it still displays 3.000.
how can i display just the 3(whole value as opposed to the part)?
Tia,
VinnySet the format to "F0"
"Vinny Vinn" wrote:
> I have a field which returns a decimal (3.0000) i only want the whole
> value(3) not the parts (.0000) to be displayed in the report.
> when i right click on the textbox and select propeties then under the
> 'format' section i click on 'number' and select '1234' hit 'ok' and preview
> the report it still displays 3.000.
> how can i display just the 3(whole value as opposed to the part)?
> Tia,
> Vinny|||Thank You,it worked!!
"David Siebert" wrote:
> Set the format to "F0"
> "Vinny Vinn" wrote:
> > I have a field which returns a decimal (3.0000) i only want the whole
> > value(3) not the parts (.0000) to be displayed in the report.
> > when i right click on the textbox and select propeties then under the
> > 'format' section i click on 'number' and select '1234' hit 'ok' and preview
> > the report it still displays 3.000.
> > how can i display just the 3(whole value as opposed to the part)?
> >
> > Tia,
> > Vinny

FormatCurrency in 2k5 Report Services

I have a test report, unformatted the value one of my fields is -29.5600

When I apply the format: FormatCurrency(Fields!Paid_Amount.Value) the value shows as -($293056). Another sample is -62.2400 which converts to -($626224).

It seems the leading digits are being repeated for some reason. I've tried playing with rounding, the optional tristate paramaters, and regional settings to get the correct output, but have had no luck. Can anyone be of assistance?Figured it out. Seems I have to use: "$#,###.##"|||Another way for formatting numeric values is to use .NET formatcodes in the "Format" property of the textbox (Note: the textbox value expression has to evaluate to a numeric value otherwise the format code has no effect).

The format code for currency would be: C

MSDN has additional information about standard and custom format strings for numeric values:
* http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconstandardnumericformatstrings.asp
* http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconcustomnumericformatstrings.asp

--Robert

format when taking two fields away from other

hi there
can someone show me how to format the following please
=(Fields!SLAResolutionDate.Value)-(Fields!CreatedDate.Value)
these are both date/time fields - I want to format to just day - two
digits and just time - two digits
so like this =Format(Fields!SLAResolutionDate.Value)-(Fields!
CreatedDate.Value), dd, hh
but I am unsure where to stop and start the brackets to get the
result.
thanks
jewelNot sure if this is the simplest, but it will work.
Try this:
'This first part finds only the "whole days". The second part determines
the "left over hours".
Format(DATEDIFF(d,
Fields!SLAResolutionDate.Value,Fields!CreatedDate.Value),"00") & " " &
Format(DATEDIFF(d, Fields!SLAResolutionDate.Value,Fields!CreatedDate.Value) *
24 - DATEDIFF(hh,
Fields!SLAResolutionDate.Value,Fields!CreatedDate.Value),"00")
Michael
"jewelfire" wrote:
> hi there
> can someone show me how to format the following please
> =(Fields!SLAResolutionDate.Value)-(Fields!CreatedDate.Value)
> these are both date/time fields - I want to format to just day - two
> digits and just time - two digits
>
> so like this =Format(Fields!SLAResolutionDate.Value)-(Fields!
> CreatedDate.Value), dd, hh
> but I am unsure where to stop and start the brackets to get the
> result.
> thanks
> jewel
>|||Correction: the second part should be reversed (hours - days*24):
Format(DATEDIFF(d,
Fields!SLAResolutionDate.Value,Fields!CreatedDate.Value),"00") & " " &
Format(DATEDIFF(hh,
Fields!SLAResolutionDate.Value,Fields!CreatedDate.Value)-
DATEDIFF(d, Fields!SLAResolutionDate.Value,Fields!CreatedDate.Value) *
24,"00")
"Michael C" wrote:
> Not sure if this is the simplest, but it will work.
> Try this:
> 'This first part finds only the "whole days". The second part determines
> the "left over hours".
> Format(DATEDIFF(d,
> Fields!SLAResolutionDate.Value,Fields!CreatedDate.Value),"00") & " " &
> Format(DATEDIFF(d, Fields!SLAResolutionDate.Value,Fields!CreatedDate.Value) *
> 24 - DATEDIFF(hh,
> Fields!SLAResolutionDate.Value,Fields!CreatedDate.Value),"00")
> Michael
> "jewelfire" wrote:
> > hi there
> >
> > can someone show me how to format the following please
> >
> > =(Fields!SLAResolutionDate.Value)-(Fields!CreatedDate.Value)
> >
> > these are both date/time fields - I want to format to just day - two
> > digits and just time - two digits
> >
> >
> >
> > so like this =Format(Fields!SLAResolutionDate.Value)-(Fields!
> > CreatedDate.Value), dd, hh
> >
> > but I am unsure where to stop and start the brackets to get the
> > result.
> >
> > thanks
> > jewel
> >
> >|||thanks Michael
I get the error 'd' is not declared.
I am putting this in the expression on the layout view.
thanks
jewel
On May 9, 4:29 am, Michael C <Micha...@.discussions.microsoft.com>
wrote:
> Correction: the second part should be reversed (hours - days*24):
> Format(DATEDIFF(d,
> Fields!SLAResolutionDate.Value,Fields!CreatedDate.Value),"00") & " " &
> Format(DATEDIFF(hh,
> Fields!SLAResolutionDate.Value,Fields!CreatedDate.Value)-
> DATEDIFF(d, Fields!SLAResolutionDate.Value,Fields!CreatedDate.Value) *
> 24,"00")
>
> "Michael C" wrote:
> > Not sure if this is the simplest, but it will work.
> > Try this:
> > 'This first part finds only the "whole days". The second part determines
> > the "left over hours".
> > Format(DATEDIFF(d,
> > Fields!SLAResolutionDate.Value,Fields!CreatedDate.Value),"00") & " " &
> > Format(DATEDIFF(d, Fields!SLAResolutionDate.Value,Fields!CreatedDate.Value) *
> > 24 - DATEDIFF(hh,
> > Fields!SLAResolutionDate.Value,Fields!CreatedDate.Value),"00")
> > Michael
> > "jewelfire" wrote:
> > > hi there
> > > can someone show me how to format the following please
> > > =(Fields!SLAResolutionDate.Value)-(Fields!CreatedDate.Value)
> > > these are both date/time fields - I want to format to just day - two
> > > digits and just time - two digits
> > > so like this =Format(Fields!SLAResolutionDate.Value)-(Fields!
> > > CreatedDate.Value), dd, hh
> > > but I am unsure where to stop and start the brackets to get the
> > > result.
> > > thanks
> > > jewel- Hide quoted text -
> - Show quoted text -|||Try using dd instead of just d, and be sure there are no quotes around it.
Here is the MSDN file for valid entries!
http://msdn2.microsoft.com/en-us/library/ms189794.aspx
"jewelfire" wrote:
> thanks Michael
> I get the error 'd' is not declared.
> I am putting this in the expression on the layout view.
> thanks
> jewel
>
> On May 9, 4:29 am, Michael C <Micha...@.discussions.microsoft.com>
> wrote:
> > Correction: the second part should be reversed (hours - days*24):
> >
> > Format(DATEDIFF(d,
> > Fields!SLAResolutionDate.Value,Fields!CreatedDate.Value),"00") & " " &
> > Format(DATEDIFF(hh,
> > Fields!SLAResolutionDate.Value,Fields!CreatedDate.Value)-
> > DATEDIFF(d, Fields!SLAResolutionDate.Value,Fields!CreatedDate.Value) *
> > 24,"00")
> >
> >
> >
> > "Michael C" wrote:
> > > Not sure if this is the simplest, but it will work.
> >
> > > Try this:
> > > 'This first part finds only the "whole days". The second part determines
> > > the "left over hours".
> >
> > > Format(DATEDIFF(d,
> > > Fields!SLAResolutionDate.Value,Fields!CreatedDate.Value),"00") & " " &
> > > Format(DATEDIFF(d, Fields!SLAResolutionDate.Value,Fields!CreatedDate.Value) *
> > > 24 - DATEDIFF(hh,
> > > Fields!SLAResolutionDate.Value,Fields!CreatedDate.Value),"00")
> >
> > > Michael
> >
> > > "jewelfire" wrote:
> >
> > > > hi there
> >
> > > > can someone show me how to format the following please
> >
> > > > =(Fields!SLAResolutionDate.Value)-(Fields!CreatedDate.Value)
> >
> > > > these are both date/time fields - I want to format to just day - two
> > > > digits and just time - two digits
> >
> > > > so like this =Format(Fields!SLAResolutionDate.Value)-(Fields!
> > > > CreatedDate.Value), dd, hh
> >
> > > > but I am unsure where to stop and start the brackets to get the
> > > > result.
> >
> > > > thanks
> > > > jewel- Hide quoted text -
> >
> > - Show quoted text -
>
>|||hi Michael
tried as below
=Format(DATEDIFF(dd,Fields!SLAResolutionDate.Value,Fields!
CreatedDate.Value),"00") & " " &
Format(DATEDIFF(hh,Fields!SLAResolutionDate.Value,Fields!
CreatedDate.Value)-
DATEDIFF(dd, Fields!SLAResolutionDate.Value,Fields!CreatedDate.Value)
*
24,"00")
dd - this is underlined and in red but the 2nd instance is not.
showing red on
"00") & " " &
"00")
probably something very simple! thanks for your help
thanks
jewel
On May 10, 4:24 am, Michael C <Micha...@.discussions.microsoft.com>
wrote:
> Try using dd instead of just d, and be sure there are no quotes around it.
> Here is the MSDN file for valid entries!
> http://msdn2.microsoft.com/en-us/library/ms189794.aspx
>
> "jewelfire" wrote:
> > thanks Michael
> > I get the error 'd' is not declared.
> > I am putting this in the expression on the layout view.
> > thanks
> > jewel
> > On May 9, 4:29 am, Michael C <Micha...@.discussions.microsoft.com>
> > wrote:
> > > Correction: the second part should be reversed (hours - days*24):
> > > Format(DATEDIFF(d,
> > > Fields!SLAResolutionDate.Value,Fields!CreatedDate.Value),"00") & " " &
> > > Format(DATEDIFF(hh,
> > > Fields!SLAResolutionDate.Value,Fields!CreatedDate.Value)-
> > > DATEDIFF(d, Fields!SLAResolutionDate.Value,Fields!CreatedDate.Value) *
> > > 24,"00")
> > > "Michael C" wrote:
> > > > Not sure if this is the simplest, but it will work.
> > > > Try this:
> > > > 'This first part finds only the "whole days". The second part determines
> > > > the "left over hours".
> > > > Format(DATEDIFF(d,
> > > > Fields!SLAResolutionDate.Value,Fields!CreatedDate.Value),"00") & " " &
> > > > Format(DATEDIFF(d, Fields!SLAResolutionDate.Value,Fields!CreatedDate.Value) *
> > > > 24 - DATEDIFF(hh,
> > > > Fields!SLAResolutionDate.Value,Fields!CreatedDate.Value),"00")
> > > > Michael
> > > > "jewelfire" wrote:
> > > > > hi there
> > > > > can someone show me how to format the following please
> > > > > =(Fields!SLAResolutionDate.Value)-(Fields!CreatedDate.Value)
> > > > > these are both date/time fields - I want to format to just day - two
> > > > > digits and just time - two digits
> > > > > so like this =Format(Fields!SLAResolutionDate.Value)-(Fields!
> > > > > CreatedDate.Value), dd, hh
> > > > > but I am unsure where to stop and start the brackets to get the
> > > > > result.
> > > > > thanks
> > > > > jewel- Hide quoted text -
> > > - Show quoted text -- Hide quoted text -
> - Show quoted text -

format string for dutch language

Hi everyone,

I have some measure with the following format string : #,#0.00

Now in Reporting Services my value is 1,975,647,808.55 instead of 1.975.647.808,55

I know that I can't change the format string to #.#0,00

This gives bad results in reporting Services

Any idea or solution?

thanks!!

Filip

Hello. I do not have a complete solution. We are struggling with this problem in Sweden as well.

You can use this format string '### ### ### ###.##' in SSAS2005.

It will help with avoiding the problem that all numbers will be text when you export to Excel.

The downside is that you will have to accept a dot (.) as a decimal pointer.

I have found no other way to solve this,

Kind Regards

Thomas Ivarsson