Number format

[aj]

Hi everyone,
Can anyone tell me how to change the numeric format??

In my case, I want to change the -ve sign into ( ), and have the digit grouping when the number reach thousands.(If anyone familiar, it's like the Excel format, changing the negative format)
I can change the -ve sign into ( ) using the IIf() function. E.g: = IIf(Fields.creditAmount>0, Fields.creditAmount, "(" + (Fields.debitAmount) + ")") . So the result which I get satisfy me. If the number is larger than 0 (which is not a negative number) , it display 1123.45. 
If  the result is smaller than 0 (which is a negative number), it display (1123.45). So far the result, fullfill my first requirement.

However, by doing the above stated (using the IIF( ) function), I could not manage to get the digit grouping. How can I do that? I try using the {0:N2},  but the result won't display according to the format digit.

Can anyOne help me? I appreciate any opinion. Thank you very much.

[Svetoslav]

Hello Aj,

Telerik Reporting expose two ways to manage your data - you may use the built-in expressions and even extend them with custom - user defined functions or handle the item binding events and use the power of the programming language. The choice depends on the complexity of the data and the operations you need to perform with it.

For your specific formatting problem there are several approaches:

• with the built-in expression functionality:

=IIF(num > 0, '', '(') + FORMAT('{0:N2}', ABS(num)) + IIF(num > 0, '', ')')

• user-defined function to format the number:

class MyReport : Telerik.Reporting.Report
        {
            public static string MyFormat(double num)
            {
                string format = "{0:N2}";
                if (num < 0)
                {
                    format = "({0:N2})";
                }
                return string.Format(format, Math.Abs(num));
            }
        }

and use in the next simple expression like this:

=MyFormat(CDbl(num))

• Use the MyFormat() method above in the text box's ItemDataBound event:

private void textBox1_ItemDataBound(object sender, System.EventArgs e)
{
   Processing.TextBox textBox1 = (Processing.TextBox)sender;
   DataRowView dataRowView = (DataRowView)textBox1.DataItem;
   textBox1.Text = MyFormat(dataRowView["num"]);
}

For more information on the expressions please see Using Expressions.
For more information on the report events please see Understanding Events, Using Report Item Events and the FAQ topic "How do I combine values of two columns to a single TextBox?".

Sincerely yours,
Svetoslav
the Telerik team

Instantly find answers to your questions at the new Telerik Support Center

[aj]

Hi Svetoslav,

Thank you for your suggestion solution. I've tried the built-in expression funtionality, but it gives me an error
    #ERROR# The expression contains undefined function call FORMAT().


Could you help me to correct the error above?

However, using your post, gives me ideas on how to manipulate the IIF() function. For the time being, i'm still working on finding the right solutions.

Thank you again and I would love to have your other magnificient solutions and responds. If anyOne else have suggestions, just post your answer... Thousand thanks.

[Milen | Product Manager @DX]

Hi aj,

There are two cases I can think of why the Format function is not defined.

First one is if you are using a version of Telerik Reporting prior to Q1 2008 - this was the version in which the function was implemented. So make sure you are using the latest version of the product.

Second one (more likely), if you are trying to use the function to format more than one value. At this time the Format function supports only the following signature:

System.String Format(System.String format, System.Object value)

If the above is not relevant to your case please open a support ticket and send us a sample report that demonstrates the described behavior.

Sincerely yours,
Milen
the Telerik team

Instantly find answers to your questions at the new Telerik Support Center

[Michael Veltre]

Is there a way to do this in the format property of the text box or do you have to use expressions?

[Steve]

Hi Michael,

This thread is two years old now, and since then we have implemented Format property to the textbox item. So the answer is that you can do this either way - via the Format Builder Dialog or through the Format function.

All the best,
Steve
the Telerik team

Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items.

[Nathan]

Hi,

I just ran into this similar issue where I want to show amounts as blank/empty if zero amount, and use brackets for negative amounts.

I looked at the link Steve provided here for the format builder dialog but don't see how to accomplish this or any other complex formatting. What is the recommended best approach?

So far I've looked into the following:
conditionalformatting - I did not find a way to manipulate the value, only the text style
user defined format - I would prefer not to have to write a bunch of stuff in a code behind (since I have many many reports to write), but this seems like it works fine. Any performance concerns using the itemdatabound event this way?

Have you guys considered adding a converter option? This would make it consistent with other data binding in Silverlight and most of us already have libraries of converters for our UI anyway. Just a thought.

Thanks for the help!

Nathan

[Patrick]

Have a look at the attached screenshot. Positive values are displayed as 123,456 and negative values are displayed as (123,456)

The format string doesn't take into account cents, only whole dollar amounts. If you need them, change the format string to:
#,###.00;(#,###.00)

[Nathan]

Hey Patrck,

 

Thanks that looks great for negatives - but what about zeroes formatted as blank string?

 

Currently I have code firing in the itemdatabound event that calls the following so you can see what I'm going for:

    Public Shared Function FormatDollar(ByVal dblValue As Double, Optional ByVal ShowZeroAsBlank As Boolean = True) As String
        If dblValue = 0 And ShowZeroAsBlank Then
            Return ""
        ElseIf dblValue < 0 Then
            Return Format(Math.Abs(dblValue), "(#,##0.00)")
        Else
            Return Format(Math.Abs(dblValue), "#,##0.00")
        End If
    End Function

 

 

[Patrick]

Unless I'm way off base here, which is very likely :o), this should get you what you want:

    Public Shared Function FormatDollar(ByVal dblValue As Double, Optional ByVal ShowZeroAsBlank As Boolean = True) As String
        If dblValue = 0 And ShowZeroAsBlank Then
            Return Format(dblValue, "#")
        ElseIf dblValue < 0 Then
            Return Format(Math.Abs(dblValue), "(#,##0.00)")
        Else
            Return Format(Math.Abs(dblValue), "#,##0.00")
        End If
    End Function