March 9, 2017 at 9:11 am
I am facing an issue with ssrs numeric formating.I am using a custom formating for a cell and it works fine but
for some values it doesnot show the data.Below is the format expresion I am using.When I use replace "#,#,," to
some other format then data shows up.For instance of the values that is not showing up is "-298075.04207000" although
the value "-3116774.79040000" is showing up on the screen as -3(which is correct).
When I export the report in excel and click on the cell then value is visible on formula screen but not its showing up
in the cell(same as ssrs reports).
expression for formating the cell:
=Lookup(Fields!field1.Value,Fields!field2.Value,"#,#,,", "dataset1")
Any ideas or suggesion would be a great help.
March 9, 2017 at 9:27 am
What do you mean by "not showing up"? If a cell has a value in the formula bar, but not in the cell, it's because the value of the formula equates to nothing.
If, however, your cell (in excel) is displaying a bunch of #'s, it's because your column is not wide enough to display the cell contents. Make your column wider.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
March 9, 2017 at 9:54 am
the values are coming from a dataset(stored procedure).Not showing up means the cell is blank for some values(although the value is present in database table).
I read one link warning the usage of place holder "#". The link says as below:
"Note that no digit appears in the result string if the corresponding digit in the input string is a non-significant 0. For example, 0003 ("####") -> 3."
https://msdn.microsoft.com/en-us/library/0c899ak8.aspx
when I change the place holder in format function then the data visible.
March 9, 2017 at 10:06 am
I'm not sure what the relevance of the above is. If you want the number "3" to be displayed as "0003" you would use the format "0000", not "####". The same page you linked to advises this.
What is the expression you are using for your formatting. What is the data you have, and what does it look like in Excel?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
March 9, 2017 at 10:16 am
The expression is mentioned the first post but I will update that to below:
I am using =Format(Fields!field1.Value,"#,#,,") as custom numeric formating where field1 comes from dataset.
Almost all the values coming from dataset are shown in the cell except few. One of such value is "-298075.04207000".
the issue is not only to show the data but to get in a desired format(which is like "#,#,,").
March 10, 2017 at 3:02 am
Don't use an expression to change the format of a field on SSRS, change the the format property of the cell. Using FORMAT converts the field to a string.
I just tested, however, in Excel and using the format "#,#,," on the value -298075.04207000, gives a blank value. I assume this is what you mean by "not showing up", but "not showing up" could have meant more than just "blank. What are you actually trying to display, the first digit of the number, and include the negative if it is? I'm not sure you can achieve this with formatting.
The formatting you've supplied, "#,#,,", is doing what it's supposed to. The reason it's not working, however, is because the format you have defined expects a number of minimum ±1,000,000. Anything below that, and the field will be blank. This is because the first # you have is in the millions position.
Why not use use a derived field? Something like:LEFT(abs(YourField),1) * CASE WHEN YourField < 0 THEN -1 ELSE 1 END
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply