March 23, 2015 at 3:43 pm
Hello, below is my query I am converting the values to text and when I run this query in SSRS it still rounds the second to right decimal values. So, the below query will return these values correctly when I run it in management studio. But, when I plug the query into SSRS the 3.45 value will be 3.46 which is NOT what want. I've tried using Format() and Floor() functions in SSRS still doesn't work. Why is SSRS rounding up? Just want it to show what the query results are as is. Appreciate the help.
3.45
2.36
4.01
select a.Sort, cast(cast(cast(SumTotal as decimal(18,2))/cast(RowTotal as decimal(18,2)) as decimal(18,2)) as nvarchar(10))
March 23, 2015 at 4:50 pm
Can I ask why you are casting your decimal to a unicode string?
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);March 23, 2015 at 4:59 pm
No particular reason I can use char(), but I don't think that is the reason for it rounding a char value in SSRS?
March 23, 2015 at 5:00 pm
Ok, let me rephrase it - why are you casting a number to a string? (ignore the unicode for now)
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);March 23, 2015 at 5:09 pm
I was originally casting it as a decimal value, but SSRS is rounding the second decimal place value, so I thought I would try using a string to try and trick SSRS into thinking it doesn't need to round the value since it's a string. But, no luck SSRS still rounds the value. I don't know why SSRS won't just display the query value.
March 23, 2015 at 5:19 pm
remove the cast as a string, and using the decimal type, display the value in a textbox using the "format" property value "N5", just to see what SSRS really thinks the numbers are.
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);March 23, 2015 at 5:29 pm
I'm getting below value.
3.46000
March 23, 2015 at 5:32 pm
What value do you see if you preview the dataset in SSRS?
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);March 24, 2015 at 10:12 am
3.46 but the actual query sql statement is 3.45 which is the correct value.
March 24, 2015 at 12:12 pm
Appreciate the help. It was user error. =) My query was missing some data. SSRS side the multi-value parameter did not include null values. Any way of adding a null to multi-value parameter? I just had to update the values with 'blank' where it was null so SSRS passes in the 'blank' value to search for null values in a column. =). Thanks.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply