July 15, 2014 at 3:39 pm
Hi,
I need to fill one small tablix on report which is define like below, chalenge that it's mixed content words and numeric together that's why column defined as varchar, but on tablix I 'd like to apply numeric formatting with no decimal places, 1000 separator to id=2,3, If I do changes to text box property/Number/ it does't take it , probably because it's varchar, is it possible to to this formatting inside RDL, as I don't want to touch that sp ?
My goal it to have on tablix:
Flag Name | Alpha
Volume | 1,000
Amount | 22
CREATE TABLE #tbl
(IDINT,
[Description]NVARCHAR(250),
ValueNVARCHAR(50)DEFAULT '' )
INSERT INTO #tbl VALUES
(1,'Flag Name', 'Alpha') ,
(2,'Volume', '1000.00') ,
(3,'Amount', '22.00')
SELECT * FROM #tbl
Thanks
M
July 15, 2014 at 5:22 pm
Without changing the stored procedure, it is a bit messy but possible.
1) In the Cell where you want to display the value, remove any existing textbox and insert a Rectangle.
2) Inside the new Rectangle, insert two textboxes.
3) In the first textbox, make it display your varchar column and set it's "Hidden" property to the expression: = IsNumeric(Fields!value.Value)
4) In the second textbox, use this expression as the display element : =Cdbl(Fields!value.Value) and set the "Hidden" property to = Not IsNumeric(Fields!value.Value) and set the "Format" property to "N0"
5) Now position the two textboxes over the top of each other and shrink the Column they are in to fit the width of one textbox.
It's a kludge, but it works.
See attached report for an example.
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);July 15, 2014 at 6:08 pm
Thanks, Magoo
It's tablix, so I don't want to introduce any new structures.
I finished making format inside sp before returning it to rdl.
like
----- formatting e.g '12345.00' ====> '12,345'
UPDATE #tbl
SET Value = LEFT(CAST(CONVERT(VARCHAR, CAST(Value AS MONEY), 1) AS VARCHAR),CHARINDEX('.',CAST(CONVERT(VARCHAR, CAST(Value AS MONEY), 1) AS VARCHAR)) -1)
WHERE ID > 2
and then in rld I did conditional allighment by id value, left or right
July 16, 2014 at 6:45 am
Using a single textbox cell within a tablix you can :
1. Set the value of the cell using an expression as :
=IIF(ISNUMERIC(Fields!Value.Value),VAL(Fields!Value.Value),Fields!Value.Value)
2. Set the format property of the cell as below:
=IIF(ISNUMERIC(Fields!Value.Value),"#,##0","")
This will convert the string to a number if possible. The second would suggest the format for numeric cells.
Fitz
(.rdl demo attached)
July 16, 2014 at 7:20 am
Did you try out with the sample data?
When i tried that method it came up with #error for the non numeric item....
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);July 16, 2014 at 7:25 am
mister.magoo (7/16/2014)
Did you try out with the sample data?When i tried that method it came up with #error for the non numeric item....
Yes, rdl includes the query as written but using a table variable rather than a temporary table. The trick seems to be using the VAL function rather than the CDBL or some other conversion function.
I have found that the IIF statement in SSRS evaluates both the TRUE and FALSE parts before deciding which to return. This would cause an #Error to return for the Alpha value on row with ID 1.
Fitz
July 16, 2014 at 7:47 am
Ah that's it then. I used cdbl. Thanks.
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply