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

Format date in a Stored Procedure

Dear friends,

I have a stored procedure that returns some fiels. One of the fields is a datetime type.

The field return in the follow format : 2006-11-13 0:00:00

How can I return only 2006-11-13? How can I use the format function?

regards!!!

declare @.someDate datetime
set @.someDate = getdate()
select cast(datepart(yyyy, @.someDate) as varchar) + '-' + cast(datepart(mm, @.someDate) as varchar) + '-' + cast(datepart(dd, @.someDate) as varchar)

result: 2006-11-13

|||

Or...

Select Convert(varchar(10), GetDate(), 120)

Lookup the convert function in Books On Line for more formats.

|||

Here is another method:

select DATEADD(DAY, 0, DATEDIFF(DAY, 0, GETDATE()))

This keeps the datatype as a datetime while removing the date from the string. It is best practice to format the data in the UI.

|||

Dear friens,

First, let me thank for all your support.

And the last question about this problem, How can get the system current time in format hh:mm? (ex: 12:30)

Thanks

|||You should not care about formatting the date on the server this is a thing for the presentation layer.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||ok, but how I return the time value of the system?|||Do you mean at the presentation layer ? That depends on your used coding language, with .NET you will date various options on the Date type.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

For example: I want to create the follow stored procedure:

CREATE PROCEDURE TEST

@.ID INT

AS

UPDATE TABLE1 SET MyFieldTime=@.MySystemTime WHERE MyFieldID=@.ID

Understood?

I want to save in my database th system time...

Thanks!!

|||Depending on which datatype you use in the column you can′t separate the date and the time. Datetime is a combined type storing date as well as time. Is it against any rules storing the date additionally ?

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

I save the time field in teh database is a nchar(5) as for example: 12:30, 22:30, 08h00

Thanks!!

format 0.00 decimal value

Is there any option/settings in SQL server to return 0 decimal value as
"0.00"
not
".00"
I can do it by converting all results to varchar(), but is there any easy
way? may be server settings?
thanks
L
Formatting of decimal data is done by the front end, not the server.
So the problem is with Query Analyzer, or whatever tool you are using.
If it is QA there is no feature like you are asking for.
Roy Harvey
Beacon Falls, CT
On Wed, 29 Nov 2006 13:43:01 -0800, LLT
<LLT@.discussions.microsoft.com> wrote:

>Is there any option/settings in SQL server to return 0 decimal value as
>"0.00"
>not
>".00"
>I can do it by converting all results to varchar(), but is there any easy
>way? may be server settings?
>thanks
>L
|||I am using QA. The same in DTS when I extract data into txt file. Strange,
but float is returned with leading 0 as "0.00", decimal and money without as
".00". When number converted to string using cast() or str() leading 0 is
shown for all numbers.
You can run in in QA.
select 'money, with varchar cast', cast(cast (0.00 as money) as varchar(10))
select 'money, no varchar cast', cast(0.00 as money)
select 'decimal(10,2) with varchar cast', cast(cast (0.00 as decimal(10,2))
as varchar(10))
select 'decimal, no varchar cast', cast(0.00 as decimal(10,2))
select 'float, with varchar cast', cast(cast (0.00 as float) as varchar(10))
select 'float, no varchar cast', cast(0.00 as float)
"Roy Harvey" wrote:

> Formatting of decimal data is done by the front end, not the server.
> So the problem is with Query Analyzer, or whatever tool you are using.
> If it is QA there is no feature like you are asking for.
> Roy Harvey
> Beacon Falls, CT
> On Wed, 29 Nov 2006 13:43:01 -0800, LLT
> <LLT@.discussions.microsoft.com> wrote:
>

format 0.00 decimal value

Is there any option/settings in SQL server to return 0 decimal value as
"0.00"
not
".00"
I can do it by converting all results to varchar(), but is there any easy
way? may be server settings?
thanks
LFormatting of decimal data is done by the front end, not the server.
So the problem is with Query Analyzer, or whatever tool you are using.
If it is QA there is no feature like you are asking for.
Roy Harvey
Beacon Falls, CT
On Wed, 29 Nov 2006 13:43:01 -0800, LLT
<LLT@.discussions.microsoft.com> wrote:

>Is there any option/settings in SQL server to return 0 decimal value as
>"0.00"
>not
>".00"
>I can do it by converting all results to varchar(), but is there any easy
>way? may be server settings?
>thanks
>L|||I am using QA. The same in DTS when I extract data into txt file. Strange,
but float is returned with leading 0 as "0.00", decimal and money without as
".00". When number converted to string using cast() or str() leading 0 is
shown for all numbers.
You can run in in QA.
select 'money, with varchar cast', cast(cast (0.00 as money) as varchar(10)
)
select 'money, no varchar cast', cast(0.00 as money)
select 'decimal(10,2) with varchar cast', cast(cast (0.00 as decimal(10,2))
as varchar(10))
select 'decimal, no varchar cast', cast(0.00 as decimal(10,2))
select 'float, with varchar cast', cast(cast (0.00 as float) as varchar(10))
select 'float, no varchar cast', cast(0.00 as float)
"Roy Harvey" wrote:

> Formatting of decimal data is done by the front end, not the server.
> So the problem is with Query Analyzer, or whatever tool you are using.
> If it is QA there is no feature like you are asking for.
> Roy Harvey
> Beacon Falls, CT
> On Wed, 29 Nov 2006 13:43:01 -0800, LLT
> <LLT@.discussions.microsoft.com> wrote:
>
>

format

Hi,
select SUM(CommAmt * (1 - Disc/100.00)) AS Comm from mytbl return:
24.6199951171875
how to format it so it will only return two decimal? Thanks.CONVERT(DECIMAL(5,2), expression)
"js" <js@.someone@.hotmail.com> wrote in message
news:O$0z8W9tFHA.1032@.TK2MSFTNGP12.phx.gbl...
> Hi,
> select SUM(CommAmt * (1 - Disc/100.00)) AS Comm from mytbl return:
> 24.6199951171875
> how to format it so it will only return two decimal? Thanks.
>
>