Formula Question

I have a field "Accept" that can be Null or contain either "Y" or "N". In my
test data I have a total of 4 rows, 2 with "Y", 1 with "N" and 1 Null. I
want a count of each case in the footer along with some descriptive text. I
tried the formula
=Count(Fields!Accept.Value="Y")
But it returns "4"? The same thing happens if I use
=Count(Fields!Accept.Value="N")
What is wrong in this formula?
Also, I really want the report to display something like: "Number of
Accepts: 2" but if I try to code the field as:
"Number of Accepts: " & =Count(Fields!Accept.Value="Y")
the formula displays as text?
TIA
Waynetry to write:
= "Number of Accepts: " & Sum(IIf(Fields!Accept.Value="Y",1,0))
Good Luck
"Wayne Wengert" <wayneSKIPSPAM@.wengert.org> wrote in message
news:uiB9sAU3FHA.3400@.tk2msftngp13.phx.gbl...
> I have a field "Accept" that can be Null or contain either "Y" or "N". In
my
> test data I have a total of 4 rows, 2 with "Y", 1 with "N" and 1 Null. I
> want a count of each case in the footer along with some descriptive text.
I
> tried the formula
> =Count(Fields!Accept.Value="Y")
> But it returns "4"? The same thing happens if I use
> =Count(Fields!Accept.Value="N")
> What is wrong in this formula?
> Also, I really want the report to display something like: "Number of
> Accepts: 2" but if I try to code the field as:
> "Number of Accepts: " & =Count(Fields!Accept.Value="Y")
> the formula displays as text?
>
> TIA
> Wayne
>
>|||Liz;
Thank you very much. I never would have figured that one out. Seems like
making a point the hard way. I've ordered a couple of books that I hope will
explain the logic of creating expressions.
Wayne
"Liz Matyas" <lizmts@.mail.com> wrote in message
news:uK3U8FZ3FHA.1276@.TK2MSFTNGP09.phx.gbl...
> try to write:
> = "Number of Accepts: " & Sum(IIf(Fields!Accept.Value="Y",1,0))
> Good Luck
>
> "Wayne Wengert" <wayneSKIPSPAM@.wengert.org> wrote in message
> news:uiB9sAU3FHA.3400@.tk2msftngp13.phx.gbl...
>> I have a field "Accept" that can be Null or contain either "Y" or "N". In
> my
>> test data I have a total of 4 rows, 2 with "Y", 1 with "N" and 1 Null. I
>> want a count of each case in the footer along with some descriptive text.
> I
>> tried the formula
>> =Count(Fields!Accept.Value="Y")
>> But it returns "4"? The same thing happens if I use
>> =Count(Fields!Accept.Value="N")
>> What is wrong in this formula?
>> Also, I really want the report to display something like: "Number of
>> Accepts: 2" but if I try to code the field as:
>> "Number of Accepts: " & =Count(Fields!Accept.Value="Y")
>> the formula displays as text?
>>
>> TIA
>> Wayne
>>
>>
>|||The Count RDL aggregate function works similar as the Count aggregate in
SQL: it will count all rows that are NOT NULL. The expresson
Fields!Accept.Value="Y" will return a boolean value (true/false) for all
rows, that's why the count results in the value 4.
Besides using =Sum(iif(...)) as shown by Liz, you could also use
=Count(iif(Fields!Accept.Value="Y", 1, Nothing))
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Wayne Wengert" <wayneSKIPSPAM@.wengert.org> wrote in message
news:u8BwZda3FHA.128@.tk2msftngp13.phx.gbl...
> Liz;
> Thank you very much. I never would have figured that one out. Seems like
> making a point the hard way. I've ordered a couple of books that I hope
> will explain the logic of creating expressions.
> Wayne
> "Liz Matyas" <lizmts@.mail.com> wrote in message
> news:uK3U8FZ3FHA.1276@.TK2MSFTNGP09.phx.gbl...
>> try to write:
>> = "Number of Accepts: " & Sum(IIf(Fields!Accept.Value="Y",1,0))
>> Good Luck
>>
>> "Wayne Wengert" <wayneSKIPSPAM@.wengert.org> wrote in message
>> news:uiB9sAU3FHA.3400@.tk2msftngp13.phx.gbl...
>> I have a field "Accept" that can be Null or contain either "Y" or "N".
>> In
>> my
>> test data I have a total of 4 rows, 2 with "Y", 1 with "N" and 1 Null. I
>> want a count of each case in the footer along with some descriptive
>> text.
>> I
>> tried the formula
>> =Count(Fields!Accept.Value="Y")
>> But it returns "4"? The same thing happens if I use
>> =Count(Fields!Accept.Value="N")
>> What is wrong in this formula?
>> Also, I really want the report to display something like: "Number of
>> Accepts: 2" but if I try to code the field as:
>> "Number of Accepts: " & =Count(Fields!Accept.Value="Y")
>> the formula displays as text?
>>
>> TIA
>> Wayne
>>
>>
>>
>|||Robert;
Thanks for that additional information. It helps me understand the process.
Wayne
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:eZK0yma3FHA.3036@.TK2MSFTNGP10.phx.gbl...
> The Count RDL aggregate function works similar as the Count aggregate in
> SQL: it will count all rows that are NOT NULL. The expresson
> Fields!Accept.Value="Y" will return a boolean value (true/false) for all
> rows, that's why the count results in the value 4.
> Besides using =Sum(iif(...)) as shown by Liz, you could also use
> =Count(iif(Fields!Accept.Value="Y", 1, Nothing))
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> "Wayne Wengert" <wayneSKIPSPAM@.wengert.org> wrote in message
> news:u8BwZda3FHA.128@.tk2msftngp13.phx.gbl...
>> Liz;
>> Thank you very much. I never would have figured that one out. Seems like
>> making a point the hard way. I've ordered a couple of books that I hope
>> will explain the logic of creating expressions.
>> Wayne
>> "Liz Matyas" <lizmts@.mail.com> wrote in message
>> news:uK3U8FZ3FHA.1276@.TK2MSFTNGP09.phx.gbl...
>> try to write:
>> = "Number of Accepts: " & Sum(IIf(Fields!Accept.Value="Y",1,0))
>> Good Luck
>>
>> "Wayne Wengert" <wayneSKIPSPAM@.wengert.org> wrote in message
>> news:uiB9sAU3FHA.3400@.tk2msftngp13.phx.gbl...
>> I have a field "Accept" that can be Null or contain either "Y" or "N".
>> In
>> my
>> test data I have a total of 4 rows, 2 with "Y", 1 with "N" and 1 Null.
>> I
>> want a count of each case in the footer along with some descriptive
>> text.
>> I
>> tried the formula
>> =Count(Fields!Accept.Value="Y")
>> But it returns "4"? The same thing happens if I use
>> =Count(Fields!Accept.Value="N")
>> What is wrong in this formula?
>> Also, I really want the report to display something like: "Number of
>> Accepts: 2" but if I try to code the field as:
>> "Number of Accepts: " & =Count(Fields!Accept.Value="Y")
>> the formula displays as text?
>>
>> TIA
>> Wayne
>>
>>
>>
>>
>sql

Formula Problem

HI All,

I have made a formula in the formula field.
How can I make use of that formula to the report?
And other question,
is the syntax of the sql expression same as the syntax of sql
and what will it return and how to apply on the report?

Thanks for answer my stupid questionhi, :wave:
To use the formula in the report..drag it and place it whereever u want to display it...if i am right?

Yes the syntax of the sql expresstion is same as sql..but in where condition if u want to use any fields in the report..dont type it urself..just double click the field from the report fields in the formula editor..

Regards,
Rohini :thumb:|||Thanks for your reply

But I still don't know how to use the sql expression field
Can you show me a step by step example for me?

Thanks~~~~

Formula problem

env: sql server 2000

objective:
add a formula for an INT column to the FORMULA field or the DEFAULT
VALUE field in DESIGN VIEW.

all of the following attempts failed
IIF (columnName = 0, "1", columnName + 1)
IIF (columnName = 0, 1, columnName + 1)
IIF (0, "1", columnName + 1)

caveat: cannot use IDENTITY
REASON: I'd like have sets of repeatable values for this row, e.g.
set a
1 -- row 1
2 -- row 2
3
4
5
set b
1 -- row 6
2
3
set c
1 -- row 9
2
3
4

Underlying rationale is to support OO design I understand probably it's
going to be huge headache for lots of people down the road but ...

TIA.NickName (dadada@.rock.com) writes:
> env: sql server 2000
> objective:
> add a formula for an INT column to the FORMULA field or the DEFAULT
> VALUE field in DESIGN VIEW.
> all of the following attempts failed
> IIF (columnName = 0, "1", columnName + 1)
> IIF (columnName = 0, 1, columnName + 1)
> IIF (0, "1", columnName + 1)

I will have to admit that I don't understand much of your post.
IIF is Access/VB, but I guess you know that.

In case not, the syntax in SQL server is

CASE WHEN columnName = 0 THEN 1 ELSE columnName + 1 END

Then again, this can be simiplied to:

columnName + 1

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Oops, haven't touched db for a couple of months, now it seems that I
totally forgot everything or mix everything. Ok, joke aside, case stmt
does not seem to work, strange.|||Ahe, because of the default NULL value.

Formula in flaot field

I have a float field and need the data to present itself as a datetime. I have been unsuccessful at converting the entire field to datetime so I thought I would try a formula but have had no success. Can this be done?

I have data like so:

100599
10699
120597
etc...

and need to hit them as though they were datetime for order by and such...

or an 'in query' conversion...

ThanksI came up with a solution, thanks anyway.sql

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 help

Report Field (LastApptDate)
I want to take the CurrentDate - LastApptDate and if its Less than 1500 days, to produce an "X" or else nothing
The second part is CurrentDate - LastApptDate - if it greater than 1500 days to produce an "X" else nothingI just started a new job and don't have access to anything yet, but if I had access to some data to test it, I think it would go something like this:

if DateDiff("d",LastApptDate,CurrentDate) >= 1500 then

"x"

else

""

or maybe like this:

if LastApptDate >= DateAdd("d", -1500, CurrentDate) then

"X"

else

""

Formula for running total

Hi,
Probably very simple but I've just started learning this but how would i do a running total in CR11 I have done the basic field but what I need is something like...
If Qty_Field = Price_field then * to give me final price
Can any help me?
ThanksI think you can use this in the Use formula option

Formula field vs. no formula field

Dear all,
Sounds like a simple question.
I have three fields in my table
FieldA
FieldB
FieldC
FieldD
FieldC = FieldA * FieldB
FieldD = 0.01 * FieldA * FieldB
(actually the scenario is slightly complex that this)
I have two options
1. Declare FieldC and FieldD as formula fields and relax myself by
bothering only about FieldA and FieldB from my asp.net application
2. Worry about updating FieldC and FieldD as "there could be a serious
performance difference"
Updates happen from different screens, and it would make a deal of
difference to me if i can allow sql to take care of the several formula
fields i want to keep. At the same time, i don't want my customers to
come crying saying the system is very slow.
Any advice?
Thanks
Hi
If your program unnecessarily updates column A and B then you will probably
be better off with a view, although you would really want to fix the
programming! It is not so much where your updates occur, but the number and
performance of inserts/updates/selects which decide which way is better!
It also sounds like you are accessing the tables directly, therefore I would
also recommend using stored procedures.
John
"mich_stone@.yahoo.com" wrote:

> Dear all,
> Sounds like a simple question.
> I have three fields in my table
> FieldA
> FieldB
> FieldC
> FieldD
> FieldC = FieldA * FieldB
> FieldD = 0.01 * FieldA * FieldB
> (actually the scenario is slightly complex that this)
> I have two options
> 1. Declare FieldC and FieldD as formula fields and relax myself by
> bothering only about FieldA and FieldB from my asp.net application
> 2. Worry about updating FieldC and FieldD as "there could be a serious
> performance difference"
> Updates happen from different screens, and it would make a deal of
> difference to me if i can allow sql to take care of the several formula
> fields i want to keep. At the same time, i don't want my customers to
> come crying saying the system is very slow.
> Any advice?
> Thanks
>
|||Defining a formula (computed column) such as:
CREATE TABLE t(c1 int, c2 AS c1 * 100)
doesn't store the calculated value. I.e. no penalty for modifications. However, for below search
argument, SQL Server can (probably) not use an index on c1:
WHERE c2 = 10000
So, you can create an index on a computed column where the value is actually stored. And in this
case, you do pay for modifications.
An alternative to computed columns is to create a view with the calculated values and have your
users SELECT from the view.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
<mich_stone@.yahoo.com> wrote in message news:1107240862.212907.54340@.c13g2000cwb.googlegro ups.com...
> Dear all,
> Sounds like a simple question.
> I have three fields in my table
> FieldA
> FieldB
> FieldC
> FieldD
> FieldC = FieldA * FieldB
> FieldD = 0.01 * FieldA * FieldB
> (actually the scenario is slightly complex that this)
> I have two options
> 1. Declare FieldC and FieldD as formula fields and relax myself by
> bothering only about FieldA and FieldB from my asp.net application
> 2. Worry about updating FieldC and FieldD as "there could be a serious
> performance difference"
> Updates happen from different screens, and it would make a deal of
> difference to me if i can allow sql to take care of the several formula
> fields i want to keep. At the same time, i don't want my customers to
> come crying saying the system is very slow.
> Any advice?
> Thanks
>

Formula field vs. no formula field

Dear all,
Sounds like a simple question.
I have three fields in my table
FieldA
FieldB
FieldC
FieldD
FieldC = FieldA * FieldB
FieldD = 0.01 * FieldA * FieldB
(actually the scenario is slightly complex that this)
I have two options
1. Declare FieldC and FieldD as formula fields and relax myself by
bothering only about FieldA and FieldB from my asp.net application
2. Worry about updating FieldC and FieldD as "there could be a serious
performance difference"
Updates happen from different screens, and it would make a deal of
difference to me if i can allow sql to take care of the several formula
fields i want to keep. At the same time, i don't want my customers to
come crying saying the system is very slow.
Any advice?
ThanksHi
If your program unnecessarily updates column A and B then you will probably
be better off with a view, although you would really want to fix the
programming! It is not so much where your updates occur, but the number and
performance of inserts/updates/selects which decide which way is better!
It also sounds like you are accessing the tables directly, therefore I would
also recommend using stored procedures.
John
"mich_stone@.yahoo.com" wrote:

> Dear all,
> Sounds like a simple question.
> I have three fields in my table
> FieldA
> FieldB
> FieldC
> FieldD
> FieldC = FieldA * FieldB
> FieldD = 0.01 * FieldA * FieldB
> (actually the scenario is slightly complex that this)
> I have two options
> 1. Declare FieldC and FieldD as formula fields and relax myself by
> bothering only about FieldA and FieldB from my asp.net application
> 2. Worry about updating FieldC and FieldD as "there could be a serious
> performance difference"
> Updates happen from different screens, and it would make a deal of
> difference to me if i can allow sql to take care of the several formula
> fields i want to keep. At the same time, i don't want my customers to
> come crying saying the system is very slow.
> Any advice?
> Thanks
>|||Defining a formula (computed column) such as:
CREATE TABLE t(c1 int, c2 AS c1 * 100)
doesn't store the calculated value. I.e. no penalty for modifications. Howev
er, for below search
argument, SQL Server can (probably) not use an index on c1:
WHERE c2 = 10000
So, you can create an index on a computed column where the value is actually
stored. And in this
case, you do pay for modifications.
An alternative to computed columns is to create a view with the calculated v
alues and have your
users SELECT from the view.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
<mich_stone@.yahoo.com> wrote in message news:1107240862.212907.54340@.c13g2000cwb.googlegroup
s.com...
> Dear all,
> Sounds like a simple question.
> I have three fields in my table
> FieldA
> FieldB
> FieldC
> FieldD
> FieldC = FieldA * FieldB
> FieldD = 0.01 * FieldA * FieldB
> (actually the scenario is slightly complex that this)
> I have two options
> 1. Declare FieldC and FieldD as formula fields and relax myself by
> bothering only about FieldA and FieldB from my asp.net application
> 2. Worry about updating FieldC and FieldD as "there could be a serious
> performance difference"
> Updates happen from different screens, and it would make a deal of
> difference to me if i can allow sql to take care of the several formula
> fields i want to keep. At the same time, i don't want my customers to
> come crying saying the system is very slow.
> Any advice?
> Thanks
>sql

Formula field vs. no formula field

Dear all,
Sounds like a simple question.
I have three fields in my table
FieldA
FieldB
FieldC
FieldD
FieldC = FieldA * FieldB
FieldD = 0.01 * FieldA * FieldB
(actually the scenario is slightly complex that this)
I have two options
1. Declare FieldC and FieldD as formula fields and relax myself by
bothering only about FieldA and FieldB from my asp.net application
2. Worry about updating FieldC and FieldD as "there could be a serious
performance difference"
Updates happen from different screens, and it would make a deal of
difference to me if i can allow sql to take care of the several formula
fields i want to keep. At the same time, i don't want my customers to
come crying saying the system is very slow.
Any advice?
ThanksHi
If your program unnecessarily updates column A and B then you will probably
be better off with a view, although you would really want to fix the
programming! It is not so much where your updates occur, but the number and
performance of inserts/updates/selects which decide which way is better!
It also sounds like you are accessing the tables directly, therefore I would
also recommend using stored procedures.
John
"mich_stone@.yahoo.com" wrote:
> Dear all,
> Sounds like a simple question.
> I have three fields in my table
> FieldA
> FieldB
> FieldC
> FieldD
> FieldC = FieldA * FieldB
> FieldD = 0.01 * FieldA * FieldB
> (actually the scenario is slightly complex that this)
> I have two options
> 1. Declare FieldC and FieldD as formula fields and relax myself by
> bothering only about FieldA and FieldB from my asp.net application
> 2. Worry about updating FieldC and FieldD as "there could be a serious
> performance difference"
> Updates happen from different screens, and it would make a deal of
> difference to me if i can allow sql to take care of the several formula
> fields i want to keep. At the same time, i don't want my customers to
> come crying saying the system is very slow.
> Any advice?
> Thanks
>|||Defining a formula (computed column) such as:
CREATE TABLE t(c1 int, c2 AS c1 * 100)
doesn't store the calculated value. I.e. no penalty for modifications. However, for below search
argument, SQL Server can (probably) not use an index on c1:
WHERE c2 = 10000
So, you can create an index on a computed column where the value is actually stored. And in this
case, you do pay for modifications.
An alternative to computed columns is to create a view with the calculated values and have your
users SELECT from the view.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
<mich_stone@.yahoo.com> wrote in message news:1107240862.212907.54340@.c13g2000cwb.googlegroups.com...
> Dear all,
> Sounds like a simple question.
> I have three fields in my table
> FieldA
> FieldB
> FieldC
> FieldD
> FieldC = FieldA * FieldB
> FieldD = 0.01 * FieldA * FieldB
> (actually the scenario is slightly complex that this)
> I have two options
> 1. Declare FieldC and FieldD as formula fields and relax myself by
> bothering only about FieldA and FieldB from my asp.net application
> 2. Worry about updating FieldC and FieldD as "there could be a serious
> performance difference"
> Updates happen from different screens, and it would make a deal of
> difference to me if i can allow sql to take care of the several formula
> fields i want to keep. At the same time, i don't want my customers to
> come crying saying the system is very slow.
> Any advice?
> Thanks
>

Formula Field in SQL Server 2000

Does anyone know how to use Formula Field in SQL Server 2000. I have googled a lot, but haven't been able to find out how to use it..

Thxyou can add/subtract or write a formula to compute from values in other columns..
lets say you have colA, colB and colC. In colC, you can set the formula as colA+ColB.

you can look for "computed columns" in BOL.

hth|||Whats BOL - Book Online??

Formula Field

I'm using the crystal report of VB.Net 2005 I've tried to write
ReportInstance.DataDefinition.FormulaFields(index).Text = "{DataTable.ColumnName}"
but it didn't work ,I've found that FormulaFields is read only,is there a way to do my point,I mean I need to assign column names of a datatble to the FormulaField(index).Try this, the following code has always worked for me:

For Each crxFormulaField In crxReport.FormulaFields
Select Case crxFormulaField.Name
Case "{@.YourFormulaName}"
crxFormulaField.Text = "{DataTable.ColumnName}"
End Select
Next crxFormulaField

Good luck!
-- Heather

Formula field

Hi,

My table looks like..
accountno datecreated balance ecode
100 09/04/07 50 a
101 09/04/07 60 b
102 09/04/07 70 c
103 09/04/07 90 d

I am using the formula..
IF {TEST_TEMP.ECODE}="b" THEN
{TEST_TEMP.ACCOUNT}
ELSE
0

I have placed the formula field in the "Details" section. But I am seeing 4 rows in the output out of which 1 row showing 101(accountno) and the remaining 0.
what should I do in order to display just the accountno 101?.
I have also tried placing the formula field in "PageHeader" section. But no use.

Thanks.can u try removing the else part?
IF {TEST_TEMP.ECODE}="b" THEN
{TEST_TEMP.ACCOUNT}|||But I am seeing 4 rows in the output out of which 1 row showing 101(accountno) and the remaining 0

You got what u asked for!
use IF {TEST_TEMP.ECODE}="b" THEN
{TEST_TEMP.ACCOUNT}
ELSE
"";|||I tried both..that is removing the ELSE part and putting "". but no use..|||Put the formula in : Menu / Report / Edit Selection Formula / Record
with the next syntax :
{TEST_TEMP.ECODE}="b"|||To show only the rows which are 'b" just go to the section expert, select details, and conditionally suppress it using a formula like:

If {table.field) <> "b" then true

formatting with expressions trouble

I am trying to make a whole row "bolder" if a field in that row return true.
iif(field = true,"bolder","normal")
Isnt that the way it is written inside the format weight in the properties.Should be =IIF(Fields!YourField.Value = True, "Bold", "Normal")
"Benw" <Benw@.discussions.microsoft.com> wrote in message
news:476371E6-E970-4144-8D40-10873AC9530B@.microsoft.com...
>I am trying to make a whole row "bolder" if a field in that row return
>true.
> iif(field = true,"bolder","normal")
> Isnt that the way it is written inside the format weight in the
> properties.

Formatting the date on report

Reporting Services 2005 (SQL Server 2005 CTP V 9.00.1187.00)

I have a date field and I want to format it on a report like:

January 9, 2006

Seems to me that should be pretty simple. But I can't find the correct format code. If I look at the textbox properties and go to the format tab, there is an option for Date, but there are only 3 choices:

1/9/2006 9:47 AM

Monday, January 9, 2006

1/9/2006

If I choose custom I found that Y gives me:

January 2006

and M gives me:

January 09

This is rather frustrating. I'm sure I could modify my source SQL to get each part of the date and past it together, but I would think a good reporting tool would give me the option on how to format the date, so I don't have to always modify the SQL. I've searched for help on the different format options and I can't find any.

Any help would be very much appreciated.

Thanks.

Well, I finally figured it out, with the help of some obscure posting on another forum. I now use format:

MMMM dd"," yyyy

interesting that you have to use MMMM, not mmmm and you have to use yyyy, not YYYY.

Now I could not find this anywhere in the documentation. I'm sure it must be there, but where? Does anybody know? I'm sure I'm going to have other formatting issues and I really don't want to have to search through forum postings on the internet to find crumbs of clues to figure out things that I think should be easily found in the products documentation. I'm sure it's me. It's gotta be right in front of me, or I did something wrong when installing and I don't have the correct documentation. (but then why can't I find it at MSDN?)

|||

You can find documentation about these custom .NET format strings (e.g. MMMM and yyyy) on MSDN: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconcustomdatetimeformatstrings.asp

-- Robert

|||Thanks!

Formatting text

I need to format a text in a text box to show a text field truncated.
In other terms, I must show only 40 characters of a very long string.
Do I use the statement "format"?
How can I solve this issue?
Many thanksI would do it in the SQL statement. As in left(fieldname, 40)
Richard
"Pasquale" <Pasquale@.discussions.microsoft.com> wrote in message
news:2550C968-0324-4FC8-A50D-67645F99C4BD@.microsoft.com...
>I need to format a text in a text box to show a text field truncated.
> In other terms, I must show only 40 characters of a very long string.
> Do I use the statement "format"?
> How can I solve this issue?
> Many thanks|||Or in the report itself:
Field => expression => LEFT(strVariable, 40)|||OK! Many thanks
"Vyv" wrote:
> Or in the report itself:
> Field => expression => LEFT(strVariable, 40)
>

Formatting syntax

Hi,

I am trying to convert an MS Access query to SQL script.The query builds a 16 digit field by combining 3 different columns.

access query

Select

"SP"& Format([CusNum],"000")& " "& Format$([InVoiceNum],"00000000") & "-" & Format$(,"00") AS RefNo,

From PurchaseOrder

RefNo

SP053 00000001-00

SP053 00000010-00

SP05314556895-00

SQL query to replace access query

Select

'SP'+Isnull(Cast(CusNumasvarchar(3)),'000')+''+Isnull(Cast(InVoiceNumasvarchar(8)),'00000000')+'-'+Isnull(Cast(PIasvarchar(2)),'00')

From dbo.PurchaseOrder

SP0531-0

SP05310-0

SP05314556895-00

I need some help in putting together a query that will give me the same results as produced by the access query.If InvoiceNum is less than 8 digits long I don’t know how to pad the field with 0000 to get it to be desired length.

Thanks for your help.

Nats

Nats:

Maybe something like this:

declare @.aNumber integer set @.aNumber = 715

select @.aNumber as [Input Number],
right ('0000000' + convert(varchar(8), @.aNumber), 8) as [formatted Number]

Input Number formatted Number
-
715 00000715

( Bemoaning my rustiness at Access; I wrote part of Access 97 Unleashed 2nd Ed, but I haven't used Access really since the previous millenium -- SHEESH! )

|||

Try the example below.

Chris

SELECT 'SP'

+ RIGHT('000' + ISNULL(CAST([CusNum] AS VARCHAR(3)), ''), 3)

+ ' '

+ RIGHT('00000000' + ISNULL(CAST([InVoiceNum] AS VARCHAR(8)), ''), 8)

+ '-'

+ RIGHT('00' + ISNULL(CAST(PI AS VARCHAR(2)), ''), 2)

FROM dbo.PurchaseOrder

--Note that PI should be in square brackets, however for some reason when I do this in the forum I end up with a piece of pizza, like so:

|||

Awesome works great...

Thanks a bunch!

Formatting Parameter and Field Dates

I'm new to Reporting Services so bear w/ me,

I'm having troubles with formatting the parameter and field dates. To resolve the Field dates I just formmated them in the query, but I still haven't found a way for the Parameter fields. The date parameters is passed as YYYYMMDD(oracle number data type), and I want to format and display it as MM/DD/YYYY.

Any help would be appreciated,

also would like how to know how this works for the field values as well,

Thanks,
Nick

If you want to change the format of the parameter (assuming that your referring to a date parameter) you can use this one:

=Format(Now(), "MM/dd/yyyy")

Click on the "Tools" then "Report Parameters". Under the Default Values, click on the Non-Queried option then place on the text field the formula above.

Hope this helps..

|||

Thanks for the reply. I'm not sure if I did it the best way, but I ended up using

="From Date: " + Parameters!FromDate.Value.substring(4,2) + "/" + Parameters!FromDate.Value.substring(6,2) + "/" + Parameters!FromDate.Value.substring(0, 4).

Formatting numeric fields in select-clause

This is propably a very simple question, but I can′t seem to find the answer
to it in the documentation.
I want to format a numeric field so the result is right justified and
zero-filled.
ex select 1 will give the result 01
How do I manage this simple task?
best reguards from sunny Sweden
Magnus B
Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.mseq:8236
On Mon, 14 Feb 2005 08:13:01 -0800, Magnus Broman wrote:

>This is propably a very simple question, but I cant seem to find the answer
>to it in the documentation.
>I want to format a numeric field so the result is right justified and
>zero-filled.
>ex select 1 will give the result 01
>How do I manage this simple task?
Hi Magnus,
Formatting is usually done by the presentation layer. In fact, the format
SQL Server uses to send numeric values to the client software is quite
different from what you'll ever see on your screen <g>.
If you have valid reasons for doing the formatting at the server, you can;
but you should be aware that the data is then no longer numeric: after
formatting, it's string data.
DECLARE @.number int
SET @.number = 1
SELECT RIGHT('00' + CAST(@.number AS varchar(2)), 2)
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

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.