Reporting Services fomating a textbox (round)

sesose
sesose Member Posts: 2
edited 2011-02-11 in NAV Three Tier
hi,

in my reporting services report i have a textbox wich shows the sum of unit prices (=Sum(Fields!Sales_Line__Unit_Price_.Value).

my problem is, i would like to fomat the textbox that it shows me the value without decimal places.

i tried many format codes in the fomat propertie like '########'. This code works but with that i dont have the point of the tousand value.

with that code the output is: 100000 but i would like to have 100.000,-. I also tryed ####.#### but it doesnt work.

have somebody an idea how to fomat it?

thanks, rené

Comments

  • rico1931
    rico1931 Member Posts: 295
    I Hate to bring up an old topic but does anyone have a solution for this? My current prices are showing like $46.335 instead of $46.34 ....

    The field is called UnitPrice and Unit price has another value in the data set that is UnitPriceFormat that when printed on the report it displays as "#,##0.00" I'm wondering why the format isn't working. Any more info just ask! Sorry if i'm being unclear!
    -rico1931
  • mohana_cse06
    mohana_cse06 Member Posts: 5,506
    Did you try the below trick?

    http://mibuso.com/forum/viewtopic.php?f=5&t=45808

    -Mohana
    http://mohana-dynamicsnav.blogspot.in/
    https://www.facebook.com/MohanaDynamicsNav
  • rico1931
    rico1931 Member Posts: 295
    mohana_cse06 wrote:
    Did you try the below trick?

    http://mibuso.com/forum/viewtopic.php?f=5&t=45808


    I tried to put a "n" in the format property text box and nothing changed :cry:
    -rico1931
  • mohana_cse06
    mohana_cse06 Member Posts: 5,506
    Are you sure that you dont have a field like
    Sales_Line__Unit_PriceFormat
    in your dataset?

    -Mohana
    http://mohana-dynamicsnav.blogspot.in/
    https://www.facebook.com/MohanaDynamicsNav
  • rico1931
    rico1931 Member Posts: 295
    mohana_cse06 wrote:
    Are you sure that you dont have a field like
    Sales_Line__Unit_PriceFormat
    in your dataset?


    yup I do.. and when I insert that field into my table above Sales_Line__Unit_Price it does nothing..
    -rico1931
  • mohana_cse06
    mohana_cse06 Member Posts: 5,506
    rico1931 wrote:
    mohana_cse06 wrote:
    Are you sure that you dont have a field like
    Sales_Line__Unit_PriceFormat
    in your dataset?


    yup I do.. and when I insert that field into my table above Sales_Line__Unit_Price it does nothing..

    I didnt get where you have inserted?

    -Mohana
    http://mohana-dynamicsnav.blogspot.in/
    https://www.facebook.com/MohanaDynamicsNav
  • rico1931
    rico1931 Member Posts: 295
    mohana_cse06 wrote:
    rico1931 wrote:
    mohana_cse06 wrote:
    Are you sure that you dont have a field like
    Sales_Line__Unit_PriceFormat
    in your dataset?


    yup I do.. and when I insert that field into my table above Sales_Line__Unit_Price it does nothing..

    I didnt get where you have inserted?


    I put the PriceFormat field in the body section - same table - in the cell above Unit_Price... so its in the same area.. I even tried putt priceformat field in the header everyone in the body and nothing. Can anyone tell me where it's "suppose" to go?
    -rico1931
  • mohana_cse06
    mohana_cse06 Member Posts: 5,506
    Can you show the report layout so that we can help you...
    you need to add priceformat in the Sales_Line__Unit_Price field format property...

    -Mohana
    http://mohana-dynamicsnav.blogspot.in/
    https://www.facebook.com/MohanaDynamicsNav
  • rico1931
    rico1931 Member Posts: 295
    Well here is the thing.. I have a very unique situation. If you see the table in the attachment you'll see that the table has been moved around and modified. Where the red arrow is showing, I have concatenated a series of fields so they show up in one cell ( the reason behind this is long, mostly to do with the picture in the cell to the far left was messing up the format of my data I was trying to return) but here is what is located in the cell...
    =Sum(Fields!Sales_Line___Unit_Price_.Value) & vbcrlf 
& Fields!DM_GetInvoiceDiscPct.Value &" % " 
& vbcrlf & Fields!DM_GetNetUnitPrice.Value

    This evens it all out and looks nice..as you can see in my 2nd attachment


    but the "Net Price" format looks all messed up . I added this field "=Fields!Sales_Line___Unit_Price_Format.Value" into the Format property of the cell but it did nothing :cry: ...i'm all out of ideas!

    any suggestions will be tried! I promise!
    -rico1931
  • Belias
    Belias Member Posts: 2,998
    you've concatenated text and numbers in the same textbox: the final result will be a text, so the numbering format won't work!if you split your values through different cells, i guess the number format will work correctly. In order to solve your problem, you've to round this value (Fields!DM_GetNetUnitPrice.Value) before sending the value to visual studio with the round function (in the onaftergetrecord trigger of nav, i guess).
    If you've problems about value Sum(Fields!Sales_Line___Unit_Price_.Value), you've two ways:
    1. round the unit price correctly in nav (and if all the unit prices have 2 decimals, their Sum aggregate will have 2 decimals, obviously) :mrgreen:
    2. use the round function of visual web developer on your sum function (in the expression menu in visual web dev, view common functions -> math to see its syntax)
    -Mirko-
    "Never memorize what you can easily find in a book".....Or Mibuso
    My Blog
  • rico1931
    rico1931 Member Posts: 295
    Belias wrote:
    you've concatenated text and numbers in the same textbox: the final result will be a text, so the numbering format won't work!if you split your values through different cells, i guess the number format will work correctly. In order to solve your problem, you've to round this value (Fields!DM_GetNetUnitPrice.Value) before sending the value to visual studio with the round function (in the onaftergetrecord trigger of nav, i guess).
    If you've problems about value Sum(Fields!Sales_Line___Unit_Price_.Value), you've two ways:
    1. round the unit price correctly in nav (and if all the unit prices have 2 decimals, their Sum aggregate will have 2 decimals, obviously) :mrgreen:
    2. use the round function of visual web developer on your sum function (in the expression menu in visual web dev, view common functions -> math to see its syntax)

    Hey Belias,

    As always you come to the rescue! LOL thanks and yes that is a lot closer than I was before using the round function

    =Round(Fields!DM_GetNetUnitPrice.Value)

    The only thing is that it rounds to the nearest WHOLE int. so a number like 46.335 rounds to 46 ... and I tried the following

    Round(Fields!DM_GetNetUnitPrice.Value,#.##0.00) and other combination's of that and I get an error.. I've looked up on

    http://msdn.microsoft.com/en-us/library/dd338738.aspx

    to try to figure it out but it really doesn't help me much.. have any suggestions on how to specify to Round to nearest hundredth decimal?
    -rico1931
  • Belias
    Belias Member Posts: 2,998
    try this
    =Round(Fields!DM_GetNetUnitPrice.Value,2)
    -Mirko-
    "Never memorize what you can easily find in a book".....Or Mibuso
    My Blog
  • rico1931
    rico1931 Member Posts: 295
    Belias wrote:
    try this
    =Round(Fields!DM_GetNetUnitPrice.Value,2)

    As we like to say here in America Belias .... YOU ARE THE MAN!!! \:D/ =D> :thumbsup: :lol::D
    -rico1931
  • rico1931
    rico1931 Member Posts: 295
    Well since I wasn't the OP can a Mod please mark this as solved or at least a good posting... GRACIAS!
    -rico1931

Categories